Make your own free website on Tripod.com

CREATING AN ORACLE TABLE WITH SQL PLUS


This Table was created using SQL Plus to Oracle. The table will hold all of the Customer Informaton. The Primary Key will be the Customer ID. This Customer ID will be system generated using an auto-incrementing Oracle Sequence. There are two Integrity Constraints in this table. The first is a Unique Constraint in the table on Customer First and Last Name to prevent duplicate entries. The second is a Data Validation Rule to only allow customers from certain States. Notice that the fields with their respective Data Types are separated by commas, and that the entire statement is ended with a semi-colon which is referred to in Oracle as a Statement Terminator:

NOTE: For purposes of clarity, I have listed the items on individual lines. However, it is not necessary to use this convention in SQL Plus/Oracle. You may have one long statement; however, this format is much more readable.


Create or Replace Table Customer

(CUSTOMERID Number(5) Primary Key,

CompanyName VARCHAR2(50),

LastName VARCHAR2(50) Not Null,

FirstName VARCHAR(50) Not Null,

Address VARCHAR2(100),

City VARCHAR(50),

State CHAR(2),

ZipCode Varchar2(10),

Phone VARCHAR2(20),

Fax VARCHAR2(20),

UNIQUE (LastName, FirstName),

CHECK (State IN ('MA','HI', 'NH',

'ME', 'VT', 'RI') ) );


RETURN TO HOME PAGE