CODEWITHSUNDEEP
sqlsql-server
user11224591
user11224591

Reputation:

Use CONSTRAINT keyword when creating a table

A question on using the CONSTRAINT keyword when creating a new table. I saw some code like this below:

CREATE TABLE dbo.T1
(
    keycol INT NOT NULL IDENTITY(1, 1) 
        CONSTRAINT PK_T1 PRIMARY KEY,
    datacol NVARCHAR(40) NOT NULL
);

My question is, isn't NOT NULL also a CONSTRAINT the same as PRIMARY KEY, so why do we place CONSTRAINT keyword for PRIMARY KEY, but not for NOT NULL?

Upvotes: 1

Views: 4856

Answers (2)

Bart Hofland
Bart Hofland

Reputation: 3905

The NOT NULL constraint can be modified using an ALTER TABLE ALTER COLUMN statement. Therefore, an explicit name for a NOT NULL constraint is useless. The name of a NOT NULL constraint will not be stored in the database's metadata.

Other constraints (primary key, foreign key, unique, check, and default) can be removed using an ALTER TABLE DROP CONSTRAINT statement. For such statements, a constraint name has to be specified. So technically a constraint name is always required for such constraints.

But constraint names are always optional in the SQL syntax, so you can always omit CONSTRAINT [constraintname] when creating a constraint. So this is valid SQL too:

CREATE TABLE dbo.T1
(
    keycol INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    datacol NVARCHAR(40) NOT NULL
);

However, for constraints that actually will require a constraint name for removal, the DBMS will automatically generate a constraint name if one is not supplied explicitly. In the above CREATE TABLE statement, the primary key will get a name like PK__T1__98D78B44D915DA1F.

Explicitly naming your primary keys, foreign keys, unique constraints, check constraints and default constraints will ease future maintenance of your database tables. If you explicitly name your constraints, you always know exactly how the constraints are named. If you want to remove a "nameless" constraint, you have to look up its generated name in the database's metadata first (which I consider to be quite ugly and complex).

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 452977

As documented in CREATE TABLE

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

so you can use the CONSTRAINT keyword there if you want

CREATE TABLE dbo.T1
(
    keycol INT NOT NULL IDENTITY(1, 1) 
        CONSTRAINT PK_T1 PRIMARY KEY,
    datacol NVARCHAR(40) CONSTRAINT Foo NOT NULL
);

This is pointless though as the constraint keyword is only ever required for constraints when specifying a name. And when used with NOT NULL this does not actually create a constraint object in sys.constraints, so the name is not stored anywhere. It is just a property of the column whether or not it is nullable.

Upvotes: 1

Related Questions