Table Constraint Examples

-- column-level primary key constraint named OUT_TRAY_PK:

START TRANSACTION;

CREATE TABLE TBL_SAMPLE_FLIGHTS
(
SENT TIMESTAMP,
DESTINATION CHAR(8),
SUBJECT CHAR(64) NOT NULL CONSTRAINT OUT_TRAY_PK PRIMARY KEY,
NOTE_TEXT VARCHAR(3000)
);

COMMIT;

INSERT INTO TBL_SAMPLE_FLIGHTS(
SENT,
DESTINATION,
SUBJECT,
NOTE_TEXT
) VALUES(
SYSDATE,
‘Atlanta’,
‘Note to other attendants’,
‘This is how this passanger wants to be treated on the flight’
);
commit;

 

Table Level Primary Key

— the table-level primary key definition allows you to
— include two columns in the primary key definition:

CREATE TABLE TBL_CLASS
(
CLASS_CODE CHAR(7) NOT NULL,
DAY SMALLINT NOT NULL,
STARTING TIME,
ENDING TIME,
PRIMARY KEY (CLASS_CODE, DAY)
);

 

 

— the table-level primary key definition allows you to
— include two columns in the primary key definition:
CREATE TABLE TBL_CLASS
(
CLASS_CODE CHAR(7) NOT NULL,
DAY SMALLINT NOT NULL,
STARTING TIME,
ENDING TIME,
PRIMARY KEY (CLASS_CODE, DAY)
);

— 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
(
EMPNR 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)
);

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top