无法与另一个表创建外键关系

I'm trying to do this,

我正在努力做到这一点,

CREATE TABLE [dbo].[tblPerson](
[ID] [int] NOT NULL,
[Personame] [varchar](50) NULL)
[Email] [varchar](50) NULL,
[GenderId] [int] NULL

CREATE TABLE [dbo].[tblGender]( [id] [int] NOT NULL, [Gender] nvarchar NOT NULL)

Alter table tblPerson Add Constraint tblPerson_GenderId_FK Foreign Key (GenderId) references tblGender(id)

I am getting Below Error not sure why

我得到以下错误不知道为什么

Msg 547, Level 16, State 0, Line 2 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "tblPerson_GenderId_FK". The conflict occurred in database "Sqlkudavenkat", table "dbo.tblGender", column 'id'.

消息547,级别16,状态0,行2 ALTER TABLE语句与FOREIGN KEY约束“tblPerson_GenderId_FK”冲突。冲突发生在数据库“Sqlkudavenkat”,表“dbo.tblGender”,列'id'。

1 个解决方案

#1

I suspect that tblGenderis new table, in that case you need to populate it with values before you can create the foreign key.

我怀疑tblGender是新表,在这种情况下,您需要在创建外键之前使用值填充它。

Suppose in tblPerson you have used 2 values in GenderID

假设在tblPerson中你在GenderID中使用了2个值

0 meaning male
1 meaning female

then your queries could look like this :

然后你的查询看起来像这样:

CREATE TABLE [dbo].[tblGender]( [id] [int] NOT NULL, [Gender] nvarchar(100) NOT NULL)

insert into tblGender(id, gender)
select distinct p.GenderID, 
       case when p.GenderID = 0 then 'Male' 
            when p.GenderID = 1 then 'Female'
            else 'it came from outer space' 
       end
from   tblPerson p

Alter table tblPerson Add Constraint tblPerson_GenderId_FK Foreign Key (GenderId) references tblGender(id)

#1