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)
 );