lishman levelup
«previous  next»


SQL Basics
Joins
Constraints



Oracle Integrity Constraints Quick Start


Quick Start contains a summary of all the SQL from this section. Cut and paste this script into a command line client such as SQL Developer or TOAD to experiment with the SQL syntax.
-- Remove the tables if they already exist
DROP TABLE visit;
DROP TABLE head_of_state;
DROP TABLE city;
DROP TABLE country;
DROP TABLE continent;


------- NOT NULL -------

-- Inline NOT NULL constraint.
-- Out-of-line not allowed for NOT NULL constraint.
CREATE TABLE continent (
  cont_id    NUMBER        NOT NULL,
  cont_name  VARCHAR2(20)  NOT NULL
);

-- Insert rejected.
-- Cannot insert null into cont_name.
INSERT INTO continent (cont_id, cont_name) VALUES (2, '');

INSERT INTO continent (cont_id, cont_name) VALUES (2, 'Asia');

-- Updated rejected.
-- Cannot update CONT_ID to null.
UPDATE continent SET cont_id = null;

DROP TABLE continent;


------- Unique -------

-- Inline unique constraint.
CREATE TABLE continent (
  cont_id    NUMBER        NOT NULL,
  cont_name  VARCHAR2(20)  NOT NULL UNIQUE
);

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

-- Insert rejected.
-- Duplicate values in CONT_NAME are not allowed.
INSERT INTO continent (cont_id, cont_name) VALUES (2, 'Africa');

-- Out-of-line unique constraint.
-- Must be out-of-line because multiple columns are specified.
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 rejected.
-- Duplicate CTRY_ID and CITY_NAMe combination.
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
  VALUES (2, 53, 'Sydney', 4689, 4399722);

DROP TABLE continent;


------- Primary Key -------

-- Inline primary key.
CREATE TABLE continent (
  cont_id    NUMBER        PRIMARY KEY,
  cont_name  VARCHAR2(20)  NOT NULL
);

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

-- Insert rejected.
-- Duplicate primary key value.
INSERT INTO continent (cont_id, cont_name) VALUES (1, 'Asia');

-- Update fails. Cannot update primary key to null.
UPDATE continent SET cont_id = null WHERE cont_id = 1;

-- Out-of-line primary key.
CREATE TABLE country (
  ctry_id         NUMBER        NOT NULL,
  cont_id         NUMBER        NOT NULL,
  ctry_name       VARCHAR2(50)  NOT NULL,
  ctry_area       NUMBER        NOT NULL,
  ctry_pop        NUMBER        NOT NULL,
  pop_upd_on      DATE,
  currency        VARCHAR2(50)  NOT NULL,
  PRIMARY KEY (ctry_id)
);

INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(14, 3, 'Germany', 137847, 82046000, '30-nov-08', 'Euro');

-- Insert fails.
-- Duplicate primary key value.
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(14, 3, 'Germany', 137847, 82046000, '30-nov-08', 'Euro');


------- Foreign Key -------

DROP TABLE country;

-- Inline foreign key.
CREATE TABLE country (
  ctry_id         NUMBER        NOT NULL,
  cont_id         NUMBER        NOT NULL REFERENCES continent,
  ctry_name       VARCHAR2(50)  NOT NULL,
  ctry_area       NUMBER        NOT NULL,
  ctry_pop        NUMBER        NOT NULL,
  pop_upd_on      DATE,
  currency        VARCHAR2(50)  NOT NULL
);

-- Insert fails.
-- No CONTINENT with a primary key value of 999999.
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(14, 999999, 'Germany', 137847, 82046000, '30-nov-08', 'Euro');

INSERT INTO continent (cont_id, cont_name) VALUES (3, 'Europe');

INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(14, 3, 'Germany', 137847, 82046000, '30-nov-08', 'Euro');

-- Update fails.
-- Cannot update foreign key value to non existent primary key.
UPDATE country SET cont_id = 999999;

-- Update fails.
-- Cannot update primary key with child rows.
UPDATE continent SET cont_id = 99999 WHERE cont_id = 3;

-- Drop table fails.
-- Cannot drop a table with child rows.
DROP TABLE continent;


------- Optional Foreign Key -------

DROP TABLE country;

-- Optional Foreign Key.
CREATE TABLE country (
  ctry_id         NUMBER        NOT NULL,
  cont_id         NUMBER        NULL REFERENCES continent,
  ctry_name       VARCHAR2(50)  NOT NULL,
  ctry_area       NUMBER        NOT NULL,
  ctry_pop        NUMBER        NOT NULL,
  pop_upd_on      DATE,
  currency        VARCHAR2(50)  NOT NULL
);


INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(14, null, 'Germany', 137847, 82046000, '30-nov-08', 'Euro');

