Fileid=Maketabs

maketabs.htm; updated 12/17/2001

Creating Oracle Tables
(Includes Referential Integrity Constraints
for Implementing Relationships)

GENERAL.

This web note explains the Create Table command for an Oracle Database.
The examples explain the clauses used to enforce referential integrity.

SIMPLE MODEL.

Initially we will examine two relationships shown in the figure below. The SectionOf relationship is a one-to-many relationship between the Course and Section entities. The Enroll relationship is a many-to-many relationship between the Student and Section entities.

The ER diagram depicts minimal non-key data in order to simplify the discussion without any loss of generality to a real-world situation. The following rules apply:

The primary identifier for the COURSE entity is the CourseNumber attribute.
The primary identifier for the SECTION entity is the concatenation of the SectionNumber, Term, and Year attributes, each course being assigned a unique SectionNumber within a specific Term and Year.
The primary identifier of the STUDENT entity is the SSN (social security number) attribute.

Types of Data Integrity.

Creating tables requires a basic understanding of the various tyeps of data integrity that need to be enforced.
Nulls. A Null rule defined on a column allows or disallows inserts and/or updates of rows containing a Null value.

You can specify Not Null as an integrity contraint for a column.
If a column is allowed to have a null value, you simply do not use an integrity contraint for that column.

Unique Column Values. This constraint restricts row insertion and updates to unique values for the specified column or set of columns.
Primary Key Values. This constraint specifies uniqueness for the primary key and can be applied to one column or a set of columns.
Referential Integrity. This rule guarantees that the value stored in a column in a table is a valid reference to an existing row in another table.

That is, the row inserted must reference a valid row in another table.
For example, when storing a value for CourseNumber in the SECTION table, the database management system software (DBMS) will ensure that the value for CourseNumber exists within the CourseNumber column of the COURSE table. SECTION references COURSE.
Referential Integrity does permit foreign keys to have null values; however, this is uncommon. As an example, a student might have a Major and this would reference the Majors Table (not shown in our ER diagram). Some students, however, would not have declared a Major.
If a foreign key is part of a primary key, then null values are not allowed.

Restrict. This constraint disallows the update or deletion of referenced data.
Set to Null. This constraint ensures that any action that updates or deletes referenced data causes associated data values in other ables to be set to Null when an appropriate referenced value is no longer defined.
Set to Default. This contraint ensures that any action that updates or deletes referenced data values causes associated data values to be set to a default value.
Cascade. When a referenced row is deleted, all dependent rows in other tables are also deleted. Likewise, when a key value is updated in a master table, the corresponding foreign key values are updated in referenced tables.
No Action. This constraint differs from Restrict in that it disallows the update or deletion of referenced data, but the rule is checked at the end of a statement or transaction. This is the default action used by Oracle.
Check. This is a constraint on a column or set of columns that enforces a specified condition, e.g., Check Hours < 6 would enforce the storage of values for the Hours column that are less than 6.

A Check integrity constraint on a column or set of columns requires that a specified condition be true or unknown for every row of the table.
If a data manipulation language (DML) statement results in the condition of the Check constraint evaluating to false, the statement is rolled back and the row is not stored in the table because the data would violate the Check constraint.
Check constraints enable you to enforce very specific or sophisticated integrity rules by specifying a check condition.
The condition of a Check constraint has some limitations:

It must be a Boolean expression evaluated using the values in the row being inserted or updated.
It cannot contain subqueries, sequences, the SQL functions SYSDATE, UID, USER, or USERENV, or the pseudocolumns LEVEL or ROWNUM.

A single column can have multiple Check constraints that reference the column in its definition. There is no limit to the number of Check constraints that you can define on a column.

IMPLEMENTATION GUIDELINES.

Following the rules for converting an ER diagram to the relational model, the one-to-many relationship between Course and Section is implemented by embedding the CourseNumber attribute within the table that will store Section data as a foreign key.
Tables will be created for COURSE, SECTION, and STUDENT. These are master tables.
The many-to-many Enroll relationship will be a table with a concatenated primary key from the SECTION and STUDENT tables. This is an Intersection table.
We will assume that the University's inventory of courses is fairly stable so that the PCTFREE value for the COURSE will be very low for this table.

