Referential Integrity in the Sample Database

This section demonstrates the table and referential constraint definitions on the sample database.

Creating the Course Table

The following statement creates the Course table.

CREATE TABLE Course
(Name CHAR(7) CASE,
Description CHAR(50) CASE,
Credit_Hours USMALLINT,
Dept_Name CHAR(20))

Adding a Primary Key to Course

The following statement adds a primary key (Name) to the Course table.

ALTER TABLE Course
ADD PRIMARY KEY (Name);

Creating the Student Table with Referential Constraints

The following statement creates the Student table and defines its referential constraints.

CREATE TABLE Student
(ID UBIGINT,
PRIMARY KEY (ID),
Cumulative_GPA NUMERICSTS(5,3),
Tuition_ID INTEGER,
Transfer_Credits NUMERICSA(4,0),
Major CHAR(20) CASE,
Minor CHAR(20) CASE,
Scholarship_Amount DECIMAL(10,2),
Cumulative_Hours INTEGER)

CREATE UNIQUE INDEX Tuition_ID ON Student(ID)

 

ALTER TABLE Student ADD CONSTRAINT
S_Tuition
FOREIGN KEY (Tuition_ID)
REFERENCES Tuition
ON DELETE RESTRICT