? src/test/regress/log ? src/test/regress/pg_regress ? src/test/regress/postgres.core ? src/test/regress/regression.diffs ? src/test/regress/regression.out ? src/test/regress/results ? src/test/regress/tmp_check ? src/test/regress/expected/bak.out ? src/test/regress/expected/constraints.out ? src/test/regress/expected/copy.out ? src/test/regress/expected/create_function_1.out ? src/test/regress/expected/create_function_2.out ? src/test/regress/expected/misc.out ? src/test/regress/sql/constraints.sql ? src/test/regress/sql/copy.sql ? src/test/regress/sql/create_function_1.sql ? src/test/regress/sql/create_function_2.sql ? src/test/regress/sql/misc.sql Index: src/test/regress/expected/alter_table.out =================================================================== RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/alter_table.out,v retrieving revision 1.32 diff -c -r1.32 alter_table.out *** src/test/regress/expected/alter_table.out 2002/03/06 06:10:52 1.32 --- src/test/regress/expected/alter_table.out 2002/03/17 08:11:44 *************** *** 473,479 **** ERROR: Cannot create unique index. Table contains non-unique values insert into atacc1 (test) values (3); drop table atacc1; ! -- let's do one where the unique contsraint fails -- because the column doesn't exist create table atacc1 ( test int ); -- add a unique constraint (fails) --- 473,479 ---- ERROR: Cannot create unique index. Table contains non-unique values insert into atacc1 (test) values (3); drop table atacc1; ! -- let's do one where the unique constraint fails -- because the column doesn't exist create table atacc1 ( test int ); -- add a unique constraint (fails) *************** *** 504,507 **** --- 504,580 ---- insert into atacc1 (test2, test) values (3, 3); insert into atacc1 (test2, test) values (2, 3); ERROR: Cannot insert a duplicate key into unique index atacc1_test_key + drop table atacc1; + -- test primary key constraint adding + create table atacc1 ( test int ); + -- add a primary key constraint + alter table atacc1 add constraint atacc_test1 primary key (test); + ERROR: Existing attribute "test" cannot be a PRIMARY KEY because it is not marked NOT NULL + -- insert first value + insert into atacc1 (test) values (2); + -- should fail + insert into atacc1 (test) values (2); + -- should succeed + insert into atacc1 (test) values (4); + -- inserting NULL should fail + insert into atacc1 (test) values(NULL); + -- try adding a primary key oid constraint + alter table atacc1 add constraint atacc_oid1 primary key(oid); + NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index 'atacc_oid1' for table 'atacc1' + drop table atacc1; + -- let's do one where the primary key constraint fails when added + create table atacc1 ( test int ); + -- insert soon to be failing rows + insert into atacc1 (test) values (2); + insert into atacc1 (test) values (2); + -- add a primary key (fails) + alter table atacc1 add constraint atacc_test1 primary key (test); + ERROR: Existing attribute "test" cannot be a PRIMARY KEY because it is not marked NOT NULL + insert into atacc1 (test) values (3); + drop table atacc1; + -- let's do another one where the primary key constraint fails when added + create table atacc1 ( test int ); + -- insert soon to be failing row + insert into atacc1 (test) values (NULL); + -- add a primary key (fails) + alter table atacc1 add constraint atacc_test1 primary key (test); + ERROR: Existing attribute "test" cannot be a PRIMARY KEY because it is not marked NOT NULL + insert into atacc1 (test) values (3); + drop table atacc1; + -- let's do one where the primary key constraint fails + -- because the column doesn't exist + create table atacc1 ( test int ); + -- add a primary key constraint (fails) + alter table atacc1 add constraint atacc_test1 primary key (test1); + ERROR: ALTER TABLE: column "test1" named in key does not exist + drop table atacc1; + -- something a little more complicated + create table atacc1 ( test int, test2 int); + -- add a primary key constraint + alter table atacc1 add constraint atacc_test1 primary key (test, test2); + ERROR: Existing attribute "test" cannot be a PRIMARY KEY because it is not marked NOT NULL + -- try adding a second primary key - should fail + alter table atacc1 add constraint atacc_test2 primary key (test); + ERROR: Existing attribute "test" cannot be a PRIMARY KEY because it is not marked NOT NULL + -- insert initial value + insert into atacc1 (test,test2) values (4,4); + -- should fail + insert into atacc1 (test,test2) values (4,4); + insert into atacc1 (test,test2) values (NULL,3); + insert into atacc1 (test,test2) values (3, NULL); + insert into atacc1 (test,test2) values (NULL,NULL); + -- should all succeed + insert into atacc1 (test,test2) values (4,5); + insert into atacc1 (test,test2) values (5,4); + insert into atacc1 (test,test2) values (5,5); + drop table atacc1; + -- lets do some naming tests + create table atacc1 (test int, test2 int, primary key(test)); + NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'atacc1_pkey' for table 'atacc1' + -- only first should succeed + insert into atacc1 (test2, test) values (3, 3); + insert into atacc1 (test2, test) values (2, 3); + ERROR: Cannot insert a duplicate key into unique index atacc1_pkey + insert into atacc1 (test2, test) values (1, NULL); + ERROR: ExecAppend: Fail to add null value in not null attribute test drop table atacc1; Index: src/test/regress/sql/alter_table.sql =================================================================== RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/alter_table.sql,v retrieving revision 1.20 diff -c -r1.20 alter_table.sql *** src/test/regress/sql/alter_table.sql 2002/03/04 05:17:54 1.20 --- src/test/regress/sql/alter_table.sql 2002/03/17 08:11:45 *************** *** 354,360 **** insert into atacc1 (test) values (3); drop table atacc1; ! -- let's do one where the unique contsraint fails -- because the column doesn't exist create table atacc1 ( test int ); -- add a unique constraint (fails) --- 354,360 ---- insert into atacc1 (test) values (3); drop table atacc1; ! -- let's do one where the unique constraint fails -- because the column doesn't exist create table atacc1 ( test int ); -- add a unique constraint (fails) *************** *** 381,384 **** --- 381,454 ---- -- should fail for @@ second one @@ insert into atacc1 (test2, test) values (3, 3); insert into atacc1 (test2, test) values (2, 3); + drop table atacc1; + + -- test primary key constraint adding + + create table atacc1 ( test int ); + -- add a primary key constraint + alter table atacc1 add constraint atacc_test1 primary key (test); + -- insert first value + insert into atacc1 (test) values (2); + -- should fail + insert into atacc1 (test) values (2); + -- should succeed + insert into atacc1 (test) values (4); + -- inserting NULL should fail + insert into atacc1 (test) values(NULL); + -- try adding a primary key oid constraint + alter table atacc1 add constraint atacc_oid1 primary key(oid); + drop table atacc1; + + -- let's do one where the primary key constraint fails when added + create table atacc1 ( test int ); + -- insert soon to be failing rows + insert into atacc1 (test) values (2); + insert into atacc1 (test) values (2); + -- add a primary key (fails) + alter table atacc1 add constraint atacc_test1 primary key (test); + insert into atacc1 (test) values (3); + drop table atacc1; + + -- let's do another one where the primary key constraint fails when added + create table atacc1 ( test int ); + -- insert soon to be failing row + insert into atacc1 (test) values (NULL); + -- add a primary key (fails) + alter table atacc1 add constraint atacc_test1 primary key (test); + insert into atacc1 (test) values (3); + drop table atacc1; + + -- let's do one where the primary key constraint fails + -- because the column doesn't exist + create table atacc1 ( test int ); + -- add a primary key constraint (fails) + alter table atacc1 add constraint atacc_test1 primary key (test1); + drop table atacc1; + + -- something a little more complicated + create table atacc1 ( test int, test2 int); + -- add a primary key constraint + alter table atacc1 add constraint atacc_test1 primary key (test, test2); + -- try adding a second primary key - should fail + alter table atacc1 add constraint atacc_test2 primary key (test); + -- insert initial value + insert into atacc1 (test,test2) values (4,4); + -- should fail + insert into atacc1 (test,test2) values (4,4); + insert into atacc1 (test,test2) values (NULL,3); + insert into atacc1 (test,test2) values (3, NULL); + insert into atacc1 (test,test2) values (NULL,NULL); + -- should all succeed + insert into atacc1 (test,test2) values (4,5); + insert into atacc1 (test,test2) values (5,4); + insert into atacc1 (test,test2) values (5,5); + drop table atacc1; + + -- lets do some naming tests + create table atacc1 (test int, test2 int, primary key(test)); + -- only first should succeed + insert into atacc1 (test2, test) values (3, 3); + insert into atacc1 (test2, test) values (2, 3); + insert into atacc1 (test2, test) values (1, NULL); drop table atacc1;