mirror of
https://github.com/MariaDB/server.git
synced 2025-02-09 23:24:11 +01:00
cc810e64d4
Don't allow the referencing key column from NULL TO NOT NULL when 1) Foreign key constraint type is ON UPDATE SET NULL 2) Foreign key constraint type is ON DELETE SET NULL 3) Foreign key constraint type is UPDATE CASCADE and referenced column declared as NULL Don't allow the referenced key column from NOT NULL to NULL when foreign key constraint type is UPDATE CASCADE and referencing key columns doesn't allow NULL values get_foreign_key_info(): InnoDB sends the information about nullability of the foreign key fields and referenced key fields. fk_check_column_changes(): Enforce the above rules for COPY algorithm innobase_check_foreign_drop_col(): Checks whether the dropped column exists in existing foreign key relation innobase_check_foreign_low() : Enforce the above rules for INPLACE algorithm dict_foreign_t::check_fk_constraint_valid(): This is used by CREATE TABLE statement to check nullability for foreign key relation.
42 lines
758 B
Text
42 lines
758 B
Text
#
|
|
# Test UPDATE on multiple columns with multiple FKs
|
|
#
|
|
|
|
--source include/galera_cluster.inc
|
|
--source include/have_innodb.inc
|
|
|
|
CREATE TABLE t0 (
|
|
f1 INT PRIMARY KEY,
|
|
f2 INT UNIQUE NOT NULL
|
|
);
|
|
|
|
CREATE TABLE t1 (
|
|
f1 INT PRIMARY KEY,
|
|
FOREIGN KEY (f1)
|
|
REFERENCES t0(f1)
|
|
ON UPDATE CASCADE
|
|
);
|
|
|
|
CREATE TABLE t2 (
|
|
f2 INT PRIMARY KEY,
|
|
FOREIGN KEY (f2)
|
|
REFERENCES t0(f2)
|
|
ON UPDATE CASCADE
|
|
);
|
|
|
|
INSERT INTO t0 VALUES (0, 0);
|
|
INSERT INTO t1 VALUES (0);
|
|
INSERT INTO t2 VALUES (0);
|
|
|
|
--connection node_2
|
|
UPDATE t0 SET f1 = 1, f2 = 2;
|
|
|
|
--connection node_1
|
|
SELECT f1 = 1 FROM t1 WHERE f1 = 1;
|
|
SELECT f2 = 2 FROM t2 WHERE f2 = 2;
|
|
SELECT f1 = 1 FROM t1;
|
|
SELECT f2 = 2 FROM t2;
|
|
|
|
DROP TABLE t2;
|
|
DROP TABLE t1;
|
|
DROP TABLE t0;
|