Databases Reference
In-Depth Information
CREATE TABLE PUBLISHERS
(PubID TEXT(10) CONSTRAINT PrimaryKeyName PRIMARY KEY,
PubName TEXT(100),
PubPhone TEXT(20));
Create the
Books
table scheme, and link to
Publishers
using PubID as foreign key:
CREATE TABLE BOOKS
(ISBN TEXT(13) CONSTRAINT PrimaryKeyName PRIMARY KEY,
TITLE TEXT(100),
PRICE MONEY,
PubID TEXT(10) CONSTRAINT Test FOREIGN KEY (PubID) REFERENCES
Publishers
(PubID) );
6.6.1.3 Notes
The CREATE TABLE statement does not provide a way to create an index with
nonunique values. This can be done using the CREATE INDEX statement,
however.
•
In specifying a foreign key, the CREATE TABLE statement does enable
referential integrity rules, but does not allow the option of enabling cascading
updates or deletes. (This is one place where Access SQL is weaker than SQL-92,
which has a FOREIGN KEY clause that allows the programmer to specify ON
UPDATE CASCADE and/or ON DELETE CASCADE.)
•
6.6.2 The ALTER TABLE Statement
The ALTER TABLE command is used to:
Add a new column to a table
•
Delete a column from a table
•
Add or delete single- or multiple-column index
•
The syntax for the ALTER TABLE command is:
ALTER TABLE
TableName
ADD COLUMN
ColName ColType
[(
size
)] [
Single-ColumnConstraint
] |
DROP COLUMN
ColName
|
ADD CONSTRAINT
Multi-ColumnConstraint
|
DROP CONSTRAINT
MultiColumnIndexName
;
As you can see, the Single- and Multi-Column Constraint clauses (as defined earlier) can
be used here to add or delete (DROP) an index.
6.6.2.1 Notes
New columns are added at the beginning of the table, immediately following any
primary key columns.
•
