| Lesson 3 | PRIMARY KEY constraints |
| Objective | Create PRIMARY KEY constraints on database tables |
Create PRIMARY KEY constraints on database tables
A primary key is a column or series of columns that uniquely identifies each individual row in a table. The PRIMARY KEY constraint is used to denote which columns in a table act as the primary key for the table.
You can have only one primary key for each table. When you choose a single column as a primary key, you must have a non-NULL value for the column that is unique for the row.
If you choose a series of columns as the primary key for a table, you must have at least one of the columns contain a non-NULL unique value.
Although you are not required to have a primary key for every table, it is good design practice to include a primary key in your table design.
This is so you can uniquely identify an individual row based on data values. In addition, you must have a PRIMARY KEY constraint on a
parent table to allow a FOREIGN KEY constraint on a child table, as described later in this module.
psuedo Column rowId
The Oracle database includes a unique identifier for every row in the database through the use of a
ROWID.Click the Audio button to hear about
ROWIDs.
Example
In the COIN database, the CREATE TABLE command for the AUCTION table, including a PRIMARY KEY constraint, would be:
CREATE TABLE AUCTION ( auction_id NUMBER,
start_time DATETIME, stop_time DATETIME,
CONSTRAINT pk_auction PRIMARY KEY (auction_id))
The operation of a PRIMARY KEY constraint
To see how a PRIMARY KEY constraint operates, use the following SlideShow to view the various ways that users can interact with a PRIMARY KEY constraint.
- This short version of the auction table contains five rows, each with its own unique, non-NULL value for the auction_id table
- User adds a new row to the auction table that has a value for the auction_id column unique to the table.
- The row is successfully inserted into the table.
- A user attempts to insert a row into the auction table that has a non-unique value for the auction_id column.
- The INSERT operation fails with an error, because a unique constraint has been violated.
- A user tries to update a row, but makes the value of the auction_id NULL.
- The INSERT operation fails with an error, because a unique constraint has been violated.
Primary Key constraints - Exercise