A primary key consists of one or more columns which uniquely identify each row in a table.
A primary key must be
A primary key should be
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.
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
Generally, surrogate keys are preferred as primary keys.