如何在SQL Server中创建外键？

How do I create a foreign key in SQL Server?

我从来没有为SQL Server"手动编码"对象创建代码，并且外键修改在SQL Server和Postgres之间看似不同。这是我的sql到目前为止：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

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, Level 16, State 0, Line 9
Number of referencing columns in
foreign key differs from number of
referenced columns, table
'question_bank'.

你能发现错误吗？

如果您只想自己创建约束，可以使用ALTER TABLE

1 2	ALTER TABLE MyTable ADD CONSTRAINT MyTable_MyColumn_FK FOREIGN KEY ( MyColumn ) REFERENCES MyOtherTable(PKColumn)

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

1
2
3
4
5
6
7
8

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)
);

您还可以使用以下命令命名外键约束：

1	CONSTRAINT your_name_here FOREIGN KEY (question_exam_id) REFERENCES EXAMS (exam_id)

我喜欢AlexCuse的答案，但是每当你添加外键约束时你应该注意的是你希望如何处理引用表的行中引用列的更新，特别是你想要如何删除引用中的行待治疗的桌子。

如果创建约束，则：

1
2
3

ALTER TABLE MyTable
ADD CONSTRAINT MyTable_MyColumn_FK FOREIGN KEY ( MyColumn )
REFERENCES MyOtherTable(PKColumn)

..如果引用表中有相应的行，那么引用表中的更新或删除将会出错。

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

如果您改为创建它：

1
2
3
4
5

ALTER TABLE MyTable
ADD CONSTRAINT MyTable_MyColumn_FK FOREIGN KEY ( MyColumn )
REFERENCES MyOtherTable(PKColumn)
ON UPDATE cascade
ON DELETE cascade

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

(我并不是说应该更改默认值，默认值是谨慎的，这很好。我只是说这是一个创建constaints的人应该始终注意的事情。)

顺便说一句，创建表时可以这样做，如下所示：

1
2
3
4
5
6
7

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
)

1
2
3
4
5
6
7

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
);

- 那也行。 Pehaps有点更直观的构造？

如果要使用查询在关系中创建两个表的列，请尝试以下操作：

1
2
3
4

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)

在任何表上创建外键

1
2
3

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)

像你一样，我通常不会手工创建外键，但如果由于某种原因我需要脚本这样做我通常使用ms sql server management studio创建它，然后在保存然后更改之前，我选择Table Designer |生成更改脚本

这个脚本是关于使用外键创建表，我添加了参照完整性约束sql-server。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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
);

Necromancing。
实际上，正确地做这件事有点棘手。

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

-- 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

我总是使用这种语法来创建2个表之间的外键约束

1
2
3

ALTER TABLE ForeignKeyTable
ADD CONSTRAINT `ForeignKeyTable_ForeignKeyColumn_FK`
`Foreign key (ForeignKeyColumn)` REFERENCES `PrimaryKeyTable (PrimaryKeyColumn)`

即

1
2
3