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

     1  # Test dumping a database with foreign keys does so in correct order.
     2  
     3  sql
     4  CREATE DATABASE d1;
     5  CREATE DATABASE d2;
     6  USE d1;
     7  
     8  -- B -> A
     9  CREATE TABLE b (i int PRIMARY KEY);
    10  CREATE TABLE a (i int REFERENCES b);
    11  INSERT INTO b VALUES (1);
    12  INSERT INTO a VALUES (1);
    13  
    14  -- Test multiple tables to make sure transitive deps are sorted correctly.
    15  -- E -> D -> C
    16  -- G -> F -> D -> C
    17  CREATE TABLE g (i int PRIMARY KEY);
    18  CREATE TABLE f (i int PRIMARY KEY, g int REFERENCES g);
    19  CREATE TABLE e (i int PRIMARY KEY);
    20  CREATE TABLE d (i int PRIMARY KEY, e int REFERENCES e, f int REFERENCES f);
    21  CREATE TABLE c (i int REFERENCES d);
    22  INSERT INTO g VALUES (1);
    23  INSERT INTO f VALUES (1, 1);
    24  INSERT INTO e VALUES (1);
    25  INSERT INTO d VALUES (1, 1, 1);
    26  INSERT INTO c VALUES (1);
    27  
    28  -- Test a table that uses a sequence to make sure the sequence is dumped first.
    29  CREATE SEQUENCE s;
    30  CREATE TABLE s_tbl (id INT PRIMARY KEY DEFAULT nextval('s'), v INT);
    31  INSERT INTO s_tbl (v) VALUES (10), (11);
    32  ----
    33  INSERT 2
    34  
    35  dump d1
    36  ----
    37  ----
    38  CREATE TABLE b (
    39  	i INT8 NOT NULL,
    40  	CONSTRAINT "primary" PRIMARY KEY (i ASC),
    41  	FAMILY "primary" (i)
    42  );
    43  
    44  CREATE TABLE a (
    45  	i INT8 NULL,
    46  	INDEX a_auto_index_fk_i_ref_b (i ASC),
    47  	FAMILY "primary" (i, rowid)
    48  );
    49  
    50  CREATE TABLE e (
    51  	i INT8 NOT NULL,
    52  	CONSTRAINT "primary" PRIMARY KEY (i ASC),
    53  	FAMILY "primary" (i)
    54  );
    55  
    56  CREATE TABLE g (
    57  	i INT8 NOT NULL,
    58  	CONSTRAINT "primary" PRIMARY KEY (i ASC),
    59  	FAMILY "primary" (i)
    60  );
    61  
    62  CREATE TABLE f (
    63  	i INT8 NOT NULL,
    64  	g INT8 NULL,
    65  	CONSTRAINT "primary" PRIMARY KEY (i ASC),
    66  	INDEX f_auto_index_fk_g_ref_g (g ASC),
    67  	FAMILY "primary" (i, g)
    68  );
    69  
    70  CREATE TABLE d (
    71  	i INT8 NOT NULL,
    72  	e INT8 NULL,
    73  	f INT8 NULL,
    74  	CONSTRAINT "primary" PRIMARY KEY (i ASC),
    75  	INDEX d_auto_index_fk_e_ref_e (e ASC),
    76  	INDEX d_auto_index_fk_f_ref_f (f ASC),
    77  	FAMILY "primary" (i, e, f)
    78  );
    79  
    80  CREATE TABLE c (
    81  	i INT8 NULL,
    82  	INDEX c_auto_index_fk_i_ref_d (i ASC),
    83  	FAMILY "primary" (i, rowid)
    84  );
    85  
    86  CREATE SEQUENCE s MINVALUE 1 MAXVALUE 9223372036854775807 INCREMENT 1 START 1;
    87  
    88  CREATE TABLE s_tbl (
    89  	id INT8 NOT NULL DEFAULT nextval('s':::STRING),
    90  	v INT8 NULL,
    91  	CONSTRAINT "primary" PRIMARY KEY (id ASC),
    92  	FAMILY "primary" (id, v)
    93  );
    94  
    95  INSERT INTO b (i) VALUES
    96  	(1);
    97  
    98  INSERT INTO a (i) VALUES
    99  	(1);
   100  
   101  INSERT INTO e (i) VALUES
   102  	(1);
   103  
   104  INSERT INTO g (i) VALUES
   105  	(1);
   106  
   107  INSERT INTO f (i, g) VALUES
   108  	(1, 1);
   109  
   110  INSERT INTO d (i, e, f) VALUES
   111  	(1, 1, 1);
   112  
   113  INSERT INTO c (i) VALUES
   114  	(1);
   115  
   116  SELECT setval('s', 3, false);
   117  
   118  INSERT INTO s_tbl (id, v) VALUES
   119  	(1, 10),
   120  	(2, 11);
   121  
   122  ALTER TABLE a ADD CONSTRAINT fk_i_ref_b FOREIGN KEY (i) REFERENCES b(i);
   123  ALTER TABLE f ADD CONSTRAINT fk_g_ref_g FOREIGN KEY (g) REFERENCES g(i);
   124  ALTER TABLE d ADD CONSTRAINT fk_e_ref_e FOREIGN KEY (e) REFERENCES e(i);
   125  ALTER TABLE d ADD CONSTRAINT fk_f_ref_f FOREIGN KEY (f) REFERENCES f(i);
   126  ALTER TABLE c ADD CONSTRAINT fk_i_ref_d FOREIGN KEY (i) REFERENCES d(i);
   127  
   128  -- Validate foreign key constraints. These can fail if there was unvalidated data during the dump.
   129  ALTER TABLE a VALIDATE CONSTRAINT fk_i_ref_b;
   130  ALTER TABLE f VALIDATE CONSTRAINT fk_g_ref_g;
   131  ALTER TABLE d VALIDATE CONSTRAINT fk_e_ref_e;
   132  ALTER TABLE d VALIDATE CONSTRAINT fk_f_ref_f;
   133  ALTER TABLE c VALIDATE CONSTRAINT fk_i_ref_d;
   134  ----
   135  ----
   136  
   137  # Ensure dump specifying only some tables works if those tables
   138  # reference tables not in the dump. Roundtrip is disabled because table
   139  # f is not present in the dump.
   140  dump d1 d e
   141  noroundtrip
   142  ----
   143  ----
   144  CREATE TABLE e (
   145  	i INT8 NOT NULL,
   146  	CONSTRAINT "primary" PRIMARY KEY (i ASC),
   147  	FAMILY "primary" (i)
   148  );
   149  
   150  CREATE TABLE d (
   151  	i INT8 NOT NULL,
   152  	e INT8 NULL,
   153  	f INT8 NULL,
   154  	CONSTRAINT "primary" PRIMARY KEY (i ASC),
   155  	INDEX d_auto_index_fk_e_ref_e (e ASC),
   156  	INDEX d_auto_index_fk_f_ref_f (f ASC),
   157  	FAMILY "primary" (i, e, f)
   158  );
   159  
   160  INSERT INTO e (i) VALUES
   161  	(1);
   162  
   163  INSERT INTO d (i, e, f) VALUES
   164  	(1, 1, 1);
   165  
   166  ALTER TABLE d ADD CONSTRAINT fk_e_ref_e FOREIGN KEY (e) REFERENCES e(i);
   167  ALTER TABLE d ADD CONSTRAINT fk_f_ref_f FOREIGN KEY (f) REFERENCES f(i);
   168  
   169  -- Validate foreign key constraints. These can fail if there was unvalidated data during the dump.
   170  ALTER TABLE d VALIDATE CONSTRAINT fk_e_ref_e;
   171  ALTER TABLE d VALIDATE CONSTRAINT fk_f_ref_f;
   172  ----
   173  ----