Oracle 5.0, Reference Manual

Page: 1064 / 3232

CREATE TABLE
Syntax
1044
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol ;
If the
FOREIGN KEY
clause included a
CONSTRAINT
name when you created the foreign key, you can
refer to that name to drop the foreign key. Otherwise, the
fk_symbol
value is generated internally
when the foreign key is created. To find out the symbol value when you want to drop a foreign key, use
a
SHOW CREATE TABLE
statement, as shown here:
mysql> SHOW CREATE TABLE ibtest11c\G
*************************** 1. row ***************************
Table: ibtest11c
Create Table: CREATE TABLE `ibtest11c` (
`A` int(11) NOT NULL auto_increment,
`D` int(11) NOT NULL default '0',
`B` varchar(200) NOT NULL default '',
`C` varchar(175) default NULL,
PRIMARY KEY (`A`,`D`,`B`),
KEY `B` (`B`,`C`),
KEY `C` (`C`),
CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)
REFERENCES `ibtest11a` (`A`, `D`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)
REFERENCES `ibtest11a` (`B`, `C`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB CHARSET=latin1
1 row in set (0.01 sec)
mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY `0_38775`;
You cannot add a foreign key and drop a foreign key in the same
ALTER TABLE
statement. A separate
statements is required for each operation.
If an
ALTER TABLE
statement results in changes to column values (for example, because a column
is truncated), MySQL's foreign key constraint checks do not notice possible violations caused by
changing the values.
Foreign Keys and Other MySQL Statements
Table and column identifiers in a
FOREIGN KEY ... REFERENCES ...
clause can be quoted within
backticks (
`
). Alternatively, double quotation marks (
"
) can be used if the
ANSI_QUOTES
[535] SQL
mode is enabled. The setting of the
lower_case_table_names
[466] system variable is also taken
into account.
You can view a child table's foreign key definitions as part of the output of the
SHOW CREATE TABLE
statement:
SHOW CREATE TABLE tbl_name ;
You can also obtain information about foreign keys by querying the
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
table.
mysqldump
produces correct definitions of tables in the dump file, including the foreign keys for child
tables.
To make it easier to reload dump files for tables that have foreign key relationships,
mysqldump
automatically includes a statement in the dump output to set
foreign_key_checks
[451] to 0. This
avoids problems with tables having to be reloaded in a particular order when the dump is reloaded. It is
also possible to set this variable manually:
mysql> SET foreign_key_checks = 0;
mysql> SOURCE dump_file_name;
mysql> SET foreign_key_checks = 1;
This enables you to import the tables in any order if the dump file contains tables that are not correctly
ordered for foreign keys. It also speeds up the import operation. Setting
foreign_key_checks
[451]
to 0 can also be useful for ignoring foreign key constraints during
LOAD DATA
and
ALTER TABLE