Managing Constraints   «Prev  Next»
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.

  1. This short version of the auction table contains five rows, each with its own unique, non-NULL value for the auction_id table
  2. User adds a new row to the auction table that has a value for the auction_id column unique to the table.
  3. The row is successfully inserted into the table.
  4. A user attempts to insert a row into the auction table that has a non-unique value for the auction_id column.
  5. The INSERT operation fails with an error, because a unique constraint has been violated.
  6. A user tries to update a row, but makes the value of the auction_id NULL.
  7. The INSERT operation fails with an error, because a unique constraint has been violated.

Primary Keys Implemented
The next lesson explains the UNIQUE constraint.

Primary Key constraints - Exercise

Click the Exercise link below to practice creating primary key constraints for the COIN database.
Primary Key constraints - Exercise