Oracle:Data Definition Language

Data Definition Language Commands (DDL)

This section looks at how tables can be created.

Creating a Table

The code that produced the sample database can be seen in Appendix A.

The following command illustrates how the sample DEPT table was created. DO NOT TYPE THIS COMMAND IN!

CREATE TABLE DEPT ( DEPTNO NUMBER(2) constraint PK_DEPT primary key, DNAME VARCHAR2(14) NOT NULL constraint UC_DNAME check (DNAME=upper(DNAME)), LOC VARCHAR2(13) constraint UC_LOC check (LOC=upper(LOC)) );

The figure in brackets after the data type indicates the length.

The following constraints have been implemented on the Department table to ensure that the integrity of the database is maintained:

• The department number attribute DEPTNO is the primary key because of the constraint PK_DEPT. This also means that the user must insert a value into this attribute when inserting or updating information in this table.
• The department name attribute DNAME must be entered because of the NOT NULL clause and it must be in uppercase because of the constraint UC_DNAME.
• The location attribute LOC may be left blank because of the absence of a NOT NULL clause. If it is entered it must be in uppercase because of the constraint UC_LOC.

The constraint names (e.g. PK_DEPT) are specified so that Oracle can use these as labels for the constraint in its Data Dictionary.

The basic valid data types are:

Type	Precision	Scale	Description
CHAR	n	-	Fixed character of width n
DATE	-	-	Date
NUMBER	n	d	Numeric of width n with d decimal places
VARCHAR2	n	-	Variable character of width n

The following command creates a table called PROJ with three columns PROJNO, PNAME and BUDGET. You should type this in:

CREATE TABLE PROJ (
   PROJNO NUMBER(5) constraint PK_PROJ primary key,
   PNAME VARCHAR2(32) constraint UC_PNAME check (PNAME=upper(PNAME)),
   BUDGET NUMBER(8,2) NOT NULL
   constraint BD_BUDGET check (BUDGET >= 0)
 );

The following constraints have been implemented on the project table PROJ:

• PROJNO is the primary key and is a number because we will use a sequence to generate the next number automatically when a new record is inserted.
• BUDGET cannot have a null value.
• PNAME is a string of 32 characters.
• BUDGET is a number of maximum width 8 characters and with two decimal places.

Composite Primary and Foreign Keys

The primary key (PK) for tables with composite primary keys are defined slightly different from above, the clause PRIMARY KEY is not put after each field taking part in the PK, but as a separate clause. For example:

CREATE TABLE Tablename1 (col1 VARCHAR2(10), col2 VARCHAR2(10), col3 VARCHAR2(3), CONSTRAINT PK_TAB PRIMARY KEY (col1,col2));

Single foreign keys (FK) can be defined using the REFERENCES clause (see the definition for the EMP table in Appendix A for an example).

Composite foreign keys can be defined using the FOREIGN KEY clause, for example:

CREATE TABLE Tablename2 (col1 VARCHAR2 (10), col2 VARCHAR2 (10), ......,

CONSTRAINT FK_TAB2 FOREIGN KEY (col1,col2) REFERENCES Tablename1)

This ensures that any values put into the col1 and col2 columns exist in the related columns of Tablename1 so enforcing referential integrity.

If the column names are not the same in both tables, then the name of the related fields must also be specified:

CREATE TABLE Tablename3(newcol1 VARCHAR2 (10), newcol2 VARCHAR2 (10), ......,

CONSTRAINT FK_TAB3 FOREIGN KEY (newcol1,newcol2)

REFERENCES Tablename1 (col1,col2))

For example, to create a table EMPPROJ that records which projects an employee works on:

CREATE TABLE EMPPROJ (
    EMPNO NUMBER(4) constraint fk_emp REFERENCES EMP,
    PROJNO NUMBER(5) constraint fk_proj REFERENCES PROJ,
    HOURS NUMBER(4),
    CONSTRAINT pk_empproj PRIMARY KEY(empno, projno)
 );

To create a further table that keeps a history of an employee’s expenses per project:

CREATE TABLE EMPPROJ_EXPENSES (
   EMPNO NUMBER(4),
   PROJNO NUMBER(5),
   CLAIMDATE DATE,
   CLAIMTOTAL NUMBER(7,2),
   CONSTRAINT pk_ep_exp PRIMARY KEY(empno, projno, claimdate),
   CONSTRAINT fk_ep FOREIGN KEY(empno, projno) REFERENCES EMPPROJ
 );