lishman levelup
«previous  next»


SQL Basics
Joins
Constraints



Oracle Primary Key Constraint


A primary key consists of one or more columns which uniquely identify each row in a table.

A primary key must be
  • Unique
  • Not Null
A primary key should be
  • Immutable
There can be only one primary key per table and it is defined like this:
CREATE TABLE continent (
  cont_id    NUMBER        PRIMARY KEY,
  cont_name  VARCHAR2(20)  NOT NULL
);
A primary key combines the NOT NULL constraint and the unique constraint into a single declaration.
INSERT INTO continent (cont_id, cont_name) VALUES (1, 'Africa');

INSERT INTO continent (cont_id, cont_name) VALUES (1, 'Asia');
-- SQL Error: ORA-00001: unique constraint (LISH.SYS_C0014654) violated

UPDATE continent SET cont_id = null WHERE cont_id = 1;
-- SQL Error: ORA-01407: cannot update ("LISH"."CONTINENT"."CONT_ID") to NULL
Ideally the value of a primary key should not change during the lifetime of the row, although technically there is nothing stopping us from doing so.

Keys

Let's take a moment to look at keys in our database. A key is a value which uniquely identifies a row. Several of the tables already have descriptive values which uniquely identify rows and could be considered candidates for keys, for example CONT_NAME or CTRY_NAME.

However, we have also chosen to add a numeric identifier column to each table which also contains a unique value (CONT_ID, CTRY_ID, HOS_ID etc).

The first type of key (the _NAME columns) are called natural keys, domain keys or sometimes business keys. They are values that occur in the real world and the values that will be seen by end users of an application.

The second type of key (the _ID columns) are called surrogate keys. These are artificial keys that contain values which are used internally by the application, but are rarely used directly by end users. These values are initially generated by the application or by the database itself.

Generally, surrogate keys are preferred as primary keys.
»