ALTER TABLE – SQL Command Examples

Example 1 adds a field called fax to the customer table and allows the field to have null values.

Example 2 makes the cust_id field the primary key of the customer table.

Example 3 adds a field validation rule to the quantity field of the orders table so that values in the quantity field must be non-negative.

Example 4 adds a one-to-many persistent relation between the customer and orders tables based on the primary key cust_id in the customer table and a new foreign key index cust_id in the orders table.

Example 5 removes the field validation rule from the quantity field in the orders table.

Example 6 removes the persistent relation between the customer and orders tables, but keeps the cust_id index tag in the orders table.

Example 7 adds a field called fax2 to the customer table and prevents the field from containing null values. The new structure of the table is displayed. Two ALTER COLUMN clauses are used to allow the field to have null values and set the default value for the field to the null value. Note that multiple ALTER COLUMN clauses are required to change more than one property of a field in a single ALTER TABLE command. The new field is then removed from the table to restore the table to its original state.

* Example 1
SET PATH TO (HOME(2) + 'Data\')     && Sets path to table
ALTER TABLE customer ADD COLUMN fax c(20) NULL

* Example 2
ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id

ALTER TABLE customer ALTER COLUMN cust_id c(5) PRIMARY KEY

* Example 3
ALTER TABLE orders;
    ALTER COLUMN quantity SET CHECK quantity >= 0;
    ERROR "Quantities must be non-negative"

* Example 4
ALTER TABLE orders;
   ADD FOREIGN KEY cust_id TAG cust_id REFERENCES customer

* Example 5
ALTER TABLE orders ALTER COLUMN quantity DROP CHECK

* Example 6
ALTER TABLE orders DROP FOREIGN KEY TAG cust_id SAVE

* Example 7
CLEAR
ALTER TABLE customer ADD COLUMN fax2 c(20) NOT NULL
DISPLAY STRUCTURE

ALTER TABLE customer;
   ALTER COLUMN fax2 NULL;
   ALTER COLUMN fax2 SET DEFAULT .NULL.

ALTER TABLE customer DROP COLUMN fax2