Oracle Unique Constraint

A unique constraint ensures that a column, or combination of columns, contains only unique values.

CREATE TABLE continent (
  cont_id    NUMBER        NOT NULL,
  cont_name  VARCHAR2(20)  NOT NULL UNIQUE
);

This makes sure that no two continents can exist with the same name.

INSERT INTO continent (cont_id, cont_name) VALUES (1, 'Africa');

INSERT INTO continent (cont_id, cont_name) VALUES (2, 'Africa');
-- SQL Error: ORA-00001: unique constraint (LISH.SYS_C0014607) violated

Combinations of columns can also be unique. Suppose we introduce a rule that no duplicate city names can exist in the same country. We can enforce the rule like this.

CREATE TABLE city (
  city_id     NUMBER        NOT NULL,
  ctry_id     NUMBER        NOT NULL,
  city_name   VARCHAR2(50)  NOT NULL,
  city_area   NUMBER,
  city_pop    NUMBER        NOT NULL,
  UNIQUE (ctry_id, city_name)
);

INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (1, 53, 'Sydney', 4689, 4399722);

INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (2, 53, 'Sydney', 4689, 4399722);
-- SQL Error: ORA-00001: unique constraint (LISH.SYS_C0014619) violated

primary key