如何在SQL Server中创建外键?

我从来没有为SQL Server“手工编写”对象创建代码，外键声明在SQL Server和Postgres之间似乎是不同的。这是我的sql到目前为止:

drop table exams;
drop table question_bank;
drop table anwser_bank;


create table exams
(
exam_id uniqueidentifier primary key,
exam_name varchar(50),
);
create table question_bank
(
question_id uniqueidentifier primary key,
question_exam_id uniqueidentifier not null,
question_text varchar(1024) not null,
question_point_value decimal,
constraint question_exam_id foreign key references exams(exam_id)
);
create table anwser_bank
(
anwser_id           uniqueidentifier primary key,
anwser_question_id  uniqueidentifier,
anwser_text         varchar(1024),
anwser_is_correct   bit
);

当我运行查询时，我得到这个错误:

Msg 8139，级别16，状态0，行9 中的引用列数 外键与number of不同 引用列、表 “question_bank”。< / p >

你能发现错误吗?

468177 次浏览
小开
最佳答案 
create table question_bank
(
question_id uniqueidentifier primary key,
question_exam_id uniqueidentifier not null,
question_text varchar(1024) not null,
question_point_value decimal,
constraint fk_questionbank_exams foreign key (question_exam_id) references exams (exam_id)
);
小开

你还可以使用以下命令命名外键约束:

CONSTRAINT your_name_here FOREIGN KEY (question_exam_id) REFERENCES EXAMS (exam_id)
小开

如果您只想单独创建约束，则可以使用ALTER TABLE

alter table MyTable
add constraint MyTable_MyColumn_FK FOREIGN KEY ( MyColumn ) references MyOtherTable(PKColumn)

我不推荐Sara Chipps提到的用于内联创建的语法，因为我宁愿自己命名约束。

小开

像你一样，我通常不手动创建外键，但如果出于某种原因，我需要脚本这样做，我通常使用ms sql server管理工作室创建它，然后保存更改，我选择表设计器|生成更改脚本

小开 
create table question_bank
(
question_id uniqueidentifier primary key,
question_exam_id uniqueidentifier not null constraint fk_exam_id foreign key references exams(exam_id),
question_text varchar(1024) not null,
question_point_value decimal
);

——那也可以。也许是一个更直观的结构?

小开

我喜欢AlexCuse的答案，但是无论何时添加外键约束，都应该注意如何处理对引用表中一行中的引用列的更新，特别是如何处理对引用表中的行删除。

如果约束是这样创建的:

alter table MyTable
add constraint MyTable_MyColumn_FK FOREIGN KEY ( MyColumn )
references MyOtherTable(PKColumn)

．． 然后如果引用表中有相应的行，则引用表中的更新或删除将失败并报错。

这可能是你想要的行为，但根据我的经验，它通常不是。

如果你像这样创建它:

alter table MyTable
add constraint MyTable_MyColumn_FK FOREIGN KEY ( MyColumn )
references MyOtherTable(PKColumn)
on update cascade
on delete cascade

．．然后，父表中的更新和删除将导致引用表中相应行的更新和删除。

(我并不是建议修改默认值，默认值过于谨慎，这是好的。我只是说，这是一个人谁正在创建约束应始终注意。)

顺便说一下，这可以在创建表时完成，就像这样:

create table ProductCategories (
Id           int identity primary key,
ProductId    int references Products(Id)
on update cascade on delete cascade
CategoryId   int references Categories(Id)
on update cascade on delete cascade
)
小开
< p > Necromancing。
实际上，正确地做这个有点棘手。< / p >

首先需要检查要将外键设置为引用的列的主键是否存在。

在本例中，创建表T_ZO_SYS_Language_Forms上的外键，引用dbo.T_SYS_Language_Forms.LANG_UID

