好急呀！

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

回复赞