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>