本文介绍了在sql server中需要外键帮助的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在列上定义了一个主键,并在另一个表中定义了外键。如果我在外键列中输入空值,则显示错误。如何允许外键中的空值?
I define a primary key on column and I define foreign key in the another table. If I enter null value in the foreign key column it is showing the error.How to allow the null value in the foreign key?
推荐答案
-- This version will FAIL if there is a null in FKID
create table parent
(
ID int IDENTITY(1,1) NOT NULL,
SomeData varchar(20),
CONSTRAINT PK_parentID PRIMARY KEY CLUSTERED (ID)
)
-- Insert some sample data
insert into parent values('parent 1')
insert into parent values('parent 2')
insert into parent values('parent 3')
create table child
(
ID int IDENTITY(1,1) NOT NULL,
SomeChildData varchar(20),
FKID int NOT NULL,
CONSTRAINT PK_childID PRIMARY KEY CLUSTERED (ID), -- Primary key on this table
CONSTRAINT FK_ChildToParent FOREIGN KEY (FKID) REFERENCES parent -- Foreign key to parent
)
--insert some sample data
insert into child values('child 1.1', 1)
insert into child values('child 1.2', 1)
insert into child values('child 2.1', null)
输出
Output
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'FKID', table 'child'; column does not allow nulls. INSERT fails.
The statement has been terminated.
(0 row(s) affected)
然而......
Whereas ...
drop table child -- note the order I have to drop the tables
drop table parent
-- This version will WORK if there is a null in FKID
create table parent
(
ID int IDENTITY(1,1) NOT NULL,
SomeData varchar(20),
CONSTRAINT PK_parentID PRIMARY KEY CLUSTERED (ID)
)
-- Insert some sample data
insert into parent values('parent 1')
insert into parent values('parent 2')
insert into parent values('parent 3')
create table child
(
ID int IDENTITY(1,1) NOT NULL,
SomeChildData varchar(20),
FKID int,
CONSTRAINT PK_childID PRIMARY KEY CLUSTERED (ID), -- Primary key on this table
CONSTRAINT FK_ChildToParent FOREIGN KEY (FKID) REFERENCES parent -- Foreign key to parent
)
--insert some sample data
insert into child values('child 1.1', 1)
insert into child values('child 1.2', 1)
insert into child values('child 2.1', null)
select * from parent
select * from child
输出
Output
ID SomeData
1 parent 1
2 parent 2
3 parent 3
ID SomeChildData FKID
1 child 1.1 1
2 child 1.2 1
3 child 2.1 NULL
这里也是MSDN文档的链接 []
这篇关于在sql server中需要外键帮助的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!