Specifying the Primary Key
What the primary key is and how it is to be used, we have not assumed that SQL “knows”. There is, however a constraints you can use to define a group of one or more column as primary key. Unsurprising, this is called the PRIMARY KEY constraint. The PRIMARY KEY constraint can be of the table or column variety.
Primary Keys of more Than One Column
The PRIMARY KEY constraint can also apply to multiple column, forcing a unique combination of values. Suppose your primary key is name (this is not recommended and is only mentioned for the purpose of illustration), and you have first and last name stored in two different column (so you could organize the data by either one). Obviously, neither the first nor last name can be forced to be unique by themselves, but we may well wish every combination of two to be unique. We can apply the PRIMARY KEY table constraint to the pair.
- CREATE TABLE EMPLOYEE_DETAILS_TWOPRIMARYKEY
- (FIRSTNAME VARCHAR(50) NOT NULL,
- LASTNAME VARCHAR(30) NOT NULL,
- CITY VARCHAR(50) NOT NULL,
- PRIMARY KEY(FIRSTNAME,LASTNAME))
One problem with this approach is that we may have to force the uniqueness by entering Mary Smith and M. Smith, for example – which can easily be confusing, because your employees may not know which is witch. This is one reason it is better to define some numeric column that can distinguish one row from another and have it be the primary key. You can then apply the UNIQUE constraint to the two name column, if desired.