[MDEV-28298] Add Foreign key to a table instantly without checking existing data Created: 2022-04-12  Updated: 2023-05-19

Status: Open
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: Task Priority: Major
Reporter: Susmeet Khaire Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: None

Issue Links:
Relates
relates to MDEV-16356 Allow ALGORITHM=NOCOPY for ADD CONSTR... Open
relates to MDEV-22164 WITHOUT VALIDATION for EXCHANGE PARTI... Closed

 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.

 Comments   
Comment by Michael Widenius [ 2022-09-20 ]

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.

Comment by Nikita Malyavin [ 2023-04-17 ]

What about SET FOREIGN_KEY_CHECKS=0? AFAIK adding FK under this uses NOCOPY algorithm, not sure which one though

Comment by Marko Mäkelä [ 2023-05-19 ]

I believe that SET foreign_key_checks=0 should allow FOREIGN KEY constraints to be added ever since MySQL 5.6 or MariaDB Server 10.0.

Currently, if checks are enabled, adding a constraint will require the table to be copied in the most inefficient way (ALGORITHM=COPY). Once MDEV-16356 is implemented, it should be possible to add a constraint without the server writing much, just reading the affected tables in order to validate the constraint.

Generated at Thu Feb 08 09:59:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.