CODEWITHSUNDEEP
sqlsql-serverpostgresqlconstraintscreate-table
Rumpleteaser
Rumpleteaser

Reputation: 4234

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 SQL Server and I also need it in PostgreSQL.

Upvotes: 60

Views: 53549

Answers (2)

Cristian Lupascu
Cristian Lupascu

Reputation: 40576

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

Upvotes: 98

Matthew Moisen
Matthew Moisen

Reputation: 18329

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

Upvotes: 52

Related Questions