好急呀!

dushuang 2002-09-15 09:07:41
由于要在数据库中建立关系,我将一个表的一个字段设为主键:
tabel A
Field1 tempID int 4 自增
Field2 webID int 4 主键
. . . . .
. . . . .
. . . . .
tabel2 B
Field1 testID int 4 自增
Field2 webID int 4 主键

关系通过webID 建成,为什么在B表中只能添加一条纪录,多了就说主键唯一值约束冲突。
B.webID 的值在A表中都有!

...全文
11 点赞 收藏 12
写回复
12 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
mengxianbao1521 2002-09-16
也遇到过但有点忘记了
不知道是不是与你的问题相同 ,我好象是因为SQL 语句的 错误
回复
yxsalj 2002-09-16
mark
回复
Yang_ 2002-09-16
tableB.WebID的约束有:
1、主键PK_TABLE_B,要求tableB.WebID唯一,不能重复,不能为空。
2、外键FK_TABLE_B_Tabel_a,要求tableB.WebID出现的值必须在tableA.WebID出现。

所以,你按照saucer(思归) 的方法插入数据是可以的
insert into Tabel_a (A_ID,A_other) values(1,N'hello')
go
insert into Tabel_a (A_ID,A_other) values(2,N'world')
go
select * from tabel_a
go
insert into Table_b (WebID,B_other) values(1,N'B-world')
go
insert into Table_b (WebID,B_other) values(2,N'C-world')
go
select * from table_b
go

而如果你按照一下语句插入,就会出唯一性错误:

insert into Tabel_a (A_ID,A_other) values(1,N'hello')
go
insert into Tabel_a (A_ID,A_other) values(2,N'world')
go
select * from tabel_a
go
insert into Table_b (WebID,B_other) values(1,N'B-world')
go
insert into Table_b (WebID,B_other) values(2,N'C-world')
go
insert into Table_b (WebID,B_other) values(2,N'C-world1')
go
select * from table_b
go
回复
dushuang 2002-09-16
出现的错误是:不能在具有唯一索引'domain'的对象'tabel_B'中添入重复键的行!
回复
dushuang 2002-09-16
我现在直接在企业管理器里添加都不行!
回复
dushuang 2002-09-16
我就是不明白主外键关系,其他的问题没有!他提示唯一约束限制!
回复
microlong 2002-09-15
两个表有相同的字段值!那么每个字段值是相同的!一个建立主键,一个建立外键!外键收主键的约束,外键也只能引用主键!
回复
saucer 2002-09-15
it works for me (by the way, you'd better rename TABEL_A to TABLE_A to be consistent)

insert into Tabel_a (A_ID,A_other) values(1,N'hello')
go
insert into Tabel_a (A_ID,A_other) values(2,N'world')
go
select * from tabel_a
go
insert into Table_b (WebID,B_other) values(1,N'B-world')
go
insert into Table_b (WebID,B_other) values(2,N'C-world')
go
select * from table_b
go
回复
dushuang 2002-09-15
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TABLE_C_TABLE_B]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TABLE_C] DROP CONSTRAINT FK_TABLE_C_TABLE_B
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TABLE_B_Tabel_a]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TABLE_B] DROP CONSTRAINT FK_TABLE_B_Tabel_a
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TABLE_B]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TABLE_B]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TABLE_C]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TABLE_C]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Tabel_a]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Tabel_a]
GO

CREATE TABLE [dbo].[TABLE_B] (
[B_id] [int] IDENTITY (1, 1) NOT NULL ,
[webID] [int] NOT NULL ,
[B_other] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TABLE_C] (
[C_ID] [int] IDENTITY (1, 1) NOT NULL ,
[webID] [int] NOT NULL ,
[C_other] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Tabel_a] (
[A_id] [int] NOT NULL ,
[webID] [int] IDENTITY (1, 1) NOT NULL ,
[A_other] [nchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TABLE_B] WITH NOCHECK ADD
CONSTRAINT [PK_TABLE_B] PRIMARY KEY CLUSTERED
(
[webID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TABLE_C] WITH NOCHECK ADD
CONSTRAINT [PK_TABLE_C] PRIMARY KEY CLUSTERED
(
[webID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Tabel_a] WITH NOCHECK ADD
CONSTRAINT [PK_Tabel_a] PRIMARY KEY CLUSTERED
(
[webID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TABLE_B] ADD
CONSTRAINT [FK_TABLE_B_Tabel_a] FOREIGN KEY
(
[webID]
) REFERENCES [dbo].[Tabel_a] (
[webID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[TABLE_C] ADD
CONSTRAINT [FK_TABLE_C_TABLE_B] FOREIGN KEY
(
[webID]
) REFERENCES [dbo].[TABLE_B] (
[webID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
回复
dushuang 2002-09-15
外键怎么建立?我不太明白主键外键的关系和用途,能给说说么?
回复
saucer 2002-09-15
show your table definitions in SQL
回复
zbc 2002-09-15
如果您的表a与表b要建立关联的话,不能两个表都建立主键的,需要一个建主键,一个建外键
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2002-09-15 09:07
社区公告
暂无公告