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.

Answer 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