From b292cafcd47d1242df4f68d7182a17de02eb668f Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Tue, 19 Mar 2002 12:59:08 +0000 Subject: [PATCH] Just adds a regressions test suite for the ALTER TABLE/ADD PRIMARY KEY feature. I'll do ALTER TABLE / DROP CONSTRAINT next... Christopher Kings-Lynne --- src/test/regress/expected/alter_table.out | 75 ++++++++++++++++++++++- src/test/regress/sql/alter_table.sql | 72 +++++++++++++++++++++- 2 files changed, 145 insertions(+), 2 deletions(-) diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 7d6209c3e8..0e01eeaef5 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -473,7 +473,7 @@ NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index 'atacc_test1' for t 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 +-- 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) @@ -505,3 +505,76 @@ 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; diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 5602bacc7f..36d9be96d2 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -354,7 +354,7 @@ alter table atacc1 add constraint atacc_test1 unique (test); insert into atacc1 (test) values (3); drop table atacc1; --- let's do one where the unique contsraint fails +-- 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) @@ -382,3 +382,73 @@ alter table atacc1 add unique (test2); 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; -- 2.40.0