Primary Key In SQL

Question

I want to know if it is possible to create more than one primary key in standard SQL. I mean something like that:

CREATE TABLE(Surname CHAR(100) PRIMARY KEY, Name CHAR(100) PRIMARY KEY)

Is that legal in SQL? If not, please link me a reference to the standard that says that is not possible...

Answer 1

Edit after the question has been clarified.

The definition of a primary key is that there is one and only one. So, no you cannot create two primary keys on two different columm.

You can however create a primary key on one column and a unique constraint on another:

create table person
(
   surname varchar(100) not null primary key,
   name varchar(100) not null,
   constraint only_one_name unique (name)
);

The above is standard SQL for all I know.

Here is a link to the book "SQL-99, Complete" which re-states the SQL standard in a more pragmatic way: https://mariadb.com/kb/en/constraint_type-primary-key-constraint/

Quote from the book:

A Base table may be constrained by no more than one PRIMARY KEY Constraint

The original wording from the SQL standard (which is not free, so no one can give you a link to that):

A <table definition> shall specify at most one implicit or explicit <unique constraint definition> that specifies PRIMARY KEY.

(Emphasis mine)

---

Note that you almost never want char - especially not with a length greater than just two or three characters. The CHAR datatype pads all values to the defined length. So if you inserted the value 'FOO' into a CHAR(10) column it will (has to) be stored as 'FOO '

Answer 2

No, It's not legal to create two primary keys in SQL, If you are able to create two separate primary keys then it's not a primary key anymore.

You can create a composite primary keys, like primary key(surname, name) for e.g. but this will be never applicable not good pratice, primary key on name and surname.

Answer 3

No - but you can have a single primary key that is a combination of multiple columns:

From MSDN:

A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. You can create a primary key by defining a PRIMARY KEY constraint when you create or modify a table.

A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values. Because PRIMARY KEY constraints guarantee unique data, they are frequently defined on an identity column.

Answer 4

This is a bit pedantic, but of course you cannot create more than one primary key, just like there cannot be more than one tallest person in the room. But you can, (and many times should), create more than one unique key. and, except for one minor distinction there is no functional difference between them. A unique key can be used as the target of a FK constraint, or in joins just like a primary key can.

Answer 5

you can only have one primary key, but this primary key can be made by N columns.

example:

create table "TABLE NAME"(
    "Surname" CHAR(100),
    "Name" CHAR(100),
    primary key ("Surname", "Name")
);

Answer 6

You can have a primary key containing multiple columns. This is often done in attributive tables, where one part of the key contains the id of the record in the table you're attributing to.

You can't have more than one primary key simply because it is ranked as 'the most important'. There can't be two things 'most important'.