Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
-
None
Description
We have received a request to add foreign keys to a table instantly but validating the existing data at a later stage.
According to the customer, this feature is supported by PostgreSQL (NOT VALID keyword).
https://dba.stackexchange.com/a/271585
Currently adding a foreign key locks the table and it is not supported by ALTER TABLE ... ALGORITHM=INSTANT.
Attachments
Issue Links
- relates to
-
MDEV-16356 Allow ALGORITHM=NOCOPY for ADD CONSTRAINT
-
- Open
-
-
MDEV-22164 WITHOUT VALIDATION for EXCHANGE PARTITION/CONVERT IN
-
- Closed
-
Note that in MariaDB 10.11 one can use ALTER TABLE ONLINE to add a foreign key without any downtime
It not instant, but at least it will not block any user of the table.
I personally don't like an 'instant' operation that may silent fail without the user knowing if the foreign key will be there or not.