Courses are rarely deleted.
New courses are rarely added.
Course descriptions do not change very often.

CREATING THE COURSE AND STUDENT MASTER TABLES AND INSERTING DATA.

This gives the SQL code to create the COURSE and STUDENT tables.
Note that the SQL code shown below has an USING INDEX clause that causes the index for the primary key to be stored to the tablespace named DATA_INDEX while the tables named COURSE and STUDENT are stored to the tablespace named DATA.
Each constraint is named using prefixes to denote the type of constraint. If you don't name the constraints, then Oracle assigns default names to the constraints.

PK stands for Primary Key constraint.
NN stands for Not Null constraint.
FK stands for Foreign Key constraint.
CK stands for a Check constraint.

CREATE TABLE Course (

CourseNumber CHAR(8)

CONSTRAINT PK_Course

PRIMARY KEY

USING INDEX TABLESPACE Data_Index

PCTFREE 5,

Description VARCHAR(50)

CONSTRAINT NN_Cou_Desc NOT NULL,

Hours Number(2)

CONSTRAINT NN_Cou_Hours NOT NULL)

PCTFREE 5 PCTUSED 60

STORAGE ( INITIAL 48K

NEXT 48K

PCTINCREASE 1

MINEXTENTS 1

MAXEXTENTS 300 )

TABLESPACE Data;

CREATE TABLE Student (

SSN CHAR(9)

CONSTRAINT PK_Student

PRIMARY KEY

USING INDEX TABLESPACE Data_Index
PCTFREE 5,

StudentName VARCHAR(50)

CONSTRAINT NN_Stu_Name NOT NULL,

DateBirth Date Default NULL)

PCTFREE 10 PCTUSED 40

STORAGE ( INITIAL 48K

NEXT 48K

PCTINCREASE 1

MINEXTENTS 1

MAXEXTENTS 300 )

TABLESPACE Data;

We now insert several rows of data into COURSE and STUDENT.

INSERT INTO Course VALUES ('CMIS108','Intro to Computers',3);

INSERT INTO Course VALUES ('CMIS142',
'VB Programming',3);

INSERT INTO Student VALUES ('111111111',
'Charley Daniels','01-MAR-40' );

INSERT INTO Student VALUES ('222222222',
'Faith Hill','05-FEB-60');

You can verify the creation of the table and the index by the commands:

SELECT Table_Name, Tablespace_Name
FROM Tabs
WHERE Table_Name = 'STUDENT' Or Table_Name = 'COURSE';

SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS
FROM Dba_Indexes
WHERE Table_Name = 'STUDENT' Or Table_Name = 'COURSE';

CREATING THE SECTION MASTER TABLE.

Next we create the Table SECTION that is also a master table, and insert a test row into this table.
Note the CourseNumber foreign key in SECTION that references the primary key from the COURSE table.
We give this constraint a name of CouSecFK and enforce it with the CONSTRAINT clause.
Note the use of the ON DELETE CASCADE clause to cause rows of the Section table to be deleted when corresponding rows in Course are deleted. This may not be a good option for this implementation, but it is shown here to demonstrate the use of the clause.
Note the use of the CONSTRAINT and PRIMARY KEY clause to create the concatenated primary key and the storage of the key in the Data_Index tablespace.

CREATE TABLE Section (

SectionNumber NUMBER(6),

Term CHAR(2),

Year Number(4),

Location VARCHAR(20)

CONSTRAINT NN_Location NOT NULL,

CourseNumber CHAR(8)

CONSTRAINT NN_Sect_CourseNumber NOT NULL

CONSTRAINT FK_CouSec

REFERENCES Course(CourseNumber),

CONSTRAINT PK_Section

PRIMARY KEY (SectionNumber, Term, Year)

USING INDEX TABLESPACE Data_Index

PCTFREE 5

)

PCTFREE 20 PCTUSED 65

STORAGE ( INITIAL 48K

NEXT 48K

PCTINCREASE 1

MINEXTENTS 1

MAXEXTENTS 300 )

TABLESPACE Data;

