granicus.if.org Git - postgresql/blob - src/test/regress/output/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 -- DEFAULT syntax
  11 --
  12 CREATE TABLE DEFAULT_TBL (i int DEFAULT 100,
  13         x text DEFAULT 'vadim', f float8 DEFAULT 123.456);
  14 INSERT INTO DEFAULT_TBL VALUES (1, 'thomas', 57.0613);
  15 INSERT INTO DEFAULT_TBL VALUES (1, 'bruce');
  16 INSERT INTO DEFAULT_TBL (i, f) VALUES (2, 987.654);
  17 INSERT INTO DEFAULT_TBL (x) VALUES ('marc');
  18 INSERT INTO DEFAULT_TBL VALUES (3, null, 1.0);
  19 SELECT '' AS five, * FROM DEFAULT_TBL;
  20  five |  i  |   x    |    f
  21 ------+-----+--------+---------
  22       |   1 | thomas | 57.0613
  23       |   1 | bruce  | 123.456
  24       |   2 | vadim  | 987.654
  25       | 100 | marc   | 123.456
  26       |   3 |        |       1
  27 (5 rows)
  28
  29 CREATE SEQUENCE DEFAULT_SEQ;
  30 CREATE TABLE DEFAULTEXPR_TBL (i1 int DEFAULT 100 + (200-199) * 2,
  31         i2 int DEFAULT nextval('default_seq'));
  32 INSERT INTO DEFAULTEXPR_TBL VALUES (-1, -2);
  33 INSERT INTO DEFAULTEXPR_TBL (i1) VALUES (-3);
  34 INSERT INTO DEFAULTEXPR_TBL (i2) VALUES (-4);
  35 INSERT INTO DEFAULTEXPR_TBL (i2) VALUES (NULL);
  36 SELECT '' AS four, * FROM DEFAULTEXPR_TBL;
  37  four | i1  | i2
  38 ------+-----+----
  39       |  -1 | -2
  40       |  -3 |  1
  41       | 102 | -4
  42       | 102 |
  43 (4 rows)
  44
  45 -- syntax errors
  46 --  test for extraneous comma
  47 CREATE TABLE error_tbl (i int DEFAULT (100, ));
  48 ERROR:  syntax error at or near ")"
  49 LINE 1: CREATE TABLE error_tbl (i int DEFAULT (100, ));
  50                                                     ^
  51 --  this will fail because gram.y uses b_expr not a_expr for defaults,
  52 --  to avoid a shift/reduce conflict that arises from NOT NULL being
  53 --  part of the column definition syntax:
  54 CREATE TABLE error_tbl (b1 bool DEFAULT 1 IN (1, 2));
  55 ERROR:  syntax error at or near "IN"
  56 LINE 1: CREATE TABLE error_tbl (b1 bool DEFAULT 1 IN (1, 2));
  57                                                   ^
  58 --  this should work, however:
  59 CREATE TABLE error_tbl (b1 bool DEFAULT (1 IN (1, 2)));
  60 DROP TABLE error_tbl;
  61 --
  62 -- CHECK syntax
  63 --
  64 CREATE TABLE CHECK_TBL (x int,
  65         CONSTRAINT CHECK_CON CHECK (x > 3));
  66 INSERT INTO CHECK_TBL VALUES (5);
  67 INSERT INTO CHECK_TBL VALUES (4);
  68 INSERT INTO CHECK_TBL VALUES (3);
  69 ERROR:  new row for relation "check_tbl" violates check constraint "check_con"
  70 INSERT INTO CHECK_TBL VALUES (2);
  71 ERROR:  new row for relation "check_tbl" violates check constraint "check_con"
  72 INSERT INTO CHECK_TBL VALUES (6);
  73 INSERT INTO CHECK_TBL VALUES (1);
  74 ERROR:  new row for relation "check_tbl" violates check constraint "check_con"
  75 SELECT '' AS three, * FROM CHECK_TBL;
  76  three | x
  77 -------+---
  78        | 5
  79        | 4
  80        | 6
  81 (3 rows)
  82
  83 CREATE SEQUENCE CHECK_SEQ;
  84 CREATE TABLE CHECK2_TBL (x int, y text, z int,
  85         CONSTRAINT SEQUENCE_CON
  86         CHECK (x > 3 and y <> 'check failed' and z < 8));
  87 INSERT INTO CHECK2_TBL VALUES (4, 'check ok', -2);
  88 INSERT INTO CHECK2_TBL VALUES (1, 'x check failed', -2);
  89 ERROR:  new row for relation "check2_tbl" violates check constraint "sequence_con"
  90 INSERT INTO CHECK2_TBL VALUES (5, 'z check failed', 10);
  91 ERROR:  new row for relation "check2_tbl" violates check constraint "sequence_con"
  92 INSERT INTO CHECK2_TBL VALUES (0, 'check failed', -2);
  93 ERROR:  new row for relation "check2_tbl" violates check constraint "sequence_con"
  94 INSERT INTO CHECK2_TBL VALUES (6, 'check failed', 11);
  95 ERROR:  new row for relation "check2_tbl" violates check constraint "sequence_con"
  96 INSERT INTO CHECK2_TBL VALUES (7, 'check ok', 7);
  97 SELECT '' AS two, * from CHECK2_TBL;
  98  two | x |    y     | z
  99 -----+---+----------+----
 100      | 4 | check ok | -2
 101      | 7 | check ok |  7
 102 (2 rows)
 103
 104 --
 105 -- Check constraints on INSERT
 106 --
 107 CREATE SEQUENCE INSERT_SEQ;
 108 CREATE TABLE INSERT_TBL (x INT DEFAULT nextval('insert_seq'),
 109         y TEXT DEFAULT '-NULL-',
 110         z INT DEFAULT -1 * currval('insert_seq'),
 111         CONSTRAINT INSERT_CON CHECK (x >= 3 AND y <> 'check failed' AND x < 8),
 112         CHECK (x + z = 0));
 113 INSERT INTO INSERT_TBL(x,z) VALUES (2, -2);
 114 ERROR:  new row for relation "insert_tbl" violates check constraint "insert_con"
 115 SELECT '' AS zero, * FROM INSERT_TBL;
 116  zero | x | y | z
 117 ------+---+---+---
 118 (0 rows)
 119
 120 SELECT 'one' AS one, nextval('insert_seq');
 121  one | nextval
 122 -----+---------
 123  one |       1
 124 (1 row)
 125
 126 INSERT INTO INSERT_TBL(y) VALUES ('Y');
 127 ERROR:  new row for relation "insert_tbl" violates check constraint "insert_con"
 128 INSERT INTO INSERT_TBL(y) VALUES ('Y');
 129 INSERT INTO INSERT_TBL(x,z) VALUES (1, -2);
 130 ERROR:  new row for relation "insert_tbl" violates check constraint "insert_tbl_check"
 131 INSERT INTO INSERT_TBL(z,x) VALUES (-7,  7);
 132 INSERT INTO INSERT_TBL VALUES (5, 'check failed', -5);
 133 ERROR:  new row for relation "insert_tbl" violates check constraint "insert_con"
 134 INSERT INTO INSERT_TBL VALUES (7, '!check failed', -7);
 135 INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-');
 136 SELECT '' AS four, * FROM INSERT_TBL;
 137  four | x |       y       | z
 138 ------+---+---------------+----
 139       | 3 | Y             | -3
 140       | 7 | -NULL-        | -7
 141       | 7 | !check failed | -7
 142       | 4 | -!NULL-       | -4
 143 (4 rows)
 144
 145 INSERT INTO INSERT_TBL(y,z) VALUES ('check failed', 4);
 146 ERROR:  new row for relation "insert_tbl" violates check constraint "insert_tbl_check"
 147 INSERT INTO INSERT_TBL(x,y) VALUES (5, 'check failed');
 148 ERROR:  new row for relation "insert_tbl" violates check constraint "insert_con"
 149 INSERT INTO INSERT_TBL(x,y) VALUES (5, '!check failed');
 150 INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-');
 151 SELECT '' AS six, * FROM INSERT_TBL;
 152  six | x |       y       | z
 153 -----+---+---------------+----
 154      | 3 | Y             | -3
 155      | 7 | -NULL-        | -7
 156      | 7 | !check failed | -7
 157      | 4 | -!NULL-       | -4
 158      | 5 | !check failed | -5
 159      | 6 | -!NULL-       | -6
 160 (6 rows)
 161
 162 SELECT 'seven' AS one, nextval('insert_seq');
 163   one  | nextval
 164 -------+---------
 165  seven |       7
 166 (1 row)
 167
 168 INSERT INTO INSERT_TBL(y) VALUES ('Y');
 169 ERROR:  new row for relation "insert_tbl" violates check constraint "insert_con"
 170 SELECT 'eight' AS one, currval('insert_seq');
 171   one  | currval
 172 -------+---------
 173  eight |       8
 174 (1 row)
 175
 176 -- According to SQL92, it is OK to insert a record that gives rise to NULL
 177 -- constraint-condition results.  Postgres used to reject this, but it
 178 -- was wrong:
 179 INSERT INTO INSERT_TBL VALUES (null, null, null);
 180 SELECT '' AS nine, * FROM INSERT_TBL;
 181  nine | x |       y       | z
 182 ------+---+---------------+----
 183       | 3 | Y             | -3
 184       | 7 | -NULL-        | -7
 185       | 7 | !check failed | -7
 186       | 4 | -!NULL-       | -4
 187       | 5 | !check failed | -5
 188       | 6 | -!NULL-       | -6
 189       |   |               |
 190 (7 rows)
 191
 192 --
 193 -- Check inheritance of defaults and constraints
 194 --
 195 CREATE TABLE INSERT_CHILD (cx INT default 42,
 196         cy INT CHECK (cy > x))
 197         INHERITS (INSERT_TBL);
 198 INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,11);
 199 INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,6);
 200 ERROR:  new row for relation "insert_child" violates check constraint "insert_child_check"
 201 INSERT INTO INSERT_CHILD(x,z,cy) VALUES (6,-7,7);
 202 ERROR:  new row for relation "insert_child" violates check constraint "insert_tbl_check"
 203 INSERT INTO INSERT_CHILD(x,y,z,cy) VALUES (6,'check failed',-6,7);
 204 ERROR:  new row for relation "insert_child" violates check constraint "insert_con"
 205 SELECT * FROM INSERT_CHILD;
 206  x |   y    | z  | cx | cy
 207 ---+--------+----+----+----
 208  7 | -NULL- | -7 | 42 | 11
 209 (1 row)
 210
 211 DROP TABLE INSERT_CHILD;
 212 --
 213 -- Check constraints on INSERT INTO
 214 --
 215 DELETE FROM INSERT_TBL;
 216 ALTER SEQUENCE INSERT_SEQ RESTART WITH 4;
 217 CREATE TABLE tmp (xd INT, yd TEXT, zd INT);
 218 INSERT INTO tmp VALUES (null, 'Y', null);
 219 INSERT INTO tmp VALUES (5, '!check failed', null);
 220 INSERT INTO tmp VALUES (null, 'try again', null);
 221 INSERT INTO INSERT_TBL(y) select yd from tmp;
 222 SELECT '' AS three, * FROM INSERT_TBL;
 223  three | x |       y       | z
 224 -------+---+---------------+----
 225        | 4 | Y             | -4
 226        | 5 | !check failed | -5
 227        | 6 | try again     | -6
 228 (3 rows)
 229
 230 INSERT INTO INSERT_TBL SELECT * FROM tmp WHERE yd = 'try again';
 231 INSERT INTO INSERT_TBL(y,z) SELECT yd, -7 FROM tmp WHERE yd = 'try again';
 232 INSERT INTO INSERT_TBL(y,z) SELECT yd, -8 FROM tmp WHERE yd = 'try again';
 233 ERROR:  new row for relation "insert_tbl" violates check constraint "insert_con"
 234 SELECT '' AS four, * FROM INSERT_TBL;
 235  four | x |       y       | z
 236 ------+---+---------------+----
 237       | 4 | Y             | -4
 238       | 5 | !check failed | -5
 239       | 6 | try again     | -6
 240       |   | try again     |
 241       | 7 | try again     | -7
 242 (5 rows)
 243
 244 DROP TABLE tmp;
 245 --
 246 -- Check constraints on UPDATE
 247 --
 248 UPDATE INSERT_TBL SET x = NULL WHERE x = 5;
 249 UPDATE INSERT_TBL SET x = 6 WHERE x = 6;
 250 UPDATE INSERT_TBL SET x = -z, z = -x;
 251 UPDATE INSERT_TBL SET x = z, z = x;
 252 ERROR:  new row for relation "insert_tbl" violates check constraint "insert_con"
 253 SELECT * FROM INSERT_TBL;
 254  x |       y       | z
 255 ---+---------------+----
 256  4 | Y             | -4
 257    | try again     |
 258  7 | try again     | -7
 259  5 | !check failed |
 260  6 | try again     | -6
 261 (5 rows)
 262
 263 -- DROP TABLE INSERT_TBL;
 264 --
 265 -- Check constraints on COPY FROM
 266 --
 267 CREATE TABLE COPY_TBL (x INT, y TEXT, z INT,
 268         CONSTRAINT COPY_CON
 269         CHECK (x > 3 AND y <> 'check failed' AND x < 7 ));
 270 COPY COPY_TBL FROM '@abs_srcdir@/data/constro.data';
 271 SELECT '' AS two, * FROM COPY_TBL;
 272  two | x |       y       | z
 273 -----+---+---------------+---
 274      | 4 | !check failed | 5
 275      | 6 | OK            | 4
 276 (2 rows)
 277
 278 COPY COPY_TBL FROM '@abs_srcdir@/data/constrf.data';
 279 ERROR:  new row for relation "copy_tbl" violates check constraint "copy_con"
 280 CONTEXT:  COPY copy_tbl, line 2: "7     check failed    6"
 281 SELECT * FROM COPY_TBL;
 282  x |       y       | z
 283 ---+---------------+---
 284  4 | !check failed | 5
 285  6 | OK            | 4
 286 (2 rows)
 287
 288 --
 289 -- Primary keys
 290 --
 291 CREATE TABLE PRIMARY_TBL (i int PRIMARY KEY, t text);
 292 NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "primary_tbl_pkey" for table "primary_tbl"
 293 INSERT INTO PRIMARY_TBL VALUES (1, 'one');
 294 INSERT INTO PRIMARY_TBL VALUES (2, 'two');
 295 INSERT INTO PRIMARY_TBL VALUES (1, 'three');
 296 ERROR:  duplicate key value violates unique constraint "primary_tbl_pkey"
 297 INSERT INTO PRIMARY_TBL VALUES (4, 'three');
 298 INSERT INTO PRIMARY_TBL VALUES (5, 'one');
 299 INSERT INTO PRIMARY_TBL (t) VALUES ('six');
 300 ERROR:  null value in column "i" violates not-null constraint
 301 SELECT '' AS four, * FROM PRIMARY_TBL;
 302  four | i |   t
 303 ------+---+-------
 304       | 1 | one
 305       | 2 | two
 306       | 4 | three
 307       | 5 | one
 308 (4 rows)
 309
 310 DROP TABLE PRIMARY_TBL;
 311 CREATE TABLE PRIMARY_TBL (i int, t text,
 312         PRIMARY KEY(i,t));
 313 NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "primary_tbl_pkey" for table "primary_tbl"
 314 INSERT INTO PRIMARY_TBL VALUES (1, 'one');
 315 INSERT INTO PRIMARY_TBL VALUES (2, 'two');
 316 INSERT INTO PRIMARY_TBL VALUES (1, 'three');
 317 INSERT INTO PRIMARY_TBL VALUES (4, 'three');
 318 INSERT INTO PRIMARY_TBL VALUES (5, 'one');
 319 INSERT INTO PRIMARY_TBL (t) VALUES ('six');
 320 ERROR:  null value in column "i" violates not-null constraint
 321 SELECT '' AS three, * FROM PRIMARY_TBL;
 322  three | i |   t
 323 -------+---+-------
 324        | 1 | one
 325        | 2 | two
 326        | 1 | three
 327        | 4 | three
 328        | 5 | one
 329 (5 rows)
 330
 331 DROP TABLE PRIMARY_TBL;
 332 --
 333 -- Unique keys
 334 --
 335 CREATE TABLE UNIQUE_TBL (i int UNIQUE, t text);
 336 NOTICE:  CREATE TABLE / UNIQUE will create implicit index "unique_tbl_i_key" for table "unique_tbl"
 337 INSERT INTO UNIQUE_TBL VALUES (1, 'one');
 338 INSERT INTO UNIQUE_TBL VALUES (2, 'two');
 339 INSERT INTO UNIQUE_TBL VALUES (1, 'three');
 340 ERROR:  duplicate key value violates unique constraint "unique_tbl_i_key"
 341 INSERT INTO UNIQUE_TBL VALUES (4, 'four');
 342 INSERT INTO UNIQUE_TBL VALUES (5, 'one');
 343 INSERT INTO UNIQUE_TBL (t) VALUES ('six');
 344 INSERT INTO UNIQUE_TBL (t) VALUES ('seven');
 345 SELECT '' AS five, * FROM UNIQUE_TBL;
 346  five | i |   t
 347 ------+---+-------
 348       | 1 | one
 349       | 2 | two
 350       | 4 | four
 351       | 5 | one
 352       |   | six
 353       |   | seven
 354 (6 rows)
 355
 356 DROP TABLE UNIQUE_TBL;
 357 CREATE TABLE UNIQUE_TBL (i int, t text,
 358         UNIQUE(i,t));
 359 NOTICE:  CREATE TABLE / UNIQUE will create implicit index "unique_tbl_i_key" for table "unique_tbl"
 360 INSERT INTO UNIQUE_TBL VALUES (1, 'one');
 361 INSERT INTO UNIQUE_TBL VALUES (2, 'two');
 362 INSERT INTO UNIQUE_TBL VALUES (1, 'three');
 363 INSERT INTO UNIQUE_TBL VALUES (1, 'one');
 364 ERROR:  duplicate key value violates unique constraint "unique_tbl_i_key"
 365 INSERT INTO UNIQUE_TBL VALUES (5, 'one');
 366 INSERT INTO UNIQUE_TBL (t) VALUES ('six');
 367 SELECT '' AS five, * FROM UNIQUE_TBL;
 368  five | i |   t
 369 ------+---+-------
 370       | 1 | one
 371       | 2 | two
 372       | 1 | three
 373       | 5 | one
 374       |   | six
 375 (5 rows)
 376
 377 DROP TABLE UNIQUE_TBL;