src/test/regress/input/constraints.source

   1 --
   2 -- CONSTRAINTS
   3 -- Constraints can be specified with:
   4 --  - DEFAULT clause
   5 --  - CHECK clauses
   6 --  - PRIMARY KEY clauses
   7 --  - UNIQUE clauses
   8 --
   9
  10 --
  11 -- DEFAULT syntax
  12 --
  13
  14 CREATE TABLE DEFAULT_TBL (i int DEFAULT 100,
  15         x text DEFAULT 'vadim', f float8 DEFAULT 123.456);
  16
  17 INSERT INTO DEFAULT_TBL VALUES (1, 'thomas', 57.0613);
  18 INSERT INTO DEFAULT_TBL VALUES (1, 'bruce');
  19 INSERT INTO DEFAULT_TBL (i, f) VALUES (2, 987.654);
  20 INSERT INTO DEFAULT_TBL (x) VALUES ('marc');
  21 INSERT INTO DEFAULT_TBL VALUES (3, null, 1.0);
  22
  23 SELECT '' AS five, * FROM DEFAULT_TBL;
  24
  25 CREATE SEQUENCE DEFAULT_SEQ;
  26
  27 CREATE TABLE DEFAULTEXPR_TBL (i1 int DEFAULT 100 + (200-199) * 2,
  28         i2 int DEFAULT nextval('default_seq'));
  29
  30 INSERT INTO DEFAULTEXPR_TBL VALUES (-1, -2);
  31 INSERT INTO DEFAULTEXPR_TBL (i1) VALUES (-3);
  32 INSERT INTO DEFAULTEXPR_TBL (i2) VALUES (-4);
  33 INSERT INTO DEFAULTEXPR_TBL (i2) VALUES (NULL);
  34
  35 SELECT '' AS four, * FROM DEFAULTEXPR_TBL;
  36
  37 -- syntax errors
  38 --  test for extraneous comma
  39 CREATE TABLE error_tbl (i int DEFAULT (100, ));
  40 --  this will fail because gram.y uses b_expr not a_expr for defaults,
  41 --  to avoid a shift/reduce conflict that arises from NOT NULL being
  42 --  part of the column definition syntax:
  43 CREATE TABLE error_tbl (b1 bool DEFAULT 1 IN (1, 2));
  44 --  this should work, however:
  45 CREATE TABLE error_tbl (b1 bool DEFAULT (1 IN (1, 2)));
  46
  47 DROP TABLE error_tbl;
  48
  49 --
  50 -- CHECK syntax
  51 --
  52
  53 CREATE TABLE CHECK_TBL (x int,
  54         CONSTRAINT CHECK_CON CHECK (x > 3));
  55
  56 INSERT INTO CHECK_TBL VALUES (5);
  57 INSERT INTO CHECK_TBL VALUES (4);
  58 INSERT INTO CHECK_TBL VALUES (3);
  59 INSERT INTO CHECK_TBL VALUES (2);
  60 INSERT INTO CHECK_TBL VALUES (6);
  61 INSERT INTO CHECK_TBL VALUES (1);
  62
  63 SELECT '' AS three, * FROM CHECK_TBL;
  64
  65 CREATE SEQUENCE CHECK_SEQ;
  66
  67 CREATE TABLE CHECK2_TBL (x int, y text, z int,
  68         CONSTRAINT SEQUENCE_CON
  69         CHECK (x > 3 and y <> 'check failed' and z < 8));
  70
  71 INSERT INTO CHECK2_TBL VALUES (4, 'check ok', -2);
  72 INSERT INTO CHECK2_TBL VALUES (1, 'x check failed', -2);
  73 INSERT INTO CHECK2_TBL VALUES (5, 'z check failed', 10);
  74 INSERT INTO CHECK2_TBL VALUES (0, 'check failed', -2);
  75 INSERT INTO CHECK2_TBL VALUES (6, 'check failed', 11);
  76 INSERT INTO CHECK2_TBL VALUES (7, 'check ok', 7);
  77
  78 SELECT '' AS two, * from CHECK2_TBL;
  79
  80 --
  81 -- Check constraints on INSERT
  82 --
  83
  84 CREATE SEQUENCE INSERT_SEQ;
  85
  86 CREATE TABLE INSERT_TBL (x INT DEFAULT nextval('insert_seq'),
  87         y TEXT DEFAULT '-NULL-',
  88         z INT DEFAULT -1 * currval('insert_seq'),
  89         CONSTRAINT INSERT_CON CHECK (x >= 3 AND y <> 'check failed' AND x < 8),
  90         CHECK (x + z = 0));
  91
  92 INSERT INTO INSERT_TBL(x,z) VALUES (2, -2);
  93
  94 SELECT '' AS zero, * FROM INSERT_TBL;
  95
  96 SELECT 'one' AS one, nextval('insert_seq');
  97
  98 INSERT INTO INSERT_TBL(y) VALUES ('Y');
  99 INSERT INTO INSERT_TBL(y) VALUES ('Y');
 100 INSERT INTO INSERT_TBL(x,z) VALUES (1, -2);
 101 INSERT INTO INSERT_TBL(z,x) VALUES (-7,  7);
 102 INSERT INTO INSERT_TBL VALUES (5, 'check failed', -5);
 103 INSERT INTO INSERT_TBL VALUES (7, '!check failed', -7);
 104 INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-');
 105
 106 SELECT '' AS four, * FROM INSERT_TBL;
 107
 108 INSERT INTO INSERT_TBL(y,z) VALUES ('check failed', 4);
 109 INSERT INTO INSERT_TBL(x,y) VALUES (5, 'check failed');
 110 INSERT INTO INSERT_TBL(x,y) VALUES (5, '!check failed');
 111 INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-');
 112
 113 SELECT '' AS six, * FROM INSERT_TBL;
 114
 115 SELECT 'seven' AS one, nextval('insert_seq');
 116
 117 INSERT INTO INSERT_TBL(y) VALUES ('Y');
 118
 119 SELECT 'eight' AS one, currval('insert_seq');
 120
 121 -- According to SQL92, it is OK to insert a record that gives rise to NULL
 122 -- constraint-condition results.  Postgres used to reject this, but it
 123 -- was wrong:
 124 INSERT INTO INSERT_TBL VALUES (null, null, null);
 125
 126 SELECT '' AS nine, * FROM INSERT_TBL;
 127
 128 --
 129 -- Check inheritance of defaults and constraints
 130 --
 131
 132 CREATE TABLE INSERT_CHILD (cx INT default 42,
 133         cy INT CHECK (cy > x))
 134         INHERITS (INSERT_TBL);
 135
 136 INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,11);
 137 INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,6);
 138 INSERT INTO INSERT_CHILD(x,z,cy) VALUES (6,-7,7);
 139 INSERT INTO INSERT_CHILD(x,y,z,cy) VALUES (6,'check failed',-6,7);
 140
 141 SELECT * FROM INSERT_CHILD;
 142
 143 DROP TABLE INSERT_CHILD;
 144
 145 --
 146 -- Check constraints on INSERT INTO
 147 --
 148
 149 DELETE FROM INSERT_TBL;
 150
 151 ALTER SEQUENCE INSERT_SEQ RESTART WITH 4;
 152
 153 CREATE TABLE tmp (xd INT, yd TEXT, zd INT);
 154
 155 INSERT INTO tmp VALUES (null, 'Y', null);
 156 INSERT INTO tmp VALUES (5, '!check failed', null);
 157 INSERT INTO tmp VALUES (null, 'try again', null);
 158 INSERT INTO INSERT_TBL(y) select yd from tmp;
 159
 160 SELECT '' AS three, * FROM INSERT_TBL;
 161
 162 INSERT INTO INSERT_TBL SELECT * FROM tmp WHERE yd = 'try again';
 163 INSERT INTO INSERT_TBL(y,z) SELECT yd, -7 FROM tmp WHERE yd = 'try again';
 164 INSERT INTO INSERT_TBL(y,z) SELECT yd, -8 FROM tmp WHERE yd = 'try again';
 165
 166 SELECT '' AS four, * FROM INSERT_TBL;
 167
 168 DROP TABLE tmp;
 169
 170 --
 171 -- Check constraints on UPDATE
 172 --
 173
 174 UPDATE INSERT_TBL SET x = NULL WHERE x = 5;
 175 UPDATE INSERT_TBL SET x = 6 WHERE x = 6;
 176 UPDATE INSERT_TBL SET x = -z, z = -x;
 177 UPDATE INSERT_TBL SET x = z, z = x;
 178
 179 SELECT * FROM INSERT_TBL;
 180
 181 -- DROP TABLE INSERT_TBL;
 182
 183 --
 184 -- Check constraints on COPY FROM
 185 --
 186
 187 CREATE TABLE COPY_TBL (x INT, y TEXT, z INT,
 188         CONSTRAINT COPY_CON
 189         CHECK (x > 3 AND y <> 'check failed' AND x < 7 ));
 190
 191 COPY COPY_TBL FROM '@abs_srcdir@/data/constro.data';
 192
 193 SELECT '' AS two, * FROM COPY_TBL;
 194
 195 COPY COPY_TBL FROM '@abs_srcdir@/data/constrf.data';
 196
 197 SELECT * FROM COPY_TBL;
 198
 199 --
 200 -- Primary keys
 201 --
 202
 203 CREATE TABLE PRIMARY_TBL (i int PRIMARY KEY, t text);
 204
 205 INSERT INTO PRIMARY_TBL VALUES (1, 'one');
 206 INSERT INTO PRIMARY_TBL VALUES (2, 'two');
 207 INSERT INTO PRIMARY_TBL VALUES (1, 'three');
 208 INSERT INTO PRIMARY_TBL VALUES (4, 'three');
 209 INSERT INTO PRIMARY_TBL VALUES (5, 'one');
 210 INSERT INTO PRIMARY_TBL (t) VALUES ('six');
 211
 212 SELECT '' AS four, * FROM PRIMARY_TBL;
 213
 214 DROP TABLE PRIMARY_TBL;
 215
 216 CREATE TABLE PRIMARY_TBL (i int, t text,
 217         PRIMARY KEY(i,t));
 218
 219 INSERT INTO PRIMARY_TBL VALUES (1, 'one');
 220 INSERT INTO PRIMARY_TBL VALUES (2, 'two');
 221 INSERT INTO PRIMARY_TBL VALUES (1, 'three');
 222 INSERT INTO PRIMARY_TBL VALUES (4, 'three');
 223 INSERT INTO PRIMARY_TBL VALUES (5, 'one');
 224 INSERT INTO PRIMARY_TBL (t) VALUES ('six');
 225
 226 SELECT '' AS three, * FROM PRIMARY_TBL;
 227
 228 DROP TABLE PRIMARY_TBL;
 229
 230 --
 231 -- Unique keys
 232 --
 233
 234 CREATE TABLE UNIQUE_TBL (i int UNIQUE, t text);
 235
 236 INSERT INTO UNIQUE_TBL VALUES (1, 'one');
 237 INSERT INTO UNIQUE_TBL VALUES (2, 'two');
 238 INSERT INTO UNIQUE_TBL VALUES (1, 'three');
 239 INSERT INTO UNIQUE_TBL VALUES (4, 'four');
 240 INSERT INTO UNIQUE_TBL VALUES (5, 'one');
 241 INSERT INTO UNIQUE_TBL (t) VALUES ('six');
 242 INSERT INTO UNIQUE_TBL (t) VALUES ('seven');
 243
 244 SELECT '' AS five, * FROM UNIQUE_TBL;
 245
 246 DROP TABLE UNIQUE_TBL;
 247
 248 CREATE TABLE UNIQUE_TBL (i int, t text,
 249         UNIQUE(i,t));
 250
 251 INSERT INTO UNIQUE_TBL VALUES (1, 'one');
 252 INSERT INTO UNIQUE_TBL VALUES (2, 'two');
 253 INSERT INTO UNIQUE_TBL VALUES (1, 'three');
 254 INSERT INTO UNIQUE_TBL VALUES (1, 'one');
 255 INSERT INTO UNIQUE_TBL VALUES (5, 'one');
 256 INSERT INTO UNIQUE_TBL (t) VALUES ('six');
 257
 258 SELECT '' AS five, * FROM UNIQUE_TBL;
 259
 260 DROP TABLE UNIQUE_TBL;
 261