Rumpleteaser Rumpleteaser - 29 days ago 14
SQL Question

Adding named foreign key constraints in a SQL Create statement

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 mssql i also need it in postgresql

Answer

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