Oracle Data Definition

[Creating Tables |Altering Tables |Removing Tables]

Creating Tables

Easy Format

     Create Table tablename
         (attribute name datatype constraint,
          attribute name datatype constraint,
            .
            .
            .)

An example table is

    Create Table Patron
       (PID NUMBER(6) Constraint pk_patron PRIMARY KEY,
        Name VARCHAR2(30) Constraint nn_Name NOT NULL
        Gender VARCHAR2(1))

The above table is called patron. It contains a PID that is the key, a name attribute and a gender attribute. PID is defined to be a 6 digit integer. Name and gender are defined to be character strings with 30 and 1 char. length respectively. Note that all constraints (e.g., Primary Key) must have a constraint name (e.g., pk_patron).

In the case where a table has a composite primary key, the constraint must be formed differently. For example,

    Create table Request
      ( CallNo VARCHAR2(10) Constraint fk_callno References Book (CallNo),
      PID NUMBER(6) Constraint fk_pid References Patron (PID),
      RDate DATE,
      Constraint pk_Request PRIMARY KEY (CallNo, PID))

Notice that Constraints are placed after the attributes. The References constraint says that the PID in Request must match a PID in patron (i.e. Foreign Key). The data type of RDate is a special data type called DATE.

More complex table specifications can also be created.

Legal Data Types

VARCHAR2(size): Variable length character string having max. length of size
CHAR(size): Fixed length character string with number of bytes equal to size
NUMBER(p,s): Number having a precision p and s digits to the right of the decimal. If you leave off p and s (e.g., NUMBER), then it is a floating point number.
LONG: Character data of variable length up to 2 gigabytes (cannot be a key)
DATE: A date field
RAW(size): Raw binary data of length size. Max. size is 255 bytes
LONG RAW: Raw binary data up to 2 gigabytes (cannot be a key)

Constraint clause

This clause is part of the create or alter table commands. The format is:

CONSTRAINT constraintname the constraint specifications

The following are some typical constraint specifications:

PRIMARY KEY--Attribute is unique, not null and indexed
UNIQUE--Attribute is unique and indexed but not the key
NOT NULL--Attrbute value cannot be left blank
REFERENCES--Foreign key constraint. The attribute must match a value of a specified attribute

More complex constraint clauses can also be added to the schema specification

Altering Tables

This command can be used to add, drop, and modify attributes and constraints. The format is:

ALTER TABLE tablename
  ADD attributename datatype constraint
  ADD tableconstraint
  MODIFY attributename datatype constraint
  DROP PRIMARY KEY
  DROP UNIQUE attributename  (drops the unique constraint on
an attribute)
  DROP CONSTRAINT constraintname

ALTER TABLE Patron
  DROP CONSTRAINT nn_name

It does not seem to be possible to change the name of an attribute or remove an attribute from a table.

Dropping Tables

To remove a table from the database use

DROP TABLE tablename

Last modified: September 1, 1998
Dirk Baldwin, MIS, UW-Parkside, dirk.baldwin@uwp.edu