ij> AUTOCOMMIT OFF;
ij> -- MODULE  DML168  

-- SQL Test Suite, V6.0, Interactive SQL, dml168.sql
-- 59-byte ID
-- TEd Version #

-- AUTHORIZATION FLATER
   set schema FLATER;
0 rows inserted/updated/deleted
ij> --O   SELECT USER FROM HU.ECCO;
  VALUES USER;
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
FLATER                                                                                                                          
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
--O   ROLLBACK WORK;

-- date_time print

-- TEST:0876 SQL_IDENTIFIER and CHARACTER_DATA domains!

--O   CREATE TABLE T0876 (
--O     C1 INFORMATION_SCHEMA.SQL_IDENTIFIER,
--O     C2 INFORMATION_SCHEMA.CHARACTER_DATA);
-- PASS:0876 If table created successfully?

--O   COMMIT WORK;

--O   INSERT INTO T0876 VALUES ('T0876',
--O     'This table tests a couple of domains.');
-- PASS:0876 If 1 row inserted successfully?

--O   SELECT COUNT(*) 
--O     FROM T0876
--O     WHERE C1 = 'T0876';
-- PASS:0876 If COUNT = 1?

--O   COMMIT WORK;

--O   DROP TABLE T0876 CASCADE;
-- PASS:0876 If table dropped successfully?

--O   COMMIT WORK;

-- END TEST >>> 0876 <<< END TEST
-- *********************************************

-- TEST:0878 Keyword COLUMN in ALTER TABLE is optional!

   CREATE TABLE T0878 (C1 INT);
0 rows inserted/updated/deleted
ij> -- PASS:0878 If table created successfully?

   COMMIT WORK;
ij> ALTER TABLE T0878 ADD C2 CHAR (4);
0 rows inserted/updated/deleted
ij> -- PASS:0878 If table altered successfully?

   COMMIT WORK;
ij> --O   ALTER TABLE T0878
--O     ALTER C2 SET DEFAULT 'ABCD';
-- PASS:0878 If table altered successfully?

--O   COMMIT WORK;

--O   ALTER TABLE T0878
--O     DROP C1 CASCADE;
-- PASS:0878 If table altered successfully?

--O   COMMIT WORK;

--O   INSERT INTO T0878 VALUES (DEFAULT);
-- PASS:0878 If 1 row inserted successfully?

--O   SELECT * FROM T0878;
-- PASS:0878 If answer = 'ABCD'?

--O   COMMIT WORK;

--O  DROP TABLE T0878 CASCADE;
  DROP TABLE T0878 ;
0 rows inserted/updated/deleted
ij> -- PASS:0878 If table dropped successfully?

   COMMIT WORK;
ij> -- END TEST >>> 0878 <<< END TEST
-- *********************************************

-- TEST:0879 <drop table constraint definition>!

   CREATE TABLE T0879 (
     C1 INT,
     C2 INT NOT NULL,
       CONSTRAINT DELME CHECK (C1 > 0),
       CONSTRAINT REFME UNIQUE (C2));
0 rows inserted/updated/deleted
ij> -- PASS:0879 If table created successfully?

   COMMIT WORK;
ij> --O   CREATE TABLE U0879 (
--O     C1 INT REFERENCES T0879 (C2));
-- PASS:0879 If table created successfully?

--O   COMMIT WORK;

   ALTER TABLE T0879
--O     DROP CONSTRAINT DELME RESTRICT;
     DROP CONSTRAINT DELME ;
0 rows inserted/updated/deleted
ij> -- PASS:0879 If table altered successfully?

   COMMIT WORK;
ij> INSERT INTO T0879 VALUES (0, 0);
1 row inserted/updated/deleted
ij> -- PASS:0879 If 1 row inserted successfully?

   INSERT INTO T0879 VALUES (-1, -1);
1 row inserted/updated/deleted
ij> -- PASS:0879 If 1 row inserted successfully?

   SELECT COUNT(*) FROM T0879;
1          
-----------
2          
ij> -- PASS:0879 If COUNT = 2?

--O   INSERT INTO U0879 VALUES (20);
-- PASS:0879 If ERROR - integrity constraint violation?

   INSERT INTO T0879 VALUES (2, 0);
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 'REFME' defined on 'T0879'.
ij> -- PASS:0879 If ERROR - integrity constraint violation?

   COMMIT WORK;
ij> --O   ALTER TABLE T0879
--O     DROP CONSTRAINT REFME RESTRICT;
-- PASS:0879 If ERROR - syntax error or access rule violation?

--O   COMMIT WORK;

   ALTER TABLE T0879
--O     DROP CONSTRAINT REFME CASCADE;
     DROP CONSTRAINT REFME ;
0 rows inserted/updated/deleted
ij> -- PASS:0879 If table altered successfully?

   COMMIT WORK;
ij> --O   INSERT INTO U0879 VALUES (20);
-- PASS:0879 If 1 row inserted successfully?

   INSERT INTO T0879 VALUES (0, 0);
1 row inserted/updated/deleted
ij> -- PASS:0879 If 1 row inserted successfully?

   COMMIT WORK;
ij> --O   DROP TABLE T0879 CASCADE;
   DROP TABLE T0879 ;
0 rows inserted/updated/deleted
ij> -- PASS:0879 If table dropped successfully?

   COMMIT WORK;
ij> --O   DROP TABLE U0879 CASCADE;
-- PASS:0879 If table dropped successfully?

--O   COMMIT WORK;

-- END TEST >>> 0879 <<< END TEST
-- *********************************************
-- *************************************************////END-OF-MODULE
;
ij>