Hi for deleting a column which is referenced in both tables we can do it by the following.
CASCADE: Delete or update the row from
the parent table, and automatically delete or update the
matching rows in the child table. Both
ON DELETE
CASCADE and
ON UPDATE CASCADE
are supported. Between two tables, do not define several
ON UPDATE CASCADE clauses that act on the
same column in the parent table or in the child table.
[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION
we can use ondelete cascade orOndelete Noaction.
see some examples:-
ALTER TABLE <TABLE identifier>
ADD [ CONSTRAINT <CONSTRAINT identifier> ]
FOREIGN KEY ( <COLUMN expression> {, <COLUMN expression>}... )
REFERENCES <TABLE identifier> [ ( <COLUMN expression> {, <COLUMN expression>}... ) ]
[ ON UPDATE <referential action> ]
[ ON DELETE <referential action> ]
Likewise, foreign keys can be defined as part of the CREATE TABLE SQL statement.
CREATE TABLE TABLE_NAME (
id INTEGER PRIMARY KEY,
col2 CHARACTER VARYING(20),
col3 INTEGER,
...
FOREIGN KEY(col3)
REFERENCES other_table(key_col) ON DELETE CASCADE,
... )
I hope it helps you.