github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/testutils/testcat/testdata/foreign_keys (about)

     1  exec-ddl
     2  CREATE TABLE parent (p INT PRIMARY KEY)
     3  ----
     4  
     5  exec-ddl
     6  CREATE TABLE child (c INT PRIMARY KEY, p INT REFERENCES parent(p))
     7  ----
     8  
     9  exec-ddl
    10  SHOW CREATE parent
    11  ----
    12  TABLE parent
    13   ├── p int not null
    14   ├── INDEX primary
    15   │    └── p int not null
    16   └── REFERENCED BY CONSTRAINT fk_p_ref_parent FOREIGN KEY child (p) REFERENCES parent (p)
    17  
    18  exec-ddl
    19  SHOW CREATE child
    20  ----
    21  TABLE child
    22   ├── c int not null
    23   ├── p int
    24   ├── INDEX primary
    25   │    └── c int not null
    26   ├── INDEX child_auto_index_fk_p_ref_parent
    27   │    ├── p int
    28   │    └── c int not null
    29   └── CONSTRAINT fk_p_ref_parent FOREIGN KEY child (p) REFERENCES parent (p)
    30  
    31  exec-ddl
    32  CREATE TABLE parent2 (p INT UNIQUE)
    33  ----
    34  
    35  exec-ddl
    36  CREATE TABLE child2 (c INT PRIMARY KEY, p INT REFERENCES parent2(p))
    37  ----
    38  
    39  exec-ddl
    40  SHOW CREATE parent2
    41  ----
    42  TABLE parent2
    43   ├── p int
    44   ├── rowid int not null default (unique_rowid()) [hidden]
    45   ├── INDEX primary
    46   │    └── rowid int not null default (unique_rowid()) [hidden]
    47   ├── INDEX parent2_p_key
    48   │    ├── p int
    49   │    └── rowid int not null default (unique_rowid()) [hidden] (storing)
    50   └── REFERENCED BY CONSTRAINT fk_p_ref_parent2 FOREIGN KEY child2 (p) REFERENCES parent2 (p)
    51  
    52  exec-ddl
    53  SHOW CREATE child2
    54  ----
    55  TABLE child2
    56   ├── c int not null
    57   ├── p int
    58   ├── INDEX primary
    59   │    └── c int not null
    60   ├── INDEX child2_auto_index_fk_p_ref_parent2
    61   │    ├── p int
    62   │    └── c int not null
    63   └── CONSTRAINT fk_p_ref_parent2 FOREIGN KEY child2 (p) REFERENCES parent2 (p)
    64  
    65  exec-ddl
    66  CREATE TABLE parent_multicol (p INT, q INT, r INT, PRIMARY KEY (p,q,r))
    67  ----
    68  
    69  exec-ddl
    70  CREATE TABLE child_multicol (
    71    p INT,
    72    q INT,
    73    r INT,
    74    PRIMARY KEY (p,q,r),
    75    CONSTRAINT fk FOREIGN KEY (p,q,r) REFERENCES parent_multicol(p,q,r)
    76  )
    77  ----
    78  
    79  exec-ddl
    80  CREATE TABLE child_multicol_full (
    81    p INT,
    82    q INT,
    83    r INT,
    84    PRIMARY KEY (p,q,r),
    85    CONSTRAINT fk FOREIGN KEY (p,q,r) REFERENCES parent_multicol(p,q,r) MATCH FULL
    86  )
    87  ----
    88  
    89  exec-ddl
    90  SHOW CREATE parent_multicol
    91  ----
    92  TABLE parent_multicol
    93   ├── p int not null
    94   ├── q int not null
    95   ├── r int not null
    96   ├── INDEX primary
    97   │    ├── p int not null
    98   │    ├── q int not null
    99   │    └── r int not null
   100   ├── REFERENCED BY CONSTRAINT fk FOREIGN KEY child_multicol (p, q, r) REFERENCES parent_multicol (p, q, r)
   101   └── REFERENCED BY CONSTRAINT fk FOREIGN KEY child_multicol_full (p, q, r) REFERENCES parent_multicol (p, q, r) MATCH FULL
   102  
   103  exec-ddl
   104  SHOW CREATE child_multicol
   105  ----
   106  TABLE child_multicol
   107   ├── p int not null
   108   ├── q int not null
   109   ├── r int not null
   110   ├── INDEX primary
   111   │    ├── p int not null
   112   │    ├── q int not null
   113   │    └── r int not null
   114   └── CONSTRAINT fk FOREIGN KEY child_multicol (p, q, r) REFERENCES parent_multicol (p, q, r)
   115  
   116  exec-ddl
   117  SHOW CREATE child_multicol_full
   118  ----
   119  TABLE child_multicol_full
   120   ├── p int not null
   121   ├── q int not null
   122   ├── r int not null
   123   ├── INDEX primary
   124   │    ├── p int not null
   125   │    ├── q int not null
   126   │    └── r int not null
   127   └── CONSTRAINT fk FOREIGN KEY child_multicol_full (p, q, r) REFERENCES parent_multicol (p, q, r) MATCH FULL
   128  
   129  exec-ddl
   130  DROP TABLE child
   131  ----
   132  
   133  exec-ddl
   134  SHOW CREATE parent
   135  ----
   136  TABLE parent
   137   ├── p int not null
   138   └── INDEX primary
   139        └── p int not null
   140  
   141  exec-ddl
   142  DROP TABLE child_multicol
   143  ----
   144  
   145  exec-ddl
   146  SHOW CREATE parent_multicol
   147  ----
   148  TABLE parent_multicol
   149   ├── p int not null
   150   ├── q int not null
   151   ├── r int not null
   152   ├── INDEX primary
   153   │    ├── p int not null
   154   │    ├── q int not null
   155   │    └── r int not null
   156   └── REFERENCED BY CONSTRAINT fk FOREIGN KEY child_multicol_full (p, q, r) REFERENCES parent_multicol (p, q, r) MATCH FULL
   157  
   158  exec-ddl
   159  DROP TABLE child_multicol_full
   160  ----
   161  
   162  exec-ddl
   163  SHOW CREATE parent_multicol
   164  ----
   165  TABLE parent_multicol
   166   ├── p int not null
   167   ├── q int not null
   168   ├── r int not null
   169   └── INDEX primary
   170        ├── p int not null
   171        ├── q int not null
   172        └── r int not null
   173  
   174  # Verify we can drop a self-referencing table.
   175  exec-ddl
   176  CREATE TABLE self (a INT PRIMARY KEY, b INT REFERENCES self(a) ON DELETE CASCADE)
   177  ----
   178  
   179  exec-ddl
   180  DROP TABLE self
   181  ----