ALTER TABLE FOREIGN
KEY
Adds a foreign
key to a table.
SYNTAX
| table_name |
Name
of the table you are adding the foreign key to. |
| key_name |
Name
of the new foreign key. |
| column_name |
1. Name
of the column the foreign key is created on.
2. Name of the column referenced by the foreign key. |
| parent_table_name |
Name
of the table the foreign key references. |
DESCRIPTION
The ALTER
TABLE FOREIGN KEY command modifies the definition of an existing table
and adds a new foreign key. To execute the ALTER TABLE DROP PRIMARY KEY
command on a table, you must have DBA security privilege, or be the owner
of the table (or have ALTER privilege on the table) and be the owner of
the table (or have REFERENCE privilege on the columns or table) containing
the primary key.
A key is
a column or combination of columns that help identify specific rows in
a table. The columns that make up a key are known as key columns. A unique
key is a key in which no two records have the same value for the key field.
A primary
key is a key that uniquely identifies each row in a table. Without a primary
key, it is impossible to distinguish between specific rows in a table
because rows may contain duplicate values. The DBMS will not allow you
to define a primary key on columns that contain duplicate values, and
will not allow you to enter a duplicate value in a primary key that already
exists.
A foreign
key is a key that corresponds to the primary key (or a unique index) of
another table. This establishes a parent-child relationship between two
tables that is represented by common data values stored in the tables.
The parent table contains the primary key or unique index, and the child
table contains the foreign key whose columns correspond to columns in
the parent table.
Referential
integrity ensures that every value in a child key (the foreign key of
the child table) has a corresponding value in the parent key (the primary
key or unique index of the parent table). Referential integrity is enforced
between tables using the parent-child relationship established with foreign
keys. DBMaker has automatic support for referential integrity constraints
between tables through the definition of foreign keys. When adding a record
to a child table, the value in the child key must also exist in the parent
key. Similarly, when deleting a record from the parent table, all records
in the child key with the same value must be deleted first.
Referential
actions provide a means to update or delete a parent key when referential
integrity would not normally allow it (such as when a parent key is referenced
by a child key). The referential actions define the operation DBMaker
should perform on all matching rows in the child key when you update or
delete a parent key. DBMaker supports four referential actions for both
updates and deletes: CASCADE, SET NULL, SET DEFAULT, and NO ACTION.
The UPDATE/DELETE
keywords are optional. These keywords specify the referential action DBMaker
should perform when you update or delete a value in a parent key that
is referenced by a child key. The referential actions you can specify
for these keywords are CASCADE, SET NULL, SET DEFAULT, and NO ACTION.
CASCADE
performs an update or delete on all matching values in the child key as
when you update or delete the parent key. This will set the value of the
child key to the same value as the parent key when you update a row in
the parent key, or will delete all matching values in the child key with
the same value as the parent key when you delete a row in the parent key.
SET NULL
sets all matching values in the child key to NULL when you update or delete
a row in the parent key. You cannot use the SET NULL action when the child
key was defined with the NOT NULL constraint.
SET DEFAULT
sets all matching values in the child key to the default value of the
column when you update or delete a row in the parent key. You cannot use
the SET DEFAULT action when the default value is NULL and the child key
was defined with the NOT NULL constraint.
NO ACTION
enforces normal referential integrity rules. If you do not specify a referential
action when you create a foreign key, DBMaker will use NO ACTION by default.
There is
no practical limit to the number of foreign keys you can have on a table.
The parent key may be the primary key or any other unique index of a table,
but you must create the parent key before adding the child key. The number
of columns and column type (or length) must be the same in the parent
key and the child key. The column order of corresponding keys may be different
in each table, provided they are listed in corresponding order in the
ALTER TABLE FOREIGN KEY command. (See example, below.) If no columns are
specified for the parent key, the primary key of the parent table is used
by default.
Columns
in a foreign key may contain null values. If a foreign key contains a
null value, it satisfies referential integrity automatically. You may
not create a foreign key on a view, but you may create one on a synonym.
Foreign
key names have a maximum length of eighteen characters, and may contain
numbers, letters, the underscore character, and the symbols $ and #. The
first character may not be a number.
EXAMPLES
The following
example creates a foreign key fkey1 on column CustNo of table Accounts
that references the Customers table. Since no column name is explicitly
specified for the parent key, DBMaker will use the primary key of the
Customers table as the parent key. The primary key of the Customers table
must be defined before executing this command.
ALTER TABLE Accounts FOREIGN KEY fkey1 (CustNo) REFERENCES Customers
|
The following
example creates the same foreign key fkey1 from the previous example,
but explicitly specifies the CustNo column as the parent key. The CustNo
column can be the primary key of the Accounts table or any other unique
index. The primary key or other unique index of the Customers table must
be defined before executing this command.
ALTER TABLE Accounts FOREIGN KEY fkey1 (CustNo)
REFERENCES Customers (CustNo)
|
The following
example creates a foreign key fkey2 on columns PartNo and StockNo of table
Invoice that references the Stock table. Column order in the Invoice table
(PartNo, SuppNo) is different from the corresponding columns in the Stock
table (SuppNo, PartNo). This is acceptable provided corresponding columns
from each table are listed in the same order in the command.
ALTER TABLE Invoice FOREIGN KEY fkey2 (SuppNo, PartNo)
REFERENCES Stock (SuppNo, PartNo)
|
RELATED COMMANDS
<
ALTER TABLE DROP PRIMARY KEY | Contents
| ALTER TABLE MODIFY COLUMN
>
|