You can verify the creation of the table and the index by the commands:

SELECT Table_Name, Tablespace_Name
FROM Tabs
WHERE Table_Name = 'SECTION';

SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS
FROM Dba_Indexes
WHERE Table_Name = 'SECTION';

TESTING REFERENTIAL INTEGRITY AND UNIQUENESS CONSTRAINTS FOR THE SECTION TABLE.

We now demonstrate that the referential and uniqueness integrity constraints work by first inserting a good row into SECTION.

INSERT INTO Section
VALUES (111111,'FA',2000,'FH-3208','CMIS108');

We follow this by inserting a row with an invalid NULL Location field that violates the NN_Location constraint.

INSERT INTO Section
VALUES (222222,'FA',2000,NULL,'CMIS108');

ERROR at line 1:
ORA-01400: cannot insert NULL into ("DBOCK"."SECTION"."LOCATION")

Test the referential integrity constraint by attempting to insert a row into SECTION for a non-existent CMIS342 COURSE value.

INSERT INTO Section
VALUES (333333,'FA',2000,'FH-3103','CMIS342');

ERROR at line 1:
ORA-02291: integrity constraint (DBOCK.FK_COUSEC) violated - parent key not found

Attempts to insert a row with a duplicate primary key yields the following system error message because a row with primary key 001 already exists.

INSERT INTO Section
VALUES (111111,'FA',2000,'FH-3208','CMIS142');

ERROR at line 1:
ORA-00001: unique constraint (DBOCK.PK_SECTION) violated

CREATING THE ENROLL TABLE AND TESTING CONSTRAINTS.

Next we create the ENROLL table which implements the many-to-many relationship between STUDENT and SECTION.
Note the column constraint for the Grade column, the table constraint used to specify the composite primary key, and the referential integrity constraint enforced through the FOREIGN KEY clauses.
Also note the use of the ON DELETE CASCADE clause. This provides an alternative form of the Foreign Key enforcement to delete related rows if the related STUDENT or SECTION rows are deleted.

CREATE TABLE Enroll (

SSN CHAR(9),

SectionNumber NUMBER(6),

Term CHAR(2),

Year NUMBER(4),

Grade CHAR(2)

CONSTRAINT CK_Grade_Check

CHECK (Grade IN ('A','B','C','D','E','WP')),
CONSTRAINT FK_Enr_Stu_SSN FOREIGN KEY (SSN)

REFERENCES Student

ON DELETE CASCADE,

CONSTRAINT FK_Enr_SectionNumber

FOREIGN KEY (SectionNumber,Term,Year)

REFERENCES Section

ON DELETE CASCADE,

CONSTRAINT PK_Enroll

PRIMARY KEY (SSN, SectionNumber, Term, Year)

USING INDEX TABLESPACE Data_Index

PCTFREE 5

)

PCTFREE 30 PCTUSED 65

STORAGE ( INITIAL 48K

NEXT 48K

PCTINCREASE 0

MINEXTENTS 1

MAXEXTENTS 300 )

TABLESPACE Data;

The results of inserting valid rows and invalid rows are given below.
This command inserts a valid row with a Null grade value. Note that the system allows a Null value since Not Null was not specified as a constraint for the Grade column..

INSERT INTO Enroll
VALUES ('111111111','111111','FA',2000,NULL);

This attempts to insert a row that violates referential integrity to the STUDENT table.

INSERT INTO Enroll
VALUES ('999999999','111111','FA',2000,'A');

ERROR at line 1:
ORA-02291: integrity constraint (DBOCK.FK_ENR_STU_SSN) violated - parent key not found

This attempts to insert a row that violates referential integrity to the SECTION table.

INSERT INTO Enroll
VALUES ('222222222','999999','FA',2000,'A');

ERROR at line 1:
ORA-02291: integrity constraint (DBOCK.FK_ENR_SECTIONNUMBER) violated - parent key not found

This attempts to insert a row that violates Check Constraint for the Grade column. The row is rejected because the letter grade 'G' is not in the acceptable list of values.

INSERT INTO Enroll
VALUES ('222222222','111111','FA',2000,'G');

