在 SQL Create 语句中添加命名外键约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14511796/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Adding named foreign key constraints in a SQL Create statement
提问by Rumpleteaser
I currently have:
我目前有：
CREATE TABLE galleries_gallery (
id INT NOT NULL PRIMARY KEY IDENTITY,
title NVARCHAR(50) UNIQUE NOT NULL,
description VARCHAR(256),
templateID INT NOT NULL REFERENCES galleries_templates(id),
jsAltImgID INT NOT NULL REFERENCES libraryImage(id)
jsAltText NVARCHAR(500),
dateCreated SMALLDATETIME NOT NULL,
dateUpdated SMALLDATETIME NOT NULL,
lastUpdatedBy INT,
deleted BIT NOT NULL DEFAULT 0
);
But this adds constraints with auto generated names which make it hard to drop the constraint later. What do I need to add in order to name the constraints?
但这增加了自动生成名称的约束，这使得以后很难删除约束。我需要添加什么才能命名约束？
The above example is SQL Server and I also need it in PostgreSQL.
上面的例子是 SQL Server，我在 PostgreSQL 中也需要它。
回答by Cristian Lupascu
In SQL Server, you can use the constraintkeyword to define foreign keys inline and name them at the same time.
在 SQL Server 中，您可以使用constraint关键字内联定义外键并同时命名它们。
Here's the updated script:
这是更新后的脚本：
CREATE TABLE galleries_gallery (
id INT NOT NULL PRIMARY KEY IDENTITY,
title NVARCHAR(50) UNIQUE NOT NULL,
description VARCHAR(256),
templateID INT NOT NULL
CONSTRAINT FK_galerry_template
REFERENCES galleries_templates(id),
jsAltImgID INT NOT NULL
CONSTRAINT FK_gallery_jsAltImg
REFERENCES libraryImage(id)
jsAltText NVARCHAR(500),
dateCreated SMALLDATETIME NOT NULL,
dateUpdated SMALLDATETIME NOT NULL,
lastUpdatedBy INT,
deleted BIT NOT NULL DEFAULT 0
);
I just made a test and apparently the same thing also works in PostgreSQL: http://www.sqlfiddle.com/#!12/2ae29
我刚刚做了一个测试，显然同样的事情也适用于 PostgreSQL：http://www.sqlfiddle.com/#!12/ 2ae29
回答by Matthew Moisen
CREATE TABLE galleries_gallery (
id INT NOT NULL,
title NVARCHAR(50) NOT NULL,
description VARCHAR(256),
templateID INT NOT NULL,
jsAltImgID INT NOT NULL,
jsAltText NVARCHAR(500),
dateCreated SMALLDATETIME NOT NULL,
dateUpdated SMALLDATETIME NOT NULL,
lastUpdatedBy INT,
deleted BIT NOT NULL DEFAULT 0,
CONSTRAINT galleries_gallery_id_pk PRIMARY KEY (id),
CONSTRAINT galleries_gallery_title_uk UNIQUE (title),
CONSTRAINT galleries_gallery_tmpltid_fk FOREIGN KEY (templateID) REFERENCES galleries_templates (id),
CONSTRAINT galleries_gallery_jsAltImgIDfk FOREIGN KEY (isAltImgID) REFERENCES libraryImage (id)
);
Use the CONSTRAINT keyword to specify constraint names. IMO it is cleaner and more readable to do this end-of-TABLE rather than in-line (both are acceptable, as the second answer indicates), and this also allows you to create UNIQUE constraints on multiple columns, as well as multiple FKs to the same table. The CONSTRAINT keyword cannot be used for not null; a change to a not null constraint requires an ALTER TABLE MODIFY COLUMN ... null. Constraint names must be less than or equal to 30 characters. Use a standard naming convention. Personally I always use the table name prepended to the column name, which is devoweled if the constraint name is over 30 characters, followed by the constraint type (pk, fk, uk, etc.)
使用 CONSTRAINT 关键字指定约束名称。IMO 执行此表末尾而不是内联操作更清晰且更具可读性（两者都可以接受，如第二个答案所示），并且这还允许您在多个列以及多个 FK 上创建 UNIQUE 约束到同一张桌子。CONSTRAINT 关键字不能用于 not null；对非空约束的更改需要 ALTER TABLE MODIFY COLUMN ... null。约束名称必须少于或等于 30 个字符。使用标准命名约定。就我个人而言，我总是使用列名前面的表名，如果约束名称超过 30 个字符，则将其删除，然后是约束类型（pk、fk、uk 等）
