Databases Reference
In-Depth Information
no two employees can have the same social security number, Janice uses a
UNIQUE constraint when she adds this column to the EMPLOYEES table:
alter table employees
add (ssn varchar2(11)
constraint uk1_employees unique);
Table altered.
Janice is doing two things in one statement: adding the SSN column and add-
ing the named constraint. The column will still allow NULL values, but when it is
populated for an employee, it must not duplicate any other SSN value in the
EMPLOYEES table.
When the HR department tries to update two records with the same social
security number, the constraint prevents the second UPDATE command from
completing successfully:
update employees
set ssn = '987-65-4321'
where employee_id = 116;
1 row updated.
update employees
set ssn = '987-65-4321'
where employee_id = 117;
update employees
*
ERROR at line 1:
ORA-00001: unique constraint (HR.UK1_EMPLOYEES) violated
PRIMARY KEY
A
PRIMARY KEY constraint
is similar to a UNIQUE constraint, with two excep-
tions: a PRIMARY KEY constraint will not allow NULL values, and only one PRIMARY
KEY constraint is allowed on a table. A PRIMARY KEY constraint can be defined at
either the column level or the table level. A PRIMARY KEY constraint is important
when you want to find a way to uniquely reference a row in the table with the pri-
mary key in another table. The syntax for a PRIMARY KEY constraint is similar to
that of the UNIQUE constraint:
PRIMARY KEY constraint
A constraint that uniquely defines each
row of a table and prevents NULL values
from being specified in the column or
combination of columns. Only one
PRIMARY
KEY
constraint may be
defined on a table.
[CONSTRAINT
<constraint name>
]
PRIMARY KEY [(
<column>
,
<column>
, ...)]
