github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optbuilder/testdata/fk-checks-delete (about)

     1  exec-ddl
     2  CREATE TABLE parent (x INT, p INT PRIMARY KEY, other INT UNIQUE)
     3  ----
     4  
     5  exec-ddl
     6  CREATE TABLE child (c INT PRIMARY KEY, p INT NOT NULL REFERENCES parent(p))
     7  ----
     8  
     9  build
    10  DELETE FROM child WHERE c = 4
    11  ----
    12  delete child
    13   ├── columns: <none>
    14   ├── fetch columns: c:3 p:4
    15   └── select
    16        ├── columns: c:3!null p:4!null
    17        ├── scan child
    18        │    └── columns: c:3!null p:4!null
    19        └── filters
    20             └── c:3 = 4
    21  
    22  build
    23  DELETE FROM parent WHERE p = 3
    24  ----
    25  delete parent
    26   ├── columns: <none>
    27   ├── fetch columns: x:4 parent.p:5 other:6
    28   ├── input binding: &1
    29   ├── select
    30   │    ├── columns: x:4 parent.p:5!null other:6
    31   │    ├── scan parent
    32   │    │    └── columns: x:4 parent.p:5!null other:6
    33   │    └── filters
    34   │         └── parent.p:5 = 3
    35   └── f-k-checks
    36        └── f-k-checks-item: child(p) -> parent(p)
    37             └── semi-join (hash)
    38                  ├── columns: p:7!null
    39                  ├── with-scan &1
    40                  │    ├── columns: p:7!null
    41                  │    └── mapping:
    42                  │         └──  parent.p:5 => p:7
    43                  ├── scan child
    44                  │    └── columns: child.p:9!null
    45                  └── filters
    46                       └── p:7 = child.p:9
    47  
    48  exec-ddl
    49  CREATE TABLE child2 (c INT PRIMARY KEY, p INT NOT NULL REFERENCES parent(other))
    50  ----
    51  
    52  build
    53  DELETE FROM parent WHERE p = 3
    54  ----
    55  delete parent
    56   ├── columns: <none>
    57   ├── fetch columns: x:4 parent.p:5 parent.other:6
    58   ├── input binding: &1
    59   ├── select
    60   │    ├── columns: x:4 parent.p:5!null parent.other:6
    61   │    ├── scan parent
    62   │    │    └── columns: x:4 parent.p:5!null parent.other:6
    63   │    └── filters
    64   │         └── parent.p:5 = 3
    65   └── f-k-checks
    66        ├── f-k-checks-item: child(p) -> parent(p)
    67        │    └── semi-join (hash)
    68        │         ├── columns: p:7!null
    69        │         ├── with-scan &1
    70        │         │    ├── columns: p:7!null
    71        │         │    └── mapping:
    72        │         │         └──  parent.p:5 => p:7
    73        │         ├── scan child
    74        │         │    └── columns: child.p:9!null
    75        │         └── filters
    76        │              └── p:7 = child.p:9
    77        └── f-k-checks-item: child2(p) -> parent(other)
    78             └── semi-join (hash)
    79                  ├── columns: other:10
    80                  ├── with-scan &1
    81                  │    ├── columns: other:10
    82                  │    └── mapping:
    83                  │         └──  parent.other:6 => other:10
    84                  ├── scan child2
    85                  │    └── columns: child2.p:12!null
    86                  └── filters
    87                       └── other:10 = child2.p:12
    88  
    89  exec-ddl
    90  CREATE TABLE doubleparent (p1 INT, p2 INT, other INT, PRIMARY KEY (p1, p2))
    91  ----
    92  
    93  exec-ddl
    94  CREATE TABLE doublechild (c INT PRIMARY KEY, p1 INT, p2 INT, FOREIGN KEY (p1, p2) REFERENCES doubleparent (p1, p2))
    95  ----
    96  
    97  build
    98  DELETE FROM doubleparent WHERE p1 = 10
    99  ----
   100  delete doubleparent
   101   ├── columns: <none>
   102   ├── fetch columns: doubleparent.p1:4 doubleparent.p2:5 other:6
   103   ├── input binding: &1
   104   ├── select
   105   │    ├── columns: doubleparent.p1:4!null doubleparent.p2:5!null other:6
   106   │    ├── scan doubleparent
   107   │    │    └── columns: doubleparent.p1:4!null doubleparent.p2:5!null other:6
   108   │    └── filters
   109   │         └── doubleparent.p1:4 = 10
   110   └── f-k-checks
   111        └── f-k-checks-item: doublechild(p1,p2) -> doubleparent(p1,p2)
   112             └── semi-join (hash)
   113                  ├── columns: p1:7!null p2:8!null
   114                  ├── with-scan &1
   115                  │    ├── columns: p1:7!null p2:8!null
   116                  │    └── mapping:
   117                  │         ├──  doubleparent.p1:4 => p1:7
   118                  │         └──  doubleparent.p2:5 => p2:8
   119                  ├── scan doublechild
   120                  │    └── columns: doublechild.p1:10 doublechild.p2:11
   121                  └── filters
   122                       ├── p1:7 = doublechild.p1:10
   123                       └── p2:8 = doublechild.p2:11
   124  
   125  build
   126  DELETE FROM doublechild WHERE p1 = 10
   127  ----
   128  delete doublechild
   129   ├── columns: <none>
   130   ├── fetch columns: c:4 p1:5 p2:6
   131   └── select
   132        ├── columns: c:4!null p1:5!null p2:6
   133        ├── scan doublechild
   134        │    └── columns: c:4!null p1:5 p2:6
   135        └── filters
   136             └── p1:5 = 10