-- First, chech if the table exists...
IF 0 < (
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'T_SYS_Language_Forms'
)
BEGIN
-- Check for NULL values in the primary-key column
IF 0 = (SELECT COUNT(*) FROM T_SYS_Language_Forms WHERE LANG_UID IS NULL)
BEGIN
ALTER TABLE T_SYS_Language_Forms ALTER COLUMN LANG_UID uniqueidentifier NOT NULL


-- No, don't drop, FK references might already exist...
-- Drop PK if exists
-- ALTER TABLE T_SYS_Language_Forms DROP CONSTRAINT pk_constraint_name
--DECLARE @pkDropCommand nvarchar(1000)
--SET @pkDropCommand = N'ALTER TABLE T_SYS_Language_Forms DROP CONSTRAINT ' + QUOTENAME((SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
--WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
--AND TABLE_SCHEMA = 'dbo'
--AND TABLE_NAME = 'T_SYS_Language_Forms'
----AND CONSTRAINT_NAME = 'PK_T_SYS_Language_Forms'
--))
---- PRINT @pkDropCommand
--EXECUTE(@pkDropCommand)


-- Instead do
-- EXEC sp_rename 'dbo.T_SYS_Language_Forms.PK_T_SYS_Language_Forms1234565', 'PK_T_SYS_Language_Forms';




-- Check if they keys are unique (it is very possible they might not be)
IF 1 >= (SELECT TOP 1 COUNT(*) AS cnt FROM T_SYS_Language_Forms GROUP BY LANG_UID ORDER BY cnt DESC)
BEGIN


-- If no Primary key for this table
IF 0 =
(
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'T_SYS_Language_Forms'
-- AND CONSTRAINT_NAME = 'PK_T_SYS_Language_Forms'
)
ALTER TABLE T_SYS_Language_Forms ADD CONSTRAINT PK_T_SYS_Language_Forms PRIMARY KEY CLUSTERED (LANG_UID ASC)
;


-- Adding foreign key
IF 0 = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_T_ZO_SYS_Language_Forms_T_SYS_Language_Forms')
ALTER TABLE T_ZO_SYS_Language_Forms WITH NOCHECK ADD CONSTRAINT FK_T_ZO_SYS_Language_Forms_T_SYS_Language_Forms FOREIGN KEY(ZOLANG_LANG_UID) REFERENCES T_SYS_Language_Forms(LANG_UID);
END -- End uniqueness check
ELSE
PRINT 'FSCK, this column has duplicate keys, and can thus not be changed to primary key...'
END -- End NULL check
ELSE
PRINT 'FSCK, need to figure out how to update NULL value(s)...'
END
小开

在任意表上创建一个外键

ALTER TABLE [SCHEMA].[TABLENAME] ADD FOREIGN KEY (COLUMNNAME) REFERENCES [TABLENAME](COLUMNNAME)
EXAMPLE
ALTER TABLE [dbo].[UserMaster] ADD FOREIGN KEY (City_Id) REFERENCES [dbo].[CityMaster](City_Id)
小开

这个脚本是关于创建带有外键的表，我添加了引用完整性约束sql server。

create table exams
(
exam_id int primary key,
exam_name varchar(50),
);


create table question_bank
(
question_id int primary key,
question_exam_id int not null,
question_text varchar(1024) not null,
question_point_value decimal,
constraint question_exam_id_fk
foreign key references exams(exam_id)
ON DELETE CASCADE
);
小开

如果你想通过查询创建两个表的列到一个关系中，请尝试以下操作:

Alter table Foreign_Key_Table_name add constraint
Foreign_Key_Table_name_Columnname_FK
Foreign Key (Column_name) references
Another_Table_name(Another_Table_Column_name)
小开

我总是使用这种语法在两个表之间创建外键约束

Alter Table ForeignKeyTable
Add constraint `ForeignKeyTable_ForeignKeyColumn_FK`
`Foreign key (ForeignKeyColumn)` references `PrimaryKeyTable (PrimaryKeyColumn)`

即。

Alter Table tblEmployee
Add constraint tblEmployee_DepartmentID_FK
foreign key (DepartmentID) references tblDepartment (ID)

提交答案