Primary keys, index and constrains in SQL

Question

Please correct if im wrong. And kindly point me to articles on this concept.

When we create a primary key, in the background there is automatically a unique index, clustered index, and a not null constraint created on that coloumn.

Does this also mean that if we create a not null constraint, [clustered index or non clustered index] and unique index on a column, then that column becomes a primary key?

I want to understand the core concept/relation between primary key, index and constrains.

Gordon Linoff · Accepted Answer · 2017-04-12T19:45:56.957

The primary key is the one that is declared as the "primary" key. Just having the characteristics doesn't make a key "primary". It has to be explicitly declared as such.

Different databases implement primary keys in different ways. Although primary keys are usually implemented with a clustered unique index, that is not a requirement.

The primary key is exactly what its name suggests: "primary". Any other column or group of columns can be declared both unique and not null. That does not make them primary keys. In some databases, you could even define another column or group of columns as not null, unique and clustered -- without that being the primary key.

In summary:

You can have any number of unique indexes on a table.
You can have any number of unique indexes on non-NULL columns on a table.
You can have at most one clustered index. In almost all cases, this would be the primary key. But is not required in all databases.
You can have at most one primary key. In almost all cases, this would be clustered, although that is not required in all databases.

For more detail, you should refer to the documentation of the database you are using.

If you have multiple columns comprising non-NULL, unique keys, then only one is "primary" -- that one that has been explicitly declared as primary.

Why would you have a non-clustered primary key? I can give one scenario. Imagine a database where UUIDs are the keys for rows. The company does not want to use auto-generated sequence numbers, because they provide information in the number.

However, UUIDs are remarkably bad candidates for clustered indexes, because inserts are almost never at the end. In this case, you might want to design the table with a clustered auto-generated sequential key, to speed inserts You might make this key the primary key. But, you want all foreign key references to use the UUID -- and you want all foreign key references to be to the primary key of the table.

My question refers to one column only. I thought i was clear. Does my question now make sense? — variable, Apr 12 '17 at 19:43
@variable, Gordon has specified that the PK should be explicitly declared — LONG, Apr 12 '17 at 19:44
if we have multiple column with the same constraints, how do you know which one is PK? — LONG, Apr 12 '17 at 19:54
@LONG . . . The one that is declared primary key. In most databases, the metadata has this information. — Gordon Linoff, Apr 13 '17 at 00:52

score 0 · Answer 2 · answered Apr 12 '17 at 19:39

0

No.

All the columns could be added with Not null and Non-clustered index and Unique But only ONE column could be PK.

And the Unique allows NULL while Primary Key does not.

You might be talking about Candidate Key, here is the ref: https://www.techopedia.com/definition/21/candidate-key

answered Apr 12 '17 at 19:39

LONG

4,490
2
17
35

Can you advice if unique is an index or constraint please? – variable Apr 12 '17 at 19:53
In the background - is an unique key implemented as an index or as a constraint? – variable Apr 12 '17 at 20:05
Both. It is implemented as a unique index; but it functions as a constraint, in that the optimizer considers it such and will optimize queries using that knowledge. – Ross Presser Apr 12 '17 at 20:52
Thanks Ross please can you share any article that covers this topic – variable Apr 13 '17 at 05:19

Primary keys, index and constrains in SQL

2 Answers2