github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/collatedstring_constraint (about)

     1  # English collation chart: http://www.unicode.org/cldr/charts/30/collation/en_US_POSIX.html
     2  
     3  statement ok
     4  CREATE TABLE p (
     5    a STRING COLLATE en_u_ks_level1 PRIMARY KEY
     6  )
     7  
     8  statement ok
     9  INSERT INTO p VALUES ('a' COLLATE en_u_ks_level1)
    10  
    11  statement error duplicate key value \(a\)=\('a' COLLATE en_u_ks_level1\) violates unique constraint "primary"
    12  INSERT INTO p VALUES ('A' COLLATE en_u_ks_level1)
    13  
    14  statement ok
    15  INSERT INTO p VALUES ('b' COLLATE en_u_ks_level1)
    16  
    17  statement ok
    18  CREATE TABLE c1 (
    19    a STRING COLLATE en_u_ks_level1 PRIMARY KEY,
    20    b STRING COLLATE en_u_ks_level1
    21  ) INTERLEAVE IN PARENT p (a)
    22  
    23  statement ok
    24  INSERT INTO c1 VALUES ('A' COLLATE en_u_ks_level1, 'apple' COLLATE en_u_ks_level1)
    25  
    26  statement ok
    27  INSERT INTO c1 VALUES ('b' COLLATE en_u_ks_level1, 'banana' COLLATE en_u_ks_level1)
    28  
    29  statement ok
    30  INSERT INTO c1 VALUES ('p' COLLATE en_u_ks_level1, 'pear' COLLATE en_u_ks_level1)
    31  
    32  query T
    33  SELECT a FROM p ORDER BY a
    34  ----
    35  a
    36  b
    37  
    38  query T
    39  SELECT a FROM c1 ORDER BY a
    40  ----
    41  A
    42  b
    43  p
    44  
    45  query T
    46  SELECT b FROM c1 ORDER BY a
    47  ----
    48  apple
    49  banana
    50  pear
    51  
    52  statement ok
    53  CREATE TABLE c2 (
    54    a STRING COLLATE en_u_ks_level1 PRIMARY KEY,
    55    b STRING COLLATE en_u_ks_level1,
    56    CONSTRAINT fk_p FOREIGN KEY (a) REFERENCES p
    57  ) INTERLEAVE IN PARENT p (a)
    58  
    59  statement ok
    60  INSERT INTO c2 VALUES ('A' COLLATE en_u_ks_level1, 'apple' COLLATE en_u_ks_level1)
    61  
    62  statement ok
    63  INSERT INTO c2 VALUES ('b' COLLATE en_u_ks_level1, 'banana' COLLATE en_u_ks_level1)
    64  
    65  statement error foreign key violation: value \['p' COLLATE en_u_ks_level1\] not found in p@primary \[a\]|insert on table "c2" violates foreign key constraint "fk_p"
    66  INSERT INTO c2 VALUES ('p' COLLATE en_u_ks_level1, 'pear' COLLATE en_u_ks_level1)
    67  
    68  query T
    69  SELECT a FROM p ORDER BY a
    70  ----
    71  a
    72  b
    73  
    74  query T
    75  SELECT a FROM c2 ORDER BY a
    76  ----
    77  A
    78  b
    79  
    80  query T
    81  SELECT b FROM c2 ORDER BY a
    82  ----
    83  apple
    84  banana