-- Update fails.
-- Primary key does not exist.
UPDATE country SET cont_id = 999999;


------- ON DELETE CASCADE -------

DROP TABLE country;

-- Cascade Delete.
CREATE TABLE country (
  ctry_id         NUMBER        NOT NULL,
  cont_id         NUMBER        NOT NULL REFERENCES continent ON DELETE CASCADE,
  ctry_name       VARCHAR2(50)  NOT NULL,
  ctry_area       NUMBER        NOT NULL,
  ctry_pop        NUMBER        NOT NULL,
  pop_upd_on      DATE,
  currency        VARCHAR2(50)  NOT NULL
);

INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(14, 3, 'Germany', 137847, 82046000, '30-nov-08', 'Euro');
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(73, 3, 'Greece', 50949, 11257285, '1-jan-09', 'Euro');

SELECT ctry_name, cont_id
FROM country;

-- COUNTRY rows deleted also.
DELETE FROM continent WHERE cont_id = 3;

SELECT ctry_name, cont_id
FROM country;


------- ON DELETE SET NULL -------

DROP TABLE country;

-- Set null on delete
CREATE TABLE country (
  ctry_id         NUMBER        NOT NULL,
  cont_id         NUMBER        NULL REFERENCES continent ON DELETE SET NULL,
  ctry_name       VARCHAR2(50)  NOT NULL,
  ctry_area       NUMBER        NOT NULL,
  ctry_pop        NUMBER        NOT NULL,
  pop_upd_on      DATE,
  currency        VARCHAR2(50)  NOT NULL
);


INSERT INTO continent (cont_id, cont_name) VALUES (3, 'Europe');

INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(14, 3, 'Germany', 137847, 82046000, '30-nov-08', 'Euro');
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(73, 3, 'Greece', 50949, 11257285, '1-jan-09', 'Euro');

SELECT ctry_name, cont_id
FROM country;

-- CONT_ID on COUNTRY set to null.
DELETE FROM continent WHERE cont_id = 3;

SELECT ctry_name, cont_id
FROM country;


------- Check Constraint -------

DROP TABLE country;
DROP TABLE continent;

-- Inline check constraint.
CREATE TABLE continent (
  cont_id    NUMBER        NOT NULL PRIMARY KEY,
  cont_name  VARCHAR2(20)  NOT NULL CHECK(cont_name = initcap(cont_name))
);

-- Insert fails.
-- Check constraint is violated.
INSERT INTO continent (cont_id, cont_name) VALUES (4, 'North america');

-- Insert fails.
-- Check constraint is violated.
INSERT INTO continent (cont_id, cont_name) VALUES (4, 'North AMerica');

INSERT INTO continent (cont_id, cont_name) VALUES (4, 'North America');


-- Out-of-line check constraint.
CREATE TABLE country (
  ctry_id         NUMBER        NOT NULL,
  cont_id         NUMBER        NOT NULL,
  ctry_name       VARCHAR2(50)  NOT NULL,
  ctry_area       NUMBER        NOT NULL,
  ctry_pop        NUMBER        NOT NULL,
  pop_upd_on      DATE,
  currency        VARCHAR2(50)  NOT NULL,
  CHECK (ctry_pop > ctry_area)
);

-- Insert fails.
-- CTRY_AREA is greater than CTRY_POPULATION
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(14, 3, 'Germany',  82046000, 137847, '30-nov-08', 'Euro');

INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(14, 3, 'Germany', 137847, 82046000, '30-nov-08', 'Euro');


------- Named Constraints -------

DROP TABLE country;
DROP TABLE continent;

-- Inline named constrains.
CREATE TABLE continent (
  cont_id    NUMBER        CONSTRAINT continent_pk PRIMARY KEY,
  cont_name  VARCHAR2(20)  CONSTRAINT cont_name_nn NOT NULL
                           CONSTRAINT continent_uk1 UNIQUE
);

-- Out-of-line named constraints
CREATE TABLE country (
  ctry_id         NUMBER        NOT NULL,
  cont_id         NUMBER        NOT NULL,
  ctry_name       VARCHAR2(50)  NOT NULL,
  ctry_area       NUMBER        NOT NULL,
  ctry_pop        NUMBER        NOT NULL,
  pop_upd_on      DATE,
  currency        VARCHAR2(50)  NOT NULL,
  CONSTRAINT county_fk1 FOREIGN KEY (cont_id) REFERENCES continent,
  CONSTRAINT county_uk1 UNIQUE (ctry_name),
  CONSTRAINT county_cc1 CHECK (ctry_area > 0),
  CONSTRAINT county_cc2 CHECK (ctry_pop > 0)
);
                    
»