What is the difference between a constraint primary key and normal primary key?

Question

I know Its possible to use the constraint to put multiple fields like this:

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

But If we had to compare these two below, is there is any difference?

Create table client (cod_clt int identity not null, Nom t, Dn datetime not null, Credit numeric(6,2) not null, Constraint x1 check (credit between 100 and 1456.25), Constraint x2 primary key (cod_clt) )

and this:

 Create table client (cod_clt int primary key, Nom t, Dn datetime not null, Credit numeric(6,2) not null, Constraint x1 check (credit between 100 and 1456.25) )

So there is no difference except having a constraint name that could help you later? — user3832856
– user3832856, Commented Apr 28, 2017 at 9:05
They are two different ways of performing the same thing, though your first form does enable you to name the PRIMARY KEY CONSTRAINT, whereas the other does not. Occasionally this is relevant depending on eactly what you are trying to do, but most of the time it is a matter of style. — toonice
– toonice, Commented Apr 28, 2017 at 9:08
As best practice, always give meaningfull names to your constraints. — Zohar Peled
– Zohar Peled, Commented Apr 28, 2017 at 9:41

Lander Van laer · Accepted Answer · 2021-12-14 16:10:22Z

There's 6 classes of constraints in SQL server:

NOT NULL
Unique Key
Primary Key
Foriegn Key
Check
Default

(Some, including myself, will argue that a column Data Type and Unique indexes are also types of constraints but I digress.)

In SQL Server there are two types of constraints: Column level and Table level.

NOT NULL is a column level constraint, all the others can be table or column level. Primary and foreign keys can consist of one or more columns; when they consist of more than one column they are known as a "composite key". Composite keys must be table-level.

The most notable difference between column and table level constraints is that table level allows you to give your constraints a meaningful name which is why I personally prefer them.

In your first example you have a table level primary key constraint and it is a composite key. In your last two examples don't have a composite key which is why it can be both table and column level. The key difference in your last two examples is that you are able to name the table level primary key but not the column level one. This is a big deal for people who properly manage their metadata.

Lastly, one thing that makes Primary Key & Unique constraints are special in that, when you create them, you can create an index. The default behavior for a primary key is to also create a clustered index. The decision to create a clustered index and/or unique index is a big one so I include the keywords clustered or nonclustered when I define my primary (and unique) keys so as not to depend on default system behavior for this.

Here's a couple good links about constraints:

https://technet.microsoft.com/en-us/library/ms189862(v=sql.105).aspx - (Microsoft) https://www.w3schools.com/sql/sql_constraints.asp (W3 Schools)

Collectives™ on Stack Overflow

What is the difference between a constraint primary key and normal primary key?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related