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
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
|
ij> --
-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements. See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License"); you may not use this file except in compliance with
-- the License. You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
AUTOCOMMIT OFF;
ij> -- MODULE CDR030
-- SQL Test Suite, V6.0, Interactive SQL, cdr030.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION SUN
set schema SUN;
0 rows inserted/updated/deleted
ij> --O SELECT USER FROM HU.ECCO;
VALUES USER;
1
--------------------------------------------------------------------------------------------------------------------------------
SUN
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
-- date_time print
-- NOTE Direct support for SQLCODE or SQLSTATE is not required
-- NOTE in Interactive Direct SQL, as defined in FIPS 127-2.
-- NOTE ********************* instead ***************************
-- NOTE If a statement raises an exception condition,
-- NOTE then the system shall display a message indicating that
-- NOTE the statement failed, giving a textual description
-- NOTE of the failure.
-- NOTE If a statement raises a completion condition that is a
-- NOTE "warning" or "no data", then the system shall display
-- NOTE a message indicating that the statement completed,
-- NOTE giving a textual description of the "warning" or "no data."
-- TEST:0516 SQLSTATE 23502: integrity constraint violation!
--O INSERT INTO EMP
--O VALUES (41,'Tom','China Architecture',
--O 20,'Architecture',040553);
-- PASS:0516 If ERROR, integrity constraint violation, 0 rows inserted?
-- PASS:0516 OR RI ERROR, parent missing, 0 rows inserted?
-- PASS:0516 OR SQLSTATE = 23502 OR SQLCODE < 0?
--O DELETE FROM EMP
--O WHERE ENO = 21;
-- PASS:0516 If ERROR, integrity constraint violation, 0 rows deleted?
-- PASS:0516 OR RI ERROR, children exist, 0 rows deleted?
-- PASS:0516 OR SQLSTATE = 23502 OR SQLCODE < 0?
--O UPDATE EMP
--O SET ENAME = 'Thomas'
--O WHERE ENO = 21;
-- PASS:0516 If ERROR, integrity constraint violation, 0 rows updated?
-- PASS:0516 OR RI ERROR, chldren exist, 0 rows updated?
-- PASS:0516 OR SQLSTATE = 23502 OR SQLCODE < 0?
-- setup
DELETE FROM STAFF7;
1 row inserted/updated/deleted
ij> -- PRIMARY KEY (EMPNUM)
INSERT INTO STAFF7 (EMPNUM)
VALUES ('XXX');
1 row inserted/updated/deleted
ij> -- PASS:0516 If 1 row inserted?
INSERT INTO STAFF7 (EMPNUM)
VALUES ('XXX');
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'STAFF7_PK' defined on 'STAFF7'.
ij> -- PASS:0516 If ERROR, integrity constraint violation, 0 rows inserted?
-- PASS:0516 OR ERROR, unique constraint, 0 rows inserted?
-- PASS:0516 OR SQLSTATE = 23502 OR SQLCODE < 0?
-- setup
DELETE FROM PROJ3;
0 rows inserted/updated/deleted
WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table.
ij> -- UNIQUE (PNUM)
INSERT INTO PROJ3 (PNUM) VALUES ('787');
1 row inserted/updated/deleted
ij> INSERT INTO PROJ3 (PNUM) VALUES ('789');
1 row inserted/updated/deleted
ij> -- PASS:0516 If 1 row inserted?
UPDATE PROJ3 SET PNUM = '787'
WHERE PNUM = '789';
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'PROJ3_UNIQUE' defined on 'PROJ3'.
ij> -- PASS:0516 If ERROR, integrity constraint violation, 0 rows updated?
-- PASS:0516 OR ERROR, unique constraint, 0 rows updated?
-- PASS:0516 OR SQLSTATE = 23502 OR SQLCODE < 0?
-- setup
DELETE FROM STAFF11;
1 row inserted/updated/deleted
ij> INSERT INTO STAFF11
VALUES('E3','Susan',11,'Hawaii');
1 row inserted/updated/deleted
ij> -- PASS:0516 If 1 row inserted?
-- (CHECK GRADE NOT IN (5,22))
UPDATE STAFF11
SET GRADE = 5
WHERE EMPNUM = 'E3';
ERROR 23513: The check constraint 'STAFF11_GRADE_EMPNAME' was violated while performing an INSERT or UPDATE on table '"SUN"."STAFF11"'.
ij> -- PASS:0516 If ERROR, integrity constraint violation, 0 rows updated?
-- PASS:0516 OR ERROR, check constraint, 0 rows updated?
-- PASS:0516 OR SQLSTATE = 23502 OR SQLCODE < 0?
-- (CHECK NOT EMPNAME LIKE 'T%')
UPDATE STAFF11
SET EMPNAME = 'Tom'
WHERE EMPNUM = 'E3';
ERROR 23513: The check constraint 'STAFF11_GRADE_EMPNAME' was violated while performing an INSERT or UPDATE on table '"SUN"."STAFF11"'.
ij> -- PASS:0516 If ERROR, integrity constraint violation, 0 rows updated?
-- PASS:0516 OR ERROR, check constraint, 0 rows updated?
-- PASS:0516 OR SQLSTATE = 23502 OR SQLCODE < 0?
-- restore
ROLLBACK WORK;
ij> -- END TEST >>> 0516 <<< END TEST
-- *************************************************////END-OF-MODULE
;
ij>
|
