Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5, 10.6, 10.3(EOL), 10.4(EOL), 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
Description
It is possible to use one ALTER TABLE to drop and create a new column or index. This is not possible with a constraint; there will an error. See below
CREATE TABLE abc (id INT NOT NULL, PRIMARY KEY (id));
|
|
CREATE TABLE def (
|
abc_id INT NOT NULL,
|
droppable_column INT NOT NULL,
|
INDEX droppable_index(abc_id),
|
CONSTRAINT constraint_name FOREIGN KEY (abc_id) REFERENCES abc (id)
|
);
|
|
# No problem
|
ALTER TABLE def
|
DROP COLUMN droppable_column ,
|
ADD COLUMN droppable_column INT NOT NULL;
|
|
# No problem
|
ALTER TABLE def
|
DROP INDEX droppable_index,
|
ADD INDEX droppable_index(abc_id);
|
|
# Problem
|
ALTER TABLE def
|
DROP FOREIGN KEY constraint_name,
|
ADD CONSTRAINT constraint_name FOREIGN KEY (abc_id) REFERENCES abc (id);
|
Attachments
Issue Links
- relates to
-
MDEV-28933 CREATE OR REPLACE fails to recreate same constraint name
- Closed
-
MDEV-16356 Allow ALGORITHM=NOCOPY for ADD CONSTRAINT
- Open
-
MDEV-16417 Store Foreign Key metadata outside of InnoDB
- In Review