ERROR at line 1:
ORA-02290: check constraint (DBOCK.CK_GRADE_CHECK) violated

VERIFY THE EXISTENCE OF THE TABLES, INDEXES, and CONSTRAINTS

The table creation process also created several indexes to store information about primary key values.
Verify the existence of the tables, their indexes and the specified constraints by running the following queries.

SELECT Table_name, Tablespace_name FROM user_tables;

SELECT index_name, Table_name, Tablespace_name
FROM user_indexes;

SELECT constraint_name, constraint_type, Table_Name, Search_Condition
FROM user_constraints;

UNARY RELATIONSHIPS.

A unary relationship (also called a recursive relationship) occurs when the rows within an individual table are related to other rows within the table.
For example, a database table for FACULTY can store the same type of information for each faculty member. The chairperson of an academic department is both a faculty member and a supervisor of other faculty members so the Supervisor relationship would represent an association among faculty members. This relationship is depicted below.

The code shown below creates the FACULTY table. The foreign key Fac_Supervisor_SSN implements the one-to-many relationship among the FACULTY rows. The Supervise relationship is implementated through use of referential integrity constraints named FK_Fac_Supervisor.
Also note that if a supervisor is deleted, the value for the cascading change to supervised faculty is set to Null through the ON DELETE clause of the foreign key.

CREATE TABLE Faculty (

Fac_SSN CHAR(9),

FirstName VARCHAR(25)

CONSTRAINT NN_FirstName NOT NULL,

LastName VARCHAR(25)

CONSTRAINT NN_LastName NOT NULL,

Fac_Dept VARCHAR(12),

Fac_Supervisor_SSN CHAR(9),

CONSTRAINT PK_Faculty

PRIMARY KEY (Fac_SSN)

USING INDEX TABLESPACE DATA_Index

PCTFREE 5,

CONSTRAINT FK_Fac_Supervisor

FOREIGN KEY (Fac_Supervisor_SSN)

REFERENCES Faculty

ON DELETE SET NULL

)

PCTFREE 15 PCTUSED 65

STORAGE ( INITIAL 48K

NEXT 48K

PCTINCREASE 0

MINEXTENTS 1

MAXEXTENTS 300 )

TABLESPACE Data;

Now insert some rows into Faculty. Note that the Supervisor of the Department (Bock) has a NULL value for the Fac_Supervisor_SSN column.

INSERT INTO Faculty
    VALUES ('123456789','Douglas','Bock','CMIS',NULL);
INSERT INTO Faculty
    VALUES ('234567890','Susan','Yager','CMIS','123456789');
INSERT INTO Faculty
    VALUES ('345678901','Anne','Powell','CMIS','123456789');

Select rows from the Faculty table. Note the value for the Fac_Supervisor_SSN column (labeled FAC_SUPER below).

SQL> column firstname format a10;
SQL> column lastname format a10;
SQL> select * from faculty;

FAC_SSN   FIRSTNAME LASTNAME   FAC_DEPT     FAC_SUPER
--------- ---------- ---------- ------------ ---------
123456789 Douglas    Bock       CMIS
234567890 Susan      Yager      CMIS         123456789
345678901 Anne       Powell     CMIS         123456789

Attempting to store a row that violates the referential integrity results in an error message from Oracle.

INSERT INTO Faculty
VALUES ('456789012','Joe','Prof','CMIS','444444444');

ERROR at line 1:
ORA-02291: integrity constraint (DBOCK.FK_FAC_SUPERVISOR) violated - parent key not found

Deleting Bock's record will cause the ON DELETE clause to reset the Fac_Supervisor_SSN values for the subordinate faculty rows to a Null value.

DELETE FROM faculty WHERE fac_ssn='123456789';

1 row deleted.

SELECT * FROM faculty;

FAC_SSN   FIRSTNAME LASTNAME   FAC_DEPT     FAC_SUPER
--------- ---------- ---------- ------------ ---------
234567890 Susan      Yager      CMIS
345678901 Anne       Powell     CMIS

LAST COMMENTS.

This handout only introduces the topic of table creation.
There are additional types of Oracle tables such as Indexed Organization tables and Clustered tables.

END OF NOTES