Adding named foreign key constraints in a SQL Create statement

Question

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.

Solution 1

In SQL Server, you can use the constraint keyword to define foreign keys inline and name them at the same time.

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

Solution 2

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

Comments

[Rumpleteaser]

While I can see that your answer is neat, I will be applying w0lf's answer in this case as it fits what I am looking for.

[Adir D]

@LauraHansen just note that the syntax where you add constraints at the end of the table definition is more flexible, in the event you ever have multi-column constraints.

[Ian Kemp]

This is the more correct answer as per @AaronBertrand's comments. However, it's important to note that in MSSQL at least, constraint name maximum length is 128 characters.

[Aluan Haddad]

I really find inline constraints easier to read and verify. There is less repetition and it is clear what it applies to as opposed to having to verify the name.