ALTER TABLE DROP
FOREIGN KEY
Drops a
foreign key from a table.
SYNTAX
| table_name |
Name
of the table you are dropping the foreign key from. |
| key_name |
Name
of the foreign key you are dropping. |
DESCRIPTION
The ALTER
TABLE DROP FOREIGN KEY command modifies the definition of an existing
table and drops a foreign key that was previously defined. To execute
the ALTER TABLE OPTIONS command on a table, you must be the table owner,
have DBA security privilege, or have the ALTER privilege for that table.
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 child keys 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. CASCADE performs
the update or delete on matching child keys as well as the parent key,
SET NULL sets the value of matching child keys to NULL, SET DEFAULT sets
the value of matching child keys to the default value of the column, and
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.
You can
use this command to drop a foreign key on a table when it is no longer
necessary. After you drop a foreign key, DBMaker no longer enforces referential
integrity or performs referential actions on the child table. Without
the foreign key it is possible to enter values in the child table that
do not exist in the parent table, and to update or delete values in the
parent table, possibly causing inconsistency in your database. This command
should be used with caution.
EXAMPLE
The following
example drops foreign key fkey1 from the Salary table.
ALTER TABLE Salary DROP FOREIGN KEY fkey1
|
RELATED COMMANDS
<
ALTER TABLE DROP COLUMN | Contents
| ALTER TABLE DROP PRIMARY KEY >
|
