TABLES

CREATE TABLE

Define the structure of a new table

Format:
CREATE TABLE tablename ({col–name type [(size)][constraint],…});

The ‘constraint’ clause in the CREATE TABLE statement is used to enforce referential integrity.  Specifically, PRIMARY KEY, FOREIGN KEY, and CHECK integrity can be set when you define the table.  The syntax for key and check constraints is shown below.

{PRIMARY KEY | FOREIGN KEY} (local-field) [REFERENCES foreign-field] }

for attribute constraints…

CHECK (condition)

Example: Define the student table
CREATE TABLE STUDENT
( STUID                      CHAR(5),
LNAME                     CHAR(10) NOT NULL,
FNAME                     CHAR(8),
MAJOR                     CHAR(7) CHECK (MAJOR IN (‘HIST’,’ART’,’CIS’)),
CREDITS                  INTEGER CHECK (CREDITS > 0),
PRIMARY KEY (STUID));

ALTER TABLE

Add a column to the “right” of an existing table, modify an existing attribute, or drop an existing column or constraint.

Format:
ALTER TABLE tablename
{ADD {col-name type [(size)] | constraint}}
| MODIFY {col_name type [(size)]
| DROP {col-name [drop-clause];

where the drop clause is …

            DROP {PRIMARY KEY | UNIQUE {col-name} | CONSTRAINT constraint|

Example1: Add column called MINOR to STUDENT table
ALTER TABLE STUDENT ADD MINOR CHAR(8);

Example2: Drop the MGR-SSN column
ALTER TABLE EMPLOYEE DROP MGR-SSN;

Example3: Modify existing attribute
ALTER TABLE DEPT MODIFY MINOR CHAR(10);

LIMITATIONS/ENHANCEMENTS:

When you add a column, all existing tuples get the extra column filled with NULL values.  You have to go in and update the column to enter valid data later to get rid of the NULLs.  You can only add or drop a single column at a time in the ALTER statement.

CREATE INDEX

Create an index on attribute(s) within a table. Indexes are used to improve system performance by providing a more efficient means of accessing selected attributes.

Format:
CREATE [UNIQUE] INDEX index-name ON table-name {(col-name [ASC | DESC])};

Example1: Create an index on the STUID attribute of the STUDENT table
CREATE INDEX stuindex ON STUDENT (STUID);

Example2: Create a unique index on SSN of EMPLOYEES and make it sort in reverse order
CREATE UNIQUE INDEX empindex ON EMPLOYEES (SSN) DESC;

Example 3: Create a composite index on COURSENUM and SDUID from ENROLL table
CREATE INDEX enroll-idx ON ENROLL (COURSENUM, STUID);

DROP TABLE

Remove a table (and all data) or an index on a table from database.  If the table has any foreign key constraints then the CASCADE CONSTRAINTS clause is required to drop the table.

Format:
DROP TABLE table-name [CASCADE CONSTRAINTS];
DROP INDEX index-name;

Example1: Delete the table STUDENT (no foreign key constraints)
DROP TABLE STUDENT;

Example2: Delete the table ENROLL (with foreign key constraints)
DROP TABLE ENROLL CASCADE CONSTRAINTS;

Example3: Remove the emp-name index on employee table
DROP INDEX emp-name;

When you drop a table you also delete all data currently in that table.  Be careful!