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)
);
|
