Why do I lose foreign keys and views after a table is copied and recreated?
There’s a bug remaining in deltaddl, to do with renaming temporary tables in postgres (possibly with other DBMSs too, I haven’t tested this elsewhere yet). A foreign key typically has a dependency on the primary key of the referenced table. This means that you cannot drop the primary key of the referenced table without either first dropping all foreign keys that refer to it, or alternatively using CASCADE on the drop. The same is true of a view which references a table. As at deltaddl version 0.3.8, when renaming a table to be copied, I drop the primary key on the renamed table (so that I can create a primary key of the same name on the copied table). I have to cascade the drop, because of the above reason. This has 2 effects: • If the cascade drops a foreign key that was to be dropped anyway as part of the schema upgrade, the DBMS will show an error when the explicit drop is attempted • (worse) If the cascade drops a foreign key that was NOT to be dropped anyway, it won’t be recreated
Related Questions
- Managing your MySQL Database Systems » Foreign Keys/relationships: How can SQLyog handle import of DUMPs with tables using Foreign Keys with HTTP tunnel?
- In edit/insert mode, how can I see a list of possible values for a column, based on some foreign table?
- Which SQL statement defines a FOREIGN KEY constraint on the DEPT NO column of the EMP table?
