| 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> |