1.1

-- Use a column-level constraint for an arithmetic check
-- Use a table-level constraint
-- to make sure that a employee's taxes does not
-- exceed the bonus
CREATE TABLE SAMP.EMP
(
EMPNO CHAR(6) NOT NULL CONSTRAINT EMP_PK PRIMARY KEY,
FIRSTNME CHAR(12) NOT NULL,
MIDINIT vARCHAR(12) NOT NULL,
LASTNAME VARCHAR(15) NOT NULL,
SALARY DECIMAL(9,2) CONSTRAINT SAL_CK CHECK (SALARY >=
10000), BONUS DECIMAL(9,2),
TAX DECIMAL(9,2),
CONSTRAINT BONUS_CK CHECK (BONUS > TAX)
);
-- use a check constraint to allow only appropriate
-- abbreviations for the meals
CREATE TABLE FLIGHTS
(
FLIGHT_ID CHAR(6) NOT NULL ,
SEGMENT_NUMBER INTEGER NOT NULL ,
ORIG_AIRPORT CHAR(3),
DEPART_TIME TIME,
DEST_AIRPORT CHAR(3),
ARRIVE_TIME TIME,
MEAL CHAR(1) CONSTRAINT MEAL_CONSTRAINT
CHECK (MEAL IN ('B', 'L', 'D', 'S')),
PRIMARY KEY (FLIGHT_ID, SEGMENT_NUMBER)
);
-- create a table with a table-level primary key constraint
CREATE TABLE CITIES
(
CITY_ID INTEGER NOT NULL CONSTRAINT CITIES_PK Primary Key ,
CITY_NAME VARCHAR(24) NOT NULL,
COUNTRY VARCHAR(26) NOT NULL,
AIRPORT VARCHAR(3),
LANGUAGE VARCHAR(16)
)
-- and a table-level foreign key constraint
CREATE TABLE METROPOLITAN
(
HOTEL_ID INT NOT NULL CONSTRAINT HOTELS_PK PRIMARY KEY,
HOTEL_NAME VARCHAR(40) NOT NULL,
CITY_ID INT CONSTRAINT METRO_FK REFERENCES CITIES
)
-- add a unique constraint to a column
CREATE TABLE METROPOLITAN
(
HOTEL_ID INT NOT NULL CONSTRAINT HOTELS_PK,
HOTEL_NAME VARCHAR(40) NOT NULL,
CITY_ID INT ,
constraint ID_Unique unique (HOTEL_ID)
)
vFabric SQLFire User's Guide482
vFabric SQLFire Reference