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

     1  # Test the interleaving of indexes within tables
     2  
     3  sql
     4  CREATE DATABASE d;
     5  CREATE TABLE d.t1 (a INT, b INT, PRIMARY KEY (a));
     6  CREATE INDEX b_idx ON d.t1(a, b) INTERLEAVE IN PARENT d.t1 (a);
     7  ----
     8  CREATE INDEX
     9  
    10  dump d t1
    11  ----
    12  ----
    13  CREATE TABLE t1 (
    14  	a INT8 NOT NULL,
    15  	b INT8 NULL,
    16  	CONSTRAINT "primary" PRIMARY KEY (a ASC),
    17  	FAMILY "primary" (a, b)
    18  );
    19  
    20  CREATE INDEX b_idx ON t1 (a ASC, b ASC) INTERLEAVE IN PARENT t1 (a);
    21  
    22  -- Validate foreign key constraints. These can fail if there was unvalidated data during the dump.
    23  ----
    24  ----
    25  
    26  sql
    27  CREATE DATABASE e;
    28  CREATE TABLE e.t2 (a INT, b INT, PRIMARY KEY (a));
    29  CREATE TABLE e.t3 (a INT, b INT, PRIMARY KEY (a));
    30  CREATE INDEX b_idx ON e.t2(a, b) INTERLEAVE IN PARENT e.t3 (a);
    31  INSERT INTO e.t2 VALUES (1, 2);
    32  INSERT INTO e.t3 VALUES (3, 4);
    33  ----
    34  INSERT 1
    35  
    36  dump e
    37  ----
    38  ----
    39  CREATE TABLE t3 (
    40  	a INT8 NOT NULL,
    41  	b INT8 NULL,
    42  	CONSTRAINT "primary" PRIMARY KEY (a ASC),
    43  	FAMILY "primary" (a, b)
    44  );
    45  
    46  CREATE TABLE t2 (
    47  	a INT8 NOT NULL,
    48  	b INT8 NULL,
    49  	CONSTRAINT "primary" PRIMARY KEY (a ASC),
    50  	FAMILY "primary" (a, b)
    51  );
    52  
    53  INSERT INTO t3 (a, b) VALUES
    54  	(3, 4);
    55  
    56  INSERT INTO t2 (a, b) VALUES
    57  	(1, 2);
    58  
    59  CREATE INDEX b_idx ON t2 (a ASC, b ASC) INTERLEAVE IN PARENT t3 (a);
    60  
    61  -- Validate foreign key constraints. These can fail if there was unvalidated data during the dump.
    62  ----
    63  ----
    64  
    65  sql
    66  CREATE DATABASE dd;
    67  CREATE TABLE dd.unique (a INT, b INT, PRIMARY KEY (a));
    68  CREATE INDEX "b_idx" ON dd.unique(a, b) INTERLEAVE IN PARENT dd.unique (a);
    69  ----
    70  CREATE INDEX
    71  
    72  dump dd unique
    73  ----
    74  ----
    75  CREATE TABLE "unique" (
    76  	a INT8 NOT NULL,
    77  	b INT8 NULL,
    78  	CONSTRAINT "primary" PRIMARY KEY (a ASC),
    79  	FAMILY "primary" (a, b)
    80  );
    81  
    82  CREATE INDEX b_idx ON "unique" (a ASC, b ASC) INTERLEAVE IN PARENT "unique" (a);
    83  
    84  -- Validate foreign key constraints. These can fail if there was unvalidated data during the dump.
    85  ----
    86  ----
    87  
    88  sql
    89  CREATE DATABASE ee;
    90  CREATE TABLE ee.a (i INT, j INT, PRIMARY KEY (i, j DESC));
    91  CREATE TABLE ee.d (x INT, y INT, z INT, PRIMARY KEY (x, y DESC, z DESC)) INTERLEAVE IN PARENT ee.a (x, y);
    92  ----
    93  CREATE TABLE
    94  
    95  dump ee a d
    96  ----
    97  ----
    98  CREATE TABLE a (
    99  	i INT8 NOT NULL,
   100  	j INT8 NOT NULL,
   101  	CONSTRAINT "primary" PRIMARY KEY (i ASC, j DESC),
   102  	FAMILY "primary" (i, j)
   103  );
   104  
   105  CREATE TABLE d (
   106  	x INT8 NOT NULL,
   107  	y INT8 NOT NULL,
   108  	z INT8 NOT NULL,
   109  	CONSTRAINT "primary" PRIMARY KEY (x ASC, y DESC, z DESC),
   110  	FAMILY "primary" (x, y, z)
   111  ) INTERLEAVE IN PARENT a (x, y);
   112  ----
   113  ----