github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/testutils/opttester/testfixtures/tpch_schema (about)

     1  exec-ddl
     2  CREATE TABLE public.region
     3  (
     4      r_regionkey int PRIMARY KEY,
     5      r_name char(25) NOT NULL,
     6      r_comment varchar(152)
     7  );
     8  ----
     9  
    10  exec-ddl
    11  CREATE TABLE public.nation
    12  (
    13      n_nationkey int PRIMARY KEY,
    14      n_name char(25) NOT NULL,
    15      n_regionkey int NOT NULL,
    16      n_comment varchar(152),
    17      INDEX n_rk (n_regionkey ASC),
    18      CONSTRAINT nation_fkey_region FOREIGN KEY (n_regionkey) references public.region (r_regionkey)
    19  );
    20  ----
    21  
    22  exec-ddl
    23  CREATE TABLE public.supplier
    24  (
    25      s_suppkey int PRIMARY KEY,
    26      s_name char(25) NOT NULL,
    27      s_address varchar(40) NOT NULL,
    28      s_nationkey int NOT NULL,
    29      s_phone char(15) NOT NULL,
    30      s_acctbal float NOT NULL,
    31      s_comment varchar(101) NOT NULL,
    32      INDEX s_nk (s_nationkey ASC),
    33      CONSTRAINT supplier_fkey_nation FOREIGN KEY (s_nationkey) references public.nation (n_nationkey)
    34  );
    35  ----
    36  
    37  exec-ddl
    38  CREATE TABLE public.part
    39  (
    40      p_partkey int PRIMARY KEY,
    41      p_name varchar(55) NOT NULL,
    42      p_mfgr char(25) NOT NULL,
    43      p_brand char(10) NOT NULL,
    44      p_type varchar(25) NOT NULL,
    45      p_size int NOT NULL,
    46      p_container char(10) NOT NULL,
    47      p_retailprice float NOT NULL,
    48      p_comment varchar(23) NOT NULL
    49  );
    50  ----
    51  
    52  exec-ddl
    53  CREATE TABLE public.partsupp
    54  (
    55      ps_partkey int NOT NULL,
    56      ps_suppkey int NOT NULL,
    57      ps_availqty int NOT NULL,
    58      ps_supplycost float NOT NULL,
    59      ps_comment varchar(199) NOT NULL,
    60      PRIMARY KEY (ps_partkey, ps_suppkey),
    61      INDEX ps_sk (ps_suppkey ASC),
    62      CONSTRAINT partsupp_fkey_part FOREIGN KEY (ps_partkey) references public.part (p_partkey),
    63      CONSTRAINT partsupp_fkey_supplier FOREIGN KEY (ps_suppkey) references public.supplier (s_suppkey)
    64  );
    65  ----
    66  
    67  exec-ddl
    68  CREATE TABLE public.customer
    69  (
    70      c_custkey int PRIMARY KEY,
    71      c_name varchar(25) NOT NULL,
    72      c_address varchar(40) NOT NULL,
    73      c_nationkey int NOT NULL NOT NULL,
    74      c_phone char(15) NOT NULL,
    75      c_acctbal float NOT NULL,
    76      c_mktsegment char(10) NOT NULL,
    77      c_comment varchar(117) NOT NULL,
    78      INDEX c_nk (c_nationkey ASC),
    79      CONSTRAINT customer_fkey_nation FOREIGN KEY (c_nationkey) references public.nation (n_nationkey)
    80  );
    81  ----
    82  
    83  exec-ddl
    84  CREATE TABLE public.orders
    85  (
    86      o_orderkey int PRIMARY KEY,
    87      o_custkey int NOT NULL,
    88      o_orderstatus char(1) NOT NULL,
    89      o_totalprice float NOT NULL,
    90      o_orderdate date NOT NULL,
    91      o_orderpriority char(15) NOT NULL,
    92      o_clerk char(15) NOT NULL,
    93      o_shippriority int NOT NULL,
    94      o_comment varchar(79) NOT NULL,
    95      INDEX o_ck (o_custkey ASC),
    96      INDEX o_od (o_orderdate ASC),
    97      CONSTRAINT orders_fkey_customer FOREIGN KEY (o_custkey) references public.customer (c_custkey)
    98  );
    99  ----
   100  
   101  exec-ddl
   102  CREATE TABLE public.lineitem
   103  (
   104      l_orderkey int NOT NULL,
   105      l_partkey int NOT NULL,
   106      l_suppkey int NOT NULL,
   107      l_linenumber int NOT NULL,
   108      l_quantity float NOT NULL,
   109      l_extendedprice float NOT NULL,
   110      l_discount float NOT NULL,
   111      l_tax float NOT NULL,
   112      l_returnflag char(1) NOT NULL,
   113      l_linestatus char(1) NOT NULL,
   114      l_shipdate date NOT NULL,
   115      l_commitdate date NOT NULL,
   116      l_receiptdate date NOT NULL,
   117      l_shipinstruct char(25) NOT NULL,
   118      l_shipmode char(10) NOT NULL,
   119      l_comment varchar(44) NOT NULL,
   120      PRIMARY KEY (l_orderkey, l_linenumber),
   121      INDEX l_ok (l_orderkey ASC),
   122      INDEX l_pk (l_partkey ASC),
   123      INDEX l_sk (l_suppkey ASC),
   124      INDEX l_sd (l_shipdate ASC),
   125      INDEX l_cd (l_commitdate ASC),
   126      INDEX l_rd (l_receiptdate ASC),
   127      INDEX l_pk_sk (l_partkey ASC, l_suppkey ASC),
   128      INDEX l_sk_pk (l_suppkey ASC, l_partkey ASC),
   129      CONSTRAINT lineitem_fkey_orders FOREIGN KEY (l_orderkey) references public.orders (o_orderkey),
   130      CONSTRAINT lineitem_fkey_part FOREIGN KEY (l_partkey) references public.part (p_partkey),
   131      CONSTRAINT lineitem_fkey_supplier FOREIGN KEY (l_suppkey) references public.supplier (s_suppkey),
   132      CONSTRAINT lineitem_fkey_partsupp FOREIGN KEY (l_partkey, l_suppkey) references public.partsupp (ps_partkey, ps_suppkey)
   133  );
   134  ----