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
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
|
ij> AUTOCOMMIT OFF;
ij> -- MODULE XTS752
-- SQL Test Suite, V6.0, Interactive SQL, xts752.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION CTS1
set schema CTS1;
0 rows inserted/updated/deleted
ij> --O SELECT USER FROM HU.ECCO;
VALUES USER;
1
--------------------------------------------------------------------------------------------------------------------------------
CTS1
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
--O ROLLBACK WORK;
-- date_time print
-- TEST:7052 ALTER TABLE ADD TABLE CONSTRAINT!
CREATE TABLE TAB752a
(COL1 NUMERIC(5) NOT NULL,
COL2 CHAR(15) NOT NULL UNIQUE,
COL3 CHAR(15));
0 rows inserted/updated/deleted
ij> -- PASS:7052 If table created successfully?
COMMIT WORK;
ij> CREATE TABLE TAB752b
(C1 NUMERIC(5) NOT NULL PRIMARY KEY,
C2 CHAR(15),
C3 CHAR(15));
0 rows inserted/updated/deleted
ij> -- PASS:7052 If table created successfully?
COMMIT WORK;
ij> --O ALTER TABLE CTS1.TAB752a
ALTER TABLE TAB752a
ADD CONSTRAINT TA752a_PRKEY PRIMARY KEY(COL1);
0 rows inserted/updated/deleted
ij> -- PASS:7052 If table altered successfully?
COMMIT WORK;
ij> --O SELECT COUNT(*)
--O FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
--O WHERE TABLE_SCHEMA = 'CTS1' AND TABLE_NAME = 'TA752A'
--O AND CONSTRAINT_NAME = 'TA752A_PRKEY' AND COLUMN_NAME = 'COL1';
-- PASS:7052 If COUNT = 1?
--O COMMIT WORK;
ALTER TABLE TAB752b
ADD CONSTRAINT TA752b_FKEY FOREIGN KEY(C2)
REFERENCES TAB752a(COL2);
0 rows inserted/updated/deleted
ij> -- PASS:7052 If table altered successfully?
--O COMMIT WORK;
--O SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
--O WHERE TABLE_SCHEMA = 'CTS1'
--O AND TABLE_NAME = 'TAB752B'
--O AND CONSTRAINT_NAME = 'TA752B_FKEY'
--O AND COLUMN_NAME = 'C2';
-- PASS:7052 If COUNT = 1?
--O COMMIT WORK;
ALTER TABLE TAB752a
ADD CONSTRAINT COL3_CHECK CHECK
(COL3 IN ('ATHENS','CORFU','PYLOS'));
0 rows inserted/updated/deleted
ij> -- PASS:7052 If table altered successfully?
COMMIT WORK;
ij> INSERT INTO TAB752a VALUES(1000,'KILLER','PAROS');
ERROR 23513: The check constraint 'COL3_CHECK' was violated while performing an INSERT or UPDATE on table '"CTS1"."TAB752A"'.
ij> -- PASS:7052 If ERROR - integrity constraint violation?
ROLLBACK WORK;
ij> --O DROP TABLE TAB752a CASCADE;
--
--HACK: we need to drop b before a since
-- we don't support cascade on drop table
--
-- PASS:7052 If table dropped successfully?
COMMIT WORK;
ij> --O DROP TABLE TAB752b CASCADE;
DROP TABLE TAB752b ;
0 rows inserted/updated/deleted
ij> DROP TABLE TAB752a ;
0 rows inserted/updated/deleted
ij> -- PASS:7052 If table dropped successfully?
COMMIT WORK;
ij> -- END TEST >>> 7052 <<< END TEST
-- *********************************************
-- *************************************************////END-OF-MODULE
;
ij>
|
