UNIQUE
Week #2: Articles
Learning Outcomes
- Learn how to create a column with a
UNIQUE
constraint. - Learn how to modify a column with a
UNIQUE
constraint.
NOT NULL
ConstraintCHECK
ConstraintUNIQUE
ConstraintPRIMARY KEY
ConstraintFOREIGN KEY
Constraint
UNIQUE
Constraint
UNIQUE
constraints are available in all relational databases. UNIQUE
constraints let you check whether the value of a column or set of columns are unique within all the rows of a table. Every well defined table should have one or more columns that uniquely identifies every row of the table. That column or set of columns is the natural key.
After you identify the natural key, you should create a unique constraint across the column or set of columns. You can create a unique constraint with the CREATE
or ALTER
statement. The CREATE
statement is the easiest because you don’t need to know if there are existing rows in the table. The ALTER
statement can only work when the column or set of columns identified as the natural key contains unique values.
A single column may include one row that is null. A set of columns may include a unique set of values in and the combination of values must be unique across all rows in a table.
Oracle lets you implement a UNIQUE
constraint in a CREATE
statement as either an in-line or out-of-line constraint. You may use the in-line syntax when there’s only one unique column but you must use a table-level or out-of-line when the unique constraint applies across more than one column. The alter command works when you have one column or a set of columns in a unique constraint.
CREATE
Statement:- You can define a
UNIQUE
constraint on the same line as you define a single column. This approach is called placing an inline constraint on a column.The following
sample
table has two named inline constraints:CREATE TABLE sample ( sample_id NUMBER CONSTRAINT sample_pk PRIMARY KEY , sample_text VARCHAR2(20) CONSTRAINT sample_ck UNIQUE);
The first inline constraint is a
PRIMARY KEY
constraint on thesample_id
column. You should note that thePRIMARY KEY
constraint makes a column applies three rules. It verifies thesample_id
column is mandatory and unique in the set of rows inside thesample
table, and designates thesample_id
column as a primary key that you can refer to from foreign key columns.The second is a
UNIQUE
constraint that makes thesample_text
column value unique within a table. Any attempt to add a row with a duplicate value in thesample_text
column would return the following error:* ERROR AT line 1: ORA-00001: UNIQUE CONSTRAINT (STUDENT.SAMPLE_PK) violated
If you describe the
sample
table with theDESCRIBE
command in SQL*Plus, you’ll see:Name Null? Type ----------------------------------------- -------- ---------------------------- SAMPLE_ID NOT NULL NUMBER SAMPLE_TEXT VARCHAR2(20)
If you want to preclude null values in the
sample_text
column and guarantee its uniqueness, you must use an out-of-line constraint, like the following:CREATE TABLE sample ( sample_id NUMBER CONSTRAINT sample_pk PRIMARY KEY , sample_text VARCHAR2(20) CONSTRAINT sample_nn NOT NULL , CONSTRAINT sample_ck UNIQUE (sample_text));
If you describe the
sample
table with theDESCRIBE
command in SQL*Plus, you’ll see:Name Null? Type ----------------------------------------- -------- ---------------------------- SAMPLE_ID NOT NULL NUMBER SAMPLE_TEXT NOT NULL VARCHAR2(20)
- You can define a table-level or out-of-line
UNIQUE
constraint when the table requires a multiple column unique constraint where some of the columns are optional and others are mandatory. For this section, you will use thecustomer
table. You can create thecustomer
table with a unique key across thefirst_name
,middle_name
, andlast_name
columns with the following syntax:CREATE TABLE customer ( contact_id NUMBER CONSTRAINT customer_pk PRIMARY KEY , username VARCHAR2(30) CONSTRAINT customer_nn1 NOT NULL , first_name VARCHAR2(20) CONSTRAINT customer_nn2 NOT NULL , middle_name VARCHAR2(20) , last_name VARCHAR2(20) CONSTRAINT customer_nn3 NOT NULL , CONSTRAINT customer_uq UNIQUE(first_name, middle_name, last_name));
The new customer table now only accepts unique combinations of the
first_name
,middle_name
, andlast_name
columns.
.
ALTER
Statement:- You can add or drop a
UNIQUE
constraint when you want to add acustomer_uq
constraint that includes thefirst_name
,middle_name
, andlast_name
columns.You drop an existing
customer_uq
UNIQUE
constraint with the following syntax:ALTER TABLE customer DROP CONSTRAINT customer_uq;
- After you drop the
customer_uq
UNIQUE
constraint, you can add a new version of thecustomer_uq
UNIQUE
constraint with the following syntax:ALTER TABLE customer ADD CONSTRAINT customer_uq UNIQUE(first_name, middle_name, last_name);