Adding named foreign key constraints in a SQL Create statement
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
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.)