github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/cli/testdata/dump/reference_cycle (about)

     1  # Test that a cycle between two tables is handled correctly.
     2  
     3  sql
     4  CREATE DATABASE d;
     5  USE d;
     6  CREATE TABLE loop_a (
     7    id INT PRIMARY KEY
     8   ,b_id INT
     9   ,INDEX(b_id)
    10  );
    11  
    12  CREATE TABLE loop_b (
    13    id INT PRIMARY KEY
    14   ,a_id INT REFERENCES loop_a ON DELETE CASCADE
    15  );
    16  
    17  ALTER TABLE loop_a ADD CONSTRAINT b_id_delete_constraint
    18    FOREIGN KEY (b_id) REFERENCES loop_b (id) ON DELETE CASCADE;
    19  
    20  INSERT INTO loop_a (id, b_id) VALUES (1, NULL);
    21  INSERT INTO loop_b (id, a_id) VALUES (1, 1);
    22  INSERT INTO loop_a (id, b_id) VALUES (2, 1);
    23  INSERT INTO loop_b (id, a_id) VALUES (2, 2);
    24  INSERT INTO loop_a (id, b_id) VALUES (3, 2);
    25  INSERT INTO loop_b (id, a_id) VALUES (3, 3);
    26  UPDATE loop_a SET b_id = 3 WHERE id = 1;
    27  ----
    28  UPDATE 1
    29  
    30  dump d
    31  ----
    32  ----
    33  CREATE TABLE loop_b (
    34  	id INT8 NOT NULL,
    35  	a_id INT8 NULL,
    36  	CONSTRAINT "primary" PRIMARY KEY (id ASC),
    37  	INDEX loop_b_auto_index_fk_a_id_ref_loop_a (a_id ASC),
    38  	FAMILY "primary" (id, a_id)
    39  );
    40  
    41  CREATE TABLE loop_a (
    42  	id INT8 NOT NULL,
    43  	b_id INT8 NULL,
    44  	CONSTRAINT "primary" PRIMARY KEY (id ASC),
    45  	INDEX loop_a_b_id_idx (b_id ASC),
    46  	FAMILY "primary" (id, b_id)
    47  );
    48  
    49  INSERT INTO loop_b (id, a_id) VALUES
    50  	(1, 1),
    51  	(2, 2),
    52  	(3, 3);
    53  
    54  INSERT INTO loop_a (id, b_id) VALUES
    55  	(1, 3),
    56  	(2, 1),
    57  	(3, 2);
    58  
    59  ALTER TABLE loop_b ADD CONSTRAINT fk_a_id_ref_loop_a FOREIGN KEY (a_id) REFERENCES loop_a(id) ON DELETE CASCADE;
    60  ALTER TABLE loop_a ADD CONSTRAINT b_id_delete_constraint FOREIGN KEY (b_id) REFERENCES loop_b(id) ON DELETE CASCADE;
    61  
    62  -- Validate foreign key constraints. These can fail if there was unvalidated data during the dump.
    63  ALTER TABLE loop_b VALIDATE CONSTRAINT fk_a_id_ref_loop_a;
    64  ALTER TABLE loop_a VALIDATE CONSTRAINT b_id_delete_constraint;
    65  ----
    66  ----
    67  
    68  dump d --dump-mode=schema
    69  ----
    70  ----
    71  CREATE TABLE loop_b (
    72  	id INT8 NOT NULL,
    73  	a_id INT8 NULL,
    74  	CONSTRAINT "primary" PRIMARY KEY (id ASC),
    75  	INDEX loop_b_auto_index_fk_a_id_ref_loop_a (a_id ASC),
    76  	FAMILY "primary" (id, a_id)
    77  );
    78  
    79  CREATE TABLE loop_a (
    80  	id INT8 NOT NULL,
    81  	b_id INT8 NULL,
    82  	CONSTRAINT "primary" PRIMARY KEY (id ASC),
    83  	INDEX loop_a_b_id_idx (b_id ASC),
    84  	FAMILY "primary" (id, b_id)
    85  );
    86  
    87  ALTER TABLE loop_b ADD CONSTRAINT fk_a_id_ref_loop_a FOREIGN KEY (a_id) REFERENCES loop_a(id) ON DELETE CASCADE;
    88  ALTER TABLE loop_a ADD CONSTRAINT b_id_delete_constraint FOREIGN KEY (b_id) REFERENCES loop_b(id) ON DELETE CASCADE;
    89  
    90  -- Validate foreign key constraints. These can fail if there was unvalidated data during the dump.
    91  ALTER TABLE loop_b VALIDATE CONSTRAINT fk_a_id_ref_loop_a;
    92  ALTER TABLE loop_a VALIDATE CONSTRAINT b_id_delete_constraint;
    93  ----
    94  ----