When creating primary and foreign keys is the CONSTRAINT keyword optional?

Question

When creating a new table, sometimes examples show the primary and foreign keys defined with the CONSTRAINT keyword and other times without the CONSTRAINT keyword. Is the only reason to use the CONSTRAINT keyword to give the constraint a name? Or are there other reasons that you would choose to use or not use this keyword?

Example 1 (without the keyword):

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

Example 2 (with the keyword):

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT PK_Person PRIMARY KEY (ID)
);

Other than assigning a specified constraint name, do DBMSes see these two examples differently? I'm thinking about SQL Server, Oracle, MySQL, and PostgreSQL in particular.

Hannah Vernon · Accepted Answer · 2017-04-20T03:55:26.027

For SQL Server, the Microsoft docs spell this out in excellent clarity (bold is my formatting):

CONSTRAINT
Is an optional keyword that indicates the start of the definition of a PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, or CHECK constraint.

Thus you do not need to specify CONSTRAINT xxx in your CREATE TABLE statement.

Note, however, that if you don't specify the constraint name, SQL Server will generate one for you if you include a constraint definition. This example shows creating a PRIMARY KEY constraint, without naming it.

CREATE TABLE dbo.ConstraintTest
(
    ConstraintTest_ID int NOT NULL
        PRIMARY KEY CLUSTERED
) ON [PRIMARY];

SELECT o_parent.name
    , o.name
FROM sys.key_constraints kc
    INNER JOIN sys.objects o ON kc.object_id = o.object_id
    INNER JOIN sys.objects o_parent on kc.parent_object_id = o_parent.object_id
WHERE o_parent.name = 'ConstraintTest';

DROP TABLE dbo.ConstraintTest

The results of the SELECT statement above:

+----------------+--------------------------------+
|      name      |              name              |
+----------------+--------------------------------+
| ConstraintTest | PK__Constrai__471ED41490593601 |
+----------------+--------------------------------+

This leads to the generally accepted best-practice of naming all constraints, as in:

CREATE TABLE dbo.ConstraintTest
(
    ConstraintTest_ID int NOT NULL
        CONSTRAINT PK_ConstraintTest
        PRIMARY KEY CLUSTERED
) ON [PRIMARY];

As always, the "It Depends™" moment here is for temporary tables; we almost never specify the name of a constraint applied to a temp table since we want them to be unique, and allowing SQL Server to name them ensures they are in fact unique. If you do specify the constraint name, then only a single copy of that temp table can exist at any given time; attempting to create more than one copy of the temp table will result in an error:

Msg 2714, Level 16, State 5, Line 19
There is already an object named 'PK_ConstraintTest' in the database.
Msg 1750, Level 16, State 0, Line 19
Could not create constraint. See previous errors.

You can easily add a UNIQUE constraint to an existing column without a name; as in:

ALTER TABLE dbo.Blah
ADD UNIQUE (SomeColumn);

In the example you've added to your question, the only difference is you are not specifying a constraint name, thereby allowing SQL Server to generate a name for the constraint. Other than the name of the constraint, there is no difference between the two tables.

When creating primary and foreign keys is the CONSTRAINT keyword optional?

1 Answers1