在sql server中需要外键帮助 [英] need help in foreign key in 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?

推荐答案

我最近有同样的linq中的问题。我有另一个我可以宣布独特的专栏。我允许列具有空值，因此，我可以将其用作外键。



通常，您倾向于获取主键，但是当您这样做时，您必须意识到外键和辅助表中的列不能为空。这两者必须在所有方面相匹配。

I recently had the very same problem in linq. I had another column that I could declare unique. I allowed the column to have null values, and thus, I could use it as a foreign key.

Often, you tend to grab the primary key, but when you do so, you must realize that the foreign key and the column in the secondary table cannot be null. The two must match in all respects.

*向ZoltánZörgő点头*

这几乎可以肯定到你的桌子定义......最好用一个例子......

*Nodding to Zoltán Zörgő*
It''s almost certainly down to your table definition ... best demonstrated with an example ...

-- 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文档的链接 http：// msdn.microsoft.com/en-us/library/ms189049(v=sql.110).aspx [ ^ ]

这篇关于在sql server中需要外键帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！