In a database table, what is the maximum number of primary keys?

Question

In a database table, what is the maximum number of primary keys?

Answer 1

You can only have one. It must be on one or more columns that cannot accept NULs, and the column (or combination of columns) must be unique.

You can have other unique indexes, of one or more columns, but they can't be designated PKs. (But their behavior will be the same as if they were, so it's just semantics to say you can only have one.)

Answer 2

You only have one primary key (or possibly none, although that makes various things tricky) but the key can consist of more than one column.

Personally, I've found the composite primary keys (ones with more than one column) have been a pain when I've used them, but that could be due to my incompetence. Even so, in some of the cases it made logical sense to use a composite key and I probably wouldn't have changed the design. (I think it's more a case of Hibernate being slightly trickier to use with composite keys at the time.)

Answer 3

You can have only 1 primary key at max but it can be composite (having more than one columns in that key)

Answer 4

Primary key is always one for a table. Primary can be single column or combination of multiple columns which represents each row uniquely.

Answer 5

Well, if you're asking what's the maximum number of columns that can compose a primary key, for SQL Server it's 16.

Reference: Maximum Capacity Specifications for SQL Server.

Answer 6

In database theory, the most critical constraints on a table are called candidate keys. The values in the column(s) of a candidate key uniquely determine the values stored in the other columns in a row of the table - that is a functional dependency, and a key aspect of normalization theory. (Strictly, join dependencies are the key ones; a functional dependency is a special case of a join dependency.) A table may have multiple candidate keys. Of those candidate keys, at most one can be designated as 'the' primary key; the others become 'alternate' keys (but not, for some reason, 'secondary keys', though it seems like an obvious name for them).

My favourite illustration of multiple candidate keys is the 'table of elements' from chemistry and physics (and the fact that it is called a 'table' is nice):

CREATE TABLE elements
(
    atomic_number   INTEGER NOT NULL UNIQUE
                    CHECK (atomic_number > 0 AND atomic_number < 120),
    symbol          CHAR(3) NOT NULL UNIQUE,
    name            CHAR(20) NOT NULL UNIQUE,
    atomic_weight   DECIMAL(8,4) NOT NULL,
    stable          CHAR(1) DEFAULT 'Y' NOT NULL
                    CHECK (stable IN ('Y', 'N'))
);

It has 3 candidate keys - the atomic number, the symbol, and the name (and empirically, you could probably use the atomic weight as a fourth, but it isn't unique in the same way that the other three are). Any one of those could be designated as the primary key, but usually, either the atomic number or the symbol would be used. Which of those is preferred depends in large part on whether you are dealing with chemistry (in which case, the symbol is the hands-down winner) or (sub-)nuclear physics, in which case the atomic number is probably more important. Your secondary tables like the table of isotopes would cross-reference the atomic number; your secondary tables relating to chemical compounds would more likely cross-reference the symbol. (Incidentally, did you know that the 'as yet unisolated' elements with atomic numbers beyond 100 have 3-character abbreviations?)

Answer 7

I think the Madheena might have actually wanted to know how many rows a table can have that uses an auto incrementing primary key... and if so, that depends on the DBMS you're using and how you created the table (which data type the primary key is), so you'll have to be more detailed in your explanation.

Answer 8

A table can have only one Primary Key defined.

The number of rows / records that a table can hold, depends on the number of columns that spans your PK, and the data-type that has been choosen for those columns. Also, the RDBMS that you use matters, since the DBMS defines how large a data-type is.

For instance, a SQL Server table that has one single integer column as a primary key, can contain 4,294,967,295 records (PK values from -2,147,483,648 to 2,147,483,647).