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