1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
DROP TABLE "Tab" IF EXISTS;
DROP TABLE TC0 IF EXISTS;
DROP TABLE TC1 IF EXISTS;
DROP TABLE TC2 IF EXISTS;
DROP TABLE TC3 IF EXISTS;
DROP TABLE TC4 IF EXISTS;
DROP TABLE TC5 IF EXISTS;
CREATE TABLE "TAB"("a" INT, "b" INT, "c" INT, CHECK("a" > "b" AND "b">"c"));
/*E*/INSERT INTO TAB VALUES(null,2,3);
INSERT INTO TAB VALUES(2,1,null);
INSERT INTO TAB VALUES(NULL,2,NULL);
INSERT INTO TAB VALUES(NULL,NULL,NULL);
CREATE TABLE TC0(A INT, B INT, C INT, CHECK(A > B AND B>C));
CREATE TABLE TC1(A CHAR(10), B CHAR(10), C CHAR(10), CHECK(TRIM(BOTH '*' FROM A) > TRIM(LEADING FROM B)));
CREATE TABLE TC2(A CHAR(10), B CHAR(10), C CHAR(10), CHECK(TRIM(TRAILING '*' FROM A) > UPPER(B)));
CREATE TABLE TC3(A CHAR(10), B CHAR(10), C CHAR(10), CHECK(A LIKE B ESCAPE ';' AND B LIKE 'test%'));
CREATE TABLE TC4(A CHAR(10), B CHAR(10), C CHAR(10), D INT, CHECK(SUBSTRING(A FROM D FOR 3) LIKE C ESCAPE ';'));
CREATE TABLE TC5(A CHAR(10), B CHAR(10), C CHAR(10), D INT, CHECK(A IN (B,C, 'Sunday', 'Monday')));
INSERT INTO TC5(A,C) VALUES ('Sunday', null);
INSERT INTO TC5(A,C) VALUES ('Today', 'Today');
INSERT INTO TC5(A,C) VALUES ('Tomorrow', 'Tomorrow');
/*e*/INSERT INTO TC5(A,C) VALUES ('Yesterday', 'Tomorrow');
INSERT INTO TC5(A,C) VALUES (null, null);
/*c4*/SELECT * FROM TC5;
ALTER TABLE TC5 DROP COLUMN D
/*c4*/SELECT * FROM TC5;
INSERT INTO TC5(A) VALUES ('Monday');
UPDATE TC5 SET A='Monday' WHERE B IS NULL;
/*e*/INSERT INTO TC5(A,C) VALUES ('Yesterday', 'Tomorrow');
/*e*/CREATE TABLE TC6(A CHAR, B CHAR, C CHAR, D INT, CHECK(A IN (SELECT A FROM TC5)));
CREATE TABLE TC6(A INT, CHECK(A IS NULL OR A > 1));
INSERT INTO TC6(A) VALUES (2);
INSERT INTO TC6(A) VALUES (null);
/*e*/INSERT INTO TC6(A) VALUES (0);
CREATE TABLE TC7(A INT, B TIMESTAMP, CONSTRAINT CH1 CHECK(B > CURRENT_TIMESTAMP));
INSERT INTO TC7 VALUES (10, '2015-01-01 12:00:00');
/*e*/INSERT INTO TC7 VALUES (10, '2004-01-01 12:00:00');
INSERT INTO TC7 VALUES (11, NULL);
SCRIPT
-- some type conversion tests
ALTER TABLE TC7 ALTER COLUMN A DECIMAL(6,2);
/*e*/ALTER TABLE TC7 ALTER COLUMN B DATE;
/*r
10.00,2015-01-01 12:00:00.000000000
11.00,NULL
*/SELECT * FROM TC7 ORDER BY A;
ALTER TABLE TC7 DROP CONSTRAINT CH1;
ALTER TABLE TC7 ALTER COLUMN B DATE;
/*r
10.00,2015-01-01
11.00,NULL
*/SELECT * FROM TC7 ORDER BY A;
|
