[MDEV-22361] Cross-engine foreign keys support Created: 2020-04-24  Updated: 2024-01-18

Status: Open
Project: MariaDB Server
Component/s: Server, Storage Engine - InnoDB
Fix Version/s: None

Type: New Feature Priority: Major
Reporter: Aleksey Midenkov Assignee: Aleksey Midenkov
Resolution: Unresolved Votes: 8
Labels: foreign-keys, snc

Issue Links:
Blocks
blocks MDEV-17440 ON DELETE CASCADE does not always del... Confirmed
blocks MDEV-22880 Honor constraints on UPDATE CASCADE Open
is blocked by MDEV-16417 Store Foreign Key metadata outside of... In Review
PartOf
includes MDEV-10393 Foreign keys SET DEFAULT action Open
includes MDEV-19191 FK support for CURRENT partition of v... Stalled
Relates
relates to MDEV-17598 InnoDB index option for per-record tr... Open
relates to MDEV-20777 Foreign keys without indexes Open
relates to MDEV-26554 Table-rebuilding DDL on parent table ... Closed
relates to MDEV-31942 Online alter: support cascade foreign... Open
relates to MDEV-12483 Add foreign keys support for partitio... Stalled
relates to MDEV-13538 better error reporting when FOREIGN K... Closed
relates to MDEV-22363 Reimplement the InnoDB virtual column... Open
relates to MDEV-23716 FK validate data but in select join t... Closed
relates to MDEV-26097 Constraints: support deferred constra... Open
relates to MDEV-26675 Multiple Foreign Keys to same column ... Stalled

 Description   

Implement cross-engine foreign keys check at SQL layer.

TODO:

  • Referenced/foreign key lookup procedure
  • Recursive table open (new FK structure for TABLE optimized for DML, FKs in TABLE point to parent TABLEs)
  • Recursive prelocking: write-locking for CASCADE actions
  • DML changes:
    UPDATE, Multi-UPDATE
    DELETE, Multi-DELETE
    REPLACE
    INSERT ODKU
    IGNORE clause
    Ignore ‘DELAYED’
  • DDL changes:
    DROP TABLE (DATABASE)

 Comments   
Comment by Marko Mäkelä [ 2020-08-07 ]

Implementing FOREIGN KEY processing in the SQL layer should also trivially fix MySQL Bug#11472 Triggers not executed following foreign key updates/deletes.
The cause of the bug is that the InnoDB storage engine, which currently is responsible for enforcing FOREIGN KEY relationships, is executing ON DELETE or ON UPDATE clauses internally, without invoking any code outside the storage engine to execute any triggers.

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