Eine Einführung in
SQL
DROP TABLE problems: Foreign key references.
SQLite
You may not drop a table if it is referenced by another table.
Execute tidy up
code
silently
DROP TABLE t_b; DROP TABLE t_a
Execute set up
code
CREATE TABLE t_a (i INTEGER NOT NULL PRIMARY KEY); CREATE TABLE t_b (j INTEGER, FOREIGN KEY(j) REFERENCES t_a(i));
DROP TABLE t_a
Specific to SQLite
none
DB2
You may not drop a table if it is referenced by another table.
Execute tidy up
code
silently
DROP TABLE t_b; DROP TABLE t_a
Execute set up
code
CREATE TABLE t_a (i INTEGER NOT NULL PRIMARY KEY); CREATE TABLE t_b (j INTEGER, FOREIGN KEY(j) REFERENCES t_a(i));
DROP TABLE t_a
Specific to DB2
none
MS Access
You may not drop a table if it is referenced by another table.
Execute tidy up
code
silently
DROP TABLE t_b; DROP TABLE t_a
Execute set up
code
CREATE TABLE t_a (i INTEGER NOT NULL PRIMARY KEY); CREATE TABLE t_b (j INTEGER, FOREIGN KEY(j) REFERENCES t_a(i));
DROP TABLE t_a
Specific to MS Access
none
PostgreSQL
You may not drop a table if it is referenced by another table.
Execute tidy up
code
silently
DROP TABLE t_b; DROP TABLE t_a
Execute set up
code
CREATE TABLE t_a (i INTEGER NOT NULL PRIMARY KEY); CREATE TABLE t_b (j INTEGER, FOREIGN KEY(j) REFERENCES t_a(i));
DROP TABLE t_a
Specific to PostgreSQL
The (implicit) trigger gets dropped automatically.
Oracle
You may not drop a table if it is referenced by another table.
Execute tidy up
code
silently
DROP TABLE t_b; DROP TABLE t_a
Execute set up
code
CREATE TABLE t_a (i INTEGER NOT NULL PRIMARY KEY); CREATE TABLE t_b (j INTEGER, FOREIGN KEY(j) REFERENCES t_a(i));
DROP TABLE t_a
Specific to Oracle
The CASCADE CONSTRAINTS clause can be used to remove the references.
DROP TABLE t_a CASCADE CONSTRAINTS
MS SQL Server
You may not drop a table if it is referenced by another table.
Execute tidy up
code
silently
DROP TABLE t_b; DROP TABLE t_a
Execute set up
code
CREATE TABLE t_a (i INTEGER NOT NULL PRIMARY KEY); CREATE TABLE t_b (j INTEGER, FOREIGN KEY(j) REFERENCES t_a(i));
DROP TABLE t_a
Specific to MS SQL Server
We can find out what the foreign keys are using
sp_help
table_name
MySQL
You may not drop a table if it is referenced by another table.
Execute tidy up
code
silently
DROP TABLE t_b; DROP TABLE t_a
Execute set up
code
CREATE TABLE t_a (i INTEGER NOT NULL PRIMARY KEY); CREATE TABLE t_b (j INTEGER, FOREIGN KEY(j) REFERENCES t_a(i));
DROP TABLE t_a
Specific to MySQL
Foreign keys are not implemented in MySQL so this problem does not arise.
Sybase
You may not drop a table if it is referenced by another table.
Execute tidy up
code
silently
DROP TABLE t_b; DROP TABLE t_a
Execute set up
code
CREATE TABLE t_a (i INTEGER NOT NULL PRIMARY KEY); CREATE TABLE t_b (j INTEGER, FOREIGN KEY(j) REFERENCES t_a(i));
DROP TABLE t_a
Specific to Sybase
none
Mimer SQL
You may not drop a table if it is referenced by another table.
Execute tidy up
code
silently
DROP TABLE t_b; DROP TABLE t_a
Execute set up
code
CREATE TABLE t_a (i INTEGER NOT NULL PRIMARY KEY); CREATE TABLE t_b (j INTEGER, FOREIGN KEY(j) REFERENCES t_a(i));
DROP TABLE t_a
Specific to Mimer SQL
none