3 -- Constraints can be specified with:
6 -- - PRIMARY KEY clauses
13 CREATE TABLE DEFAULT_TBL (i int DEFAULT 100,
14 x text DEFAULT 'vadim', f float8 DEFAULT 123.456);
15 INSERT INTO DEFAULT_TBL VALUES (1, 'thomas', 57.0613);
16 INSERT INTO DEFAULT_TBL VALUES (1, 'bruce');
17 INSERT INTO DEFAULT_TBL (i, f) VALUES (2, 987.654);
18 INSERT INTO DEFAULT_TBL (x) VALUES ('marc');
19 INSERT INTO DEFAULT_TBL VALUES (3, null, 1.0);
20 SELECT '' AS five, * FROM DEFAULT_TBL;
22 ------+-----+--------+---------
23 | 1 | thomas | 57.0613
26 | 100 | marc | 123.456
30 CREATE SEQUENCE DEFAULT_SEQ;
31 CREATE TABLE DEFAULTEXPR_TBL (i1 int DEFAULT 100 + (200-199) * 2,
32 i2 int DEFAULT nextval('default_seq'));
33 INSERT INTO DEFAULTEXPR_TBL VALUES (-1, -2);
34 INSERT INTO DEFAULTEXPR_TBL (i1) VALUES (-3);
35 INSERT INTO DEFAULTEXPR_TBL (i2) VALUES (-4);
36 INSERT INTO DEFAULTEXPR_TBL (i2) VALUES (NULL);
37 SELECT '' AS four, * FROM DEFAULTEXPR_TBL;
47 -- test for extraneous comma
48 CREATE TABLE error_tbl (i int DEFAULT (100, ));
49 ERROR: syntax error at or near ")"
50 LINE 1: CREATE TABLE error_tbl (i int DEFAULT (100, ));
52 -- this will fail because gram.y uses b_expr not a_expr for defaults,
53 -- to avoid a shift/reduce conflict that arises from NOT NULL being
54 -- part of the column definition syntax:
55 CREATE TABLE error_tbl (b1 bool DEFAULT 1 IN (1, 2));
56 ERROR: syntax error at or near "IN"
57 LINE 1: CREATE TABLE error_tbl (b1 bool DEFAULT 1 IN (1, 2));
59 -- this should work, however:
60 CREATE TABLE error_tbl (b1 bool DEFAULT (1 IN (1, 2)));
65 CREATE TABLE CHECK_TBL (x int,
66 CONSTRAINT CHECK_CON CHECK (x > 3));
67 INSERT INTO CHECK_TBL VALUES (5);
68 INSERT INTO CHECK_TBL VALUES (4);
69 INSERT INTO CHECK_TBL VALUES (3);
70 ERROR: new row for relation "check_tbl" violates check constraint "check_con"
71 INSERT INTO CHECK_TBL VALUES (2);
72 ERROR: new row for relation "check_tbl" violates check constraint "check_con"
73 INSERT INTO CHECK_TBL VALUES (6);
74 INSERT INTO CHECK_TBL VALUES (1);
75 ERROR: new row for relation "check_tbl" violates check constraint "check_con"
76 SELECT '' AS three, * FROM CHECK_TBL;
84 CREATE SEQUENCE CHECK_SEQ;
85 CREATE TABLE CHECK2_TBL (x int, y text, z int,
86 CONSTRAINT SEQUENCE_CON
87 CHECK (x > 3 and y <> 'check failed' and z < 8));
88 INSERT INTO CHECK2_TBL VALUES (4, 'check ok', -2);
89 INSERT INTO CHECK2_TBL VALUES (1, 'x check failed', -2);
90 ERROR: new row for relation "check2_tbl" violates check constraint "sequence_con"
91 INSERT INTO CHECK2_TBL VALUES (5, 'z check failed', 10);
92 ERROR: new row for relation "check2_tbl" violates check constraint "sequence_con"
93 INSERT INTO CHECK2_TBL VALUES (0, 'check failed', -2);
94 ERROR: new row for relation "check2_tbl" violates check constraint "sequence_con"
95 INSERT INTO CHECK2_TBL VALUES (6, 'check failed', 11);
96 ERROR: new row for relation "check2_tbl" violates check constraint "sequence_con"
97 INSERT INTO CHECK2_TBL VALUES (7, 'check ok', 7);
98 SELECT '' AS two, * from CHECK2_TBL;
100 -----+---+----------+----
106 -- Check constraints on INSERT
108 CREATE SEQUENCE INSERT_SEQ;
109 CREATE TABLE INSERT_TBL (x INT DEFAULT nextval('insert_seq'),
110 y TEXT DEFAULT '-NULL-',
111 z INT DEFAULT -1 * currval('insert_seq'),
112 CONSTRAINT INSERT_CON CHECK (x >= 3 AND y <> 'check failed' AND x < 8),
114 INSERT INTO INSERT_TBL(x,z) VALUES (2, -2);
115 ERROR: new row for relation "insert_tbl" violates check constraint "insert_con"
116 SELECT '' AS zero, * FROM INSERT_TBL;
121 SELECT 'one' AS one, nextval('insert_seq');
127 INSERT INTO INSERT_TBL(y) VALUES ('Y');
128 ERROR: new row for relation "insert_tbl" violates check constraint "insert_con"
129 INSERT INTO INSERT_TBL(y) VALUES ('Y');
130 INSERT INTO INSERT_TBL(x,z) VALUES (1, -2);
131 ERROR: new row for relation "insert_tbl" violates check constraint "insert_tbl_check"
132 INSERT INTO INSERT_TBL(z,x) VALUES (-7, 7);
133 INSERT INTO INSERT_TBL VALUES (5, 'check failed', -5);
134 ERROR: new row for relation "insert_tbl" violates check constraint "insert_con"
135 INSERT INTO INSERT_TBL VALUES (7, '!check failed', -7);
136 INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-');
137 SELECT '' AS four, * FROM INSERT_TBL;
139 ------+---+---------------+----
142 | 7 | !check failed | -7
146 INSERT INTO INSERT_TBL(y,z) VALUES ('check failed', 4);
147 ERROR: new row for relation "insert_tbl" violates check constraint "insert_tbl_check"
148 INSERT INTO INSERT_TBL(x,y) VALUES (5, 'check failed');
149 ERROR: new row for relation "insert_tbl" violates check constraint "insert_con"
150 INSERT INTO INSERT_TBL(x,y) VALUES (5, '!check failed');
151 INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-');
152 SELECT '' AS six, * FROM INSERT_TBL;
154 -----+---+---------------+----
157 | 7 | !check failed | -7
159 | 5 | !check failed | -5
163 SELECT 'seven' AS one, nextval('insert_seq');
169 INSERT INTO INSERT_TBL(y) VALUES ('Y');
170 ERROR: new row for relation "insert_tbl" violates check constraint "insert_con"
171 SELECT 'eight' AS one, currval('insert_seq');
177 -- According to SQL92, it is OK to insert a record that gives rise to NULL
178 -- constraint-condition results. Postgres used to reject this, but it
180 INSERT INTO INSERT_TBL VALUES (null, null, null);
181 SELECT '' AS nine, * FROM INSERT_TBL;
183 ------+---+---------------+----
186 | 7 | !check failed | -7
188 | 5 | !check failed | -5
194 -- Check inheritance of defaults and constraints
196 CREATE TABLE INSERT_CHILD (cx INT default 42,
197 cy INT CHECK (cy > x))
198 INHERITS (INSERT_TBL);
199 INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,11);
200 INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,6);
201 ERROR: new row for relation "insert_child" violates check constraint "insert_child_check"
202 INSERT INTO INSERT_CHILD(x,z,cy) VALUES (6,-7,7);
203 ERROR: new row for relation "insert_child" violates check constraint "insert_tbl_check"
204 INSERT INTO INSERT_CHILD(x,y,z,cy) VALUES (6,'check failed',-6,7);
205 ERROR: new row for relation "insert_child" violates check constraint "insert_con"
206 SELECT * FROM INSERT_CHILD;
208 ---+--------+----+----+----
209 7 | -NULL- | -7 | 42 | 11
212 DROP TABLE INSERT_CHILD;
214 -- Check constraints on INSERT INTO
216 DELETE FROM INSERT_TBL;
217 ALTER SEQUENCE INSERT_SEQ RESTART WITH 4;
218 CREATE TABLE tmp (xd INT, yd TEXT, zd INT);
219 INSERT INTO tmp VALUES (null, 'Y', null);
220 INSERT INTO tmp VALUES (5, '!check failed', null);
221 INSERT INTO tmp VALUES (null, 'try again', null);
222 INSERT INTO INSERT_TBL(y) select yd from tmp;
223 SELECT '' AS three, * FROM INSERT_TBL;
225 -------+---+---------------+----
227 | 5 | !check failed | -5
231 INSERT INTO INSERT_TBL SELECT * FROM tmp WHERE yd = 'try again';
232 INSERT INTO INSERT_TBL(y,z) SELECT yd, -7 FROM tmp WHERE yd = 'try again';
233 INSERT INTO INSERT_TBL(y,z) SELECT yd, -8 FROM tmp WHERE yd = 'try again';
234 ERROR: new row for relation "insert_tbl" violates check constraint "insert_con"
235 SELECT '' AS four, * FROM INSERT_TBL;
237 ------+---+---------------+----
239 | 5 | !check failed | -5
247 -- Check constraints on UPDATE
249 UPDATE INSERT_TBL SET x = NULL WHERE x = 5;
250 UPDATE INSERT_TBL SET x = 6 WHERE x = 6;
251 UPDATE INSERT_TBL SET x = -z, z = -x;
252 UPDATE INSERT_TBL SET x = z, z = x;
253 ERROR: new row for relation "insert_tbl" violates check constraint "insert_con"
254 SELECT * FROM INSERT_TBL;
256 ---+---------------+----
264 -- DROP TABLE INSERT_TBL;
266 -- Check constraints on COPY FROM
268 CREATE TABLE COPY_TBL (x INT, y TEXT, z INT,
270 CHECK (x > 3 AND y <> 'check failed' AND x < 7 ));
271 COPY COPY_TBL FROM '@abs_srcdir@/data/constro.data';
272 SELECT '' AS two, * FROM COPY_TBL;
274 -----+---+---------------+---
275 | 4 | !check failed | 5
279 COPY COPY_TBL FROM '@abs_srcdir@/data/constrf.data';
280 ERROR: new row for relation "copy_tbl" violates check constraint "copy_con"
281 CONTEXT: COPY copy_tbl, line 2: "7 check failed 6"
282 SELECT * FROM COPY_TBL;
284 ---+---------------+---
285 4 | !check failed | 5
292 CREATE TABLE PRIMARY_TBL (i int PRIMARY KEY, t text);
293 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "primary_tbl_pkey" for table "primary_tbl"
294 INSERT INTO PRIMARY_TBL VALUES (1, 'one');
295 INSERT INTO PRIMARY_TBL VALUES (2, 'two');
296 INSERT INTO PRIMARY_TBL VALUES (1, 'three');
297 ERROR: duplicate key value violates unique constraint "primary_tbl_pkey"
298 DETAIL: Key (i)=(1) already exists.
299 INSERT INTO PRIMARY_TBL VALUES (4, 'three');
300 INSERT INTO PRIMARY_TBL VALUES (5, 'one');
301 INSERT INTO PRIMARY_TBL (t) VALUES ('six');
302 ERROR: null value in column "i" violates not-null constraint
303 SELECT '' AS four, * FROM PRIMARY_TBL;
312 DROP TABLE PRIMARY_TBL;
313 CREATE TABLE PRIMARY_TBL (i int, t text,
315 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "primary_tbl_pkey" for table "primary_tbl"
316 INSERT INTO PRIMARY_TBL VALUES (1, 'one');
317 INSERT INTO PRIMARY_TBL VALUES (2, 'two');
318 INSERT INTO PRIMARY_TBL VALUES (1, 'three');
319 INSERT INTO PRIMARY_TBL VALUES (4, 'three');
320 INSERT INTO PRIMARY_TBL VALUES (5, 'one');
321 INSERT INTO PRIMARY_TBL (t) VALUES ('six');
322 ERROR: null value in column "i" violates not-null constraint
323 SELECT '' AS three, * FROM PRIMARY_TBL;
333 DROP TABLE PRIMARY_TBL;
337 CREATE TABLE UNIQUE_TBL (i int UNIQUE, t text);
338 NOTICE: CREATE TABLE / UNIQUE will create implicit index "unique_tbl_i_key" for table "unique_tbl"
339 INSERT INTO UNIQUE_TBL VALUES (1, 'one');
340 INSERT INTO UNIQUE_TBL VALUES (2, 'two');
341 INSERT INTO UNIQUE_TBL VALUES (1, 'three');
342 ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
343 DETAIL: Key (i)=(1) already exists.
344 INSERT INTO UNIQUE_TBL VALUES (4, 'four');
345 INSERT INTO UNIQUE_TBL VALUES (5, 'one');
346 INSERT INTO UNIQUE_TBL (t) VALUES ('six');
347 INSERT INTO UNIQUE_TBL (t) VALUES ('seven');
348 SELECT '' AS five, * FROM UNIQUE_TBL;
359 DROP TABLE UNIQUE_TBL;
360 CREATE TABLE UNIQUE_TBL (i int, t text,
362 NOTICE: CREATE TABLE / UNIQUE will create implicit index "unique_tbl_i_t_key" for table "unique_tbl"
363 INSERT INTO UNIQUE_TBL VALUES (1, 'one');
364 INSERT INTO UNIQUE_TBL VALUES (2, 'two');
365 INSERT INTO UNIQUE_TBL VALUES (1, 'three');
366 INSERT INTO UNIQUE_TBL VALUES (1, 'one');
367 ERROR: duplicate key value violates unique constraint "unique_tbl_i_t_key"
368 DETAIL: Key (i, t)=(1, one) already exists.
369 INSERT INTO UNIQUE_TBL VALUES (5, 'one');
370 INSERT INTO UNIQUE_TBL (t) VALUES ('six');
371 SELECT '' AS five, * FROM UNIQUE_TBL;
381 DROP TABLE UNIQUE_TBL;
383 -- Deferrable unique constraints
385 CREATE TABLE unique_tbl (i int UNIQUE DEFERRABLE, t text);
386 NOTICE: CREATE TABLE / UNIQUE will create implicit index "unique_tbl_i_key" for table "unique_tbl"
387 INSERT INTO unique_tbl VALUES (0, 'one');
388 INSERT INTO unique_tbl VALUES (1, 'two');
389 INSERT INTO unique_tbl VALUES (2, 'tree');
390 INSERT INTO unique_tbl VALUES (3, 'four');
391 INSERT INTO unique_tbl VALUES (4, 'five');
393 -- default is immediate so this should fail right away
394 UPDATE unique_tbl SET i = 1 WHERE i = 0;
395 ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
396 DETAIL: Key (i)=(1) already exists.
398 -- check is done at end of statement, so this should succeed
399 UPDATE unique_tbl SET i = i+1;
400 SELECT * FROM unique_tbl;
410 -- explicitly defer the constraint
412 SET CONSTRAINTS unique_tbl_i_key DEFERRED;
413 INSERT INTO unique_tbl VALUES (3, 'three');
414 DELETE FROM unique_tbl WHERE t = 'tree'; -- makes constraint valid again
415 COMMIT; -- should succeed
416 SELECT * FROM unique_tbl;
426 -- try adding an initially deferred constraint
427 ALTER TABLE unique_tbl DROP CONSTRAINT unique_tbl_i_key;
428 ALTER TABLE unique_tbl ADD CONSTRAINT unique_tbl_i_key
429 UNIQUE (i) DEFERRABLE INITIALLY DEFERRED;
430 NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "unique_tbl_i_key" for table "unique_tbl"
432 INSERT INTO unique_tbl VALUES (1, 'five');
433 INSERT INTO unique_tbl VALUES (5, 'one');
434 UPDATE unique_tbl SET i = 4 WHERE i = 2;
435 UPDATE unique_tbl SET i = 2 WHERE i = 4 AND t = 'four';
436 DELETE FROM unique_tbl WHERE i = 1 AND t = 'one';
437 DELETE FROM unique_tbl WHERE i = 5 AND t = 'five';
439 SELECT * FROM unique_tbl;
449 -- should fail at commit-time
451 INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now
452 COMMIT; -- should fail
453 ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
454 DETAIL: Key (i)=(3) already exists.
455 -- make constraint check immediate
457 SET CONSTRAINTS ALL IMMEDIATE;
458 INSERT INTO unique_tbl VALUES (3, 'Three'); -- should fail
459 ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
460 DETAIL: Key (i)=(3) already exists.
462 -- forced check when SET CONSTRAINTS is called
464 SET CONSTRAINTS ALL DEFERRED;
465 INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now
466 SET CONSTRAINTS ALL IMMEDIATE; -- should fail
467 ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
468 DETAIL: Key (i)=(3) already exists.
470 -- test a HOT update that invalidates the conflicting tuple.
471 -- the trigger should still fire and catch the violation
473 INSERT INTO unique_tbl VALUES (3, 'Three'); -- should succeed for now
474 UPDATE unique_tbl SET t = 'THREE' WHERE i = 3 AND t = 'Three';
475 COMMIT; -- should fail
476 ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
477 DETAIL: Key (i)=(3) already exists.
478 SELECT * FROM unique_tbl;
488 -- test a HOT update that modifies the newly inserted tuple,
489 -- but should succeed because we then remove the other conflicting tuple.
491 INSERT INTO unique_tbl VALUES(3, 'tree'); -- should succeed for now
492 UPDATE unique_tbl SET t = 'threex' WHERE t = 'tree';
493 DELETE FROM unique_tbl WHERE t = 'three';
494 SELECT * FROM unique_tbl;
505 SELECT * FROM unique_tbl;
515 DROP TABLE unique_tbl;
517 -- EXCLUDE constraints
519 CREATE TABLE circles (
523 (c1 WITH &&, (c2::circle) WITH ~=)
524 WHERE (circle_center(c1) <> '(0,0)')
526 NOTICE: CREATE TABLE / EXCLUDE will create implicit index "circles_c1_c2_exclusion" for table "circles"
527 -- these should succeed because they don't match the index predicate
528 INSERT INTO circles VALUES('<(0,0), 5>', '<(0,0), 5>');
529 INSERT INTO circles VALUES('<(0,0), 5>', '<(0,0), 5>');
531 INSERT INTO circles VALUES('<(10,10), 10>', '<(0,0), 5>');
533 INSERT INTO circles VALUES('<(20,20), 10>', '<(0,0), 5>');
534 ERROR: conflicting key value violates exclusion constraint "circles_c1_c2_exclusion"
535 DETAIL: Key (c1, (c2::circle))=(<(20,20),10>, <(0,0),5>) conflicts with existing key (c1, (c2::circle))=(<(10,10),10>, <(0,0),5>).
536 -- succeed because c1 doesn't overlap
537 INSERT INTO circles VALUES('<(20,20), 1>', '<(0,0), 5>');
538 -- succeed because c2 is not the same
539 INSERT INTO circles VALUES('<(20,20), 10>', '<(1,1), 5>');
540 -- should fail on existing data without the WHERE clause
541 ALTER TABLE circles ADD EXCLUDE USING gist
542 (c1 WITH &&, (c2::circle) WITH ~=);
543 NOTICE: ALTER TABLE / ADD EXCLUDE will create implicit index "circles_c1_c2_exclusion1" for table "circles"
544 ERROR: could not create exclusion constraint "circles_c1_c2_exclusion1"
545 DETAIL: Key (c1, (c2::circle))=(<(0,0),5>, <(0,0),5>) conflicts with key (c1, (c2::circle))=(<(0,0),5>, <(0,0),5>).
547 -- Check deferred exclusion constraint
548 CREATE TABLE deferred_excl (
550 CONSTRAINT deferred_excl_con EXCLUDE (f1 WITH =) INITIALLY DEFERRED
552 NOTICE: CREATE TABLE / EXCLUDE will create implicit index "deferred_excl_con" for table "deferred_excl"
553 INSERT INTO deferred_excl VALUES(1);
554 INSERT INTO deferred_excl VALUES(2);
555 INSERT INTO deferred_excl VALUES(1); -- fail
556 ERROR: conflicting key value violates exclusion constraint "deferred_excl_con"
557 DETAIL: Key (f1)=(1) conflicts with existing key (f1)=(1).
559 INSERT INTO deferred_excl VALUES(2); -- no fail here
560 COMMIT; -- should fail here
561 ERROR: conflicting key value violates exclusion constraint "deferred_excl_con"
562 DETAIL: Key (f1)=(2) conflicts with existing key (f1)=(2).
564 INSERT INTO deferred_excl VALUES(3);
565 INSERT INTO deferred_excl VALUES(3); -- no fail here
566 COMMIT; -- should fail here
567 ERROR: conflicting key value violates exclusion constraint "deferred_excl_con"
568 DETAIL: Key (f1)=(3) conflicts with existing key (f1)=(3).
569 ALTER TABLE deferred_excl DROP CONSTRAINT deferred_excl_con;
570 -- This should fail, but worth testing because of HOT updates
571 UPDATE deferred_excl SET f1 = 3;
572 ALTER TABLE deferred_excl ADD EXCLUDE (f1 WITH =);
573 NOTICE: ALTER TABLE / ADD EXCLUDE will create implicit index "deferred_excl_f1_exclusion" for table "deferred_excl"
574 ERROR: could not create exclusion constraint "deferred_excl_f1_exclusion"
575 DETAIL: Key (f1)=(3) conflicts with key (f1)=(3).
576 DROP TABLE deferred_excl;