Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb292caf

Browse files
committed
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
1 parentf5810bb commitb292caf

File tree

2 files changed

+145
-2
lines changed

2 files changed

+145
-2
lines changed

‎src/test/regress/expected/alter_table.out

Lines changed: 74 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -473,7 +473,7 @@ NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index 'atacc_test1' for t
473473
ERROR: Cannot create unique index. Table contains non-unique values
474474
insert into atacc1 (test) values (3);
475475
drop table atacc1;
476-
-- let's do one where the uniquecontsraint fails
476+
-- let's do one where the uniqueconstraint fails
477477
-- because the column doesn't exist
478478
create table atacc1 ( test int );
479479
-- add a unique constraint (fails)
@@ -505,3 +505,76 @@ insert into atacc1 (test2, test) values (3, 3);
505505
insert into atacc1 (test2, test) values (2, 3);
506506
ERROR: Cannot insert a duplicate key into unique index atacc1_test_key
507507
drop table atacc1;
508+
-- test primary key constraint adding
509+
create table atacc1 ( test int );
510+
-- add a primary key constraint
511+
alter table atacc1 add constraint atacc_test1 primary key (test);
512+
ERROR: Existing attribute "test" cannot be a PRIMARY KEY because it is not marked NOT NULL
513+
-- insert first value
514+
insert into atacc1 (test) values (2);
515+
-- should fail
516+
insert into atacc1 (test) values (2);
517+
-- should succeed
518+
insert into atacc1 (test) values (4);
519+
-- inserting NULL should fail
520+
insert into atacc1 (test) values(NULL);
521+
-- try adding a primary key oid constraint
522+
alter table atacc1 add constraint atacc_oid1 primary key(oid);
523+
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index 'atacc_oid1' for table 'atacc1'
524+
drop table atacc1;
525+
-- let's do one where the primary key constraint fails when added
526+
create table atacc1 ( test int );
527+
-- insert soon to be failing rows
528+
insert into atacc1 (test) values (2);
529+
insert into atacc1 (test) values (2);
530+
-- add a primary key (fails)
531+
alter table atacc1 add constraint atacc_test1 primary key (test);
532+
ERROR: Existing attribute "test" cannot be a PRIMARY KEY because it is not marked NOT NULL
533+
insert into atacc1 (test) values (3);
534+
drop table atacc1;
535+
-- let's do another one where the primary key constraint fails when added
536+
create table atacc1 ( test int );
537+
-- insert soon to be failing row
538+
insert into atacc1 (test) values (NULL);
539+
-- add a primary key (fails)
540+
alter table atacc1 add constraint atacc_test1 primary key (test);
541+
ERROR: Existing attribute "test" cannot be a PRIMARY KEY because it is not marked NOT NULL
542+
insert into atacc1 (test) values (3);
543+
drop table atacc1;
544+
-- let's do one where the primary key constraint fails
545+
-- because the column doesn't exist
546+
create table atacc1 ( test int );
547+
-- add a primary key constraint (fails)
548+
alter table atacc1 add constraint atacc_test1 primary key (test1);
549+
ERROR: ALTER TABLE: column "test1" named in key does not exist
550+
drop table atacc1;
551+
-- something a little more complicated
552+
create table atacc1 ( test int, test2 int);
553+
-- add a primary key constraint
554+
alter table atacc1 add constraint atacc_test1 primary key (test, test2);
555+
ERROR: Existing attribute "test" cannot be a PRIMARY KEY because it is not marked NOT NULL
556+
-- try adding a second primary key - should fail
557+
alter table atacc1 add constraint atacc_test2 primary key (test);
558+
ERROR: Existing attribute "test" cannot be a PRIMARY KEY because it is not marked NOT NULL
559+
-- insert initial value
560+
insert into atacc1 (test,test2) values (4,4);
561+
-- should fail
562+
insert into atacc1 (test,test2) values (4,4);
563+
insert into atacc1 (test,test2) values (NULL,3);
564+
insert into atacc1 (test,test2) values (3, NULL);
565+
insert into atacc1 (test,test2) values (NULL,NULL);
566+
-- should all succeed
567+
insert into atacc1 (test,test2) values (4,5);
568+
insert into atacc1 (test,test2) values (5,4);
569+
insert into atacc1 (test,test2) values (5,5);
570+
drop table atacc1;
571+
-- lets do some naming tests
572+
create table atacc1 (test int, test2 int, primary key(test));
573+
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'atacc1_pkey' for table 'atacc1'
574+
-- only first should succeed
575+
insert into atacc1 (test2, test) values (3, 3);
576+
insert into atacc1 (test2, test) values (2, 3);
577+
ERROR: Cannot insert a duplicate key into unique index atacc1_pkey
578+
insert into atacc1 (test2, test) values (1, NULL);
579+
ERROR: ExecAppend: Fail to add null value in not null attribute test
580+
drop table atacc1;

‎src/test/regress/sql/alter_table.sql

Lines changed: 71 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -354,7 +354,7 @@ alter table atacc1 add constraint atacc_test1 unique (test);
354354
insert into atacc1 (test)values (3);
355355
droptable atacc1;
356356

357-
-- let's do one where the uniquecontsraint fails
357+
-- let's do one where the uniqueconstraint fails
358358
-- because the column doesn't exist
359359
createtableatacc1 ( testint );
360360
-- add a unique constraint (fails)
@@ -382,3 +382,73 @@ alter table atacc1 add unique (test2);
382382
insert into atacc1 (test2, test)values (3,3);
383383
insert into atacc1 (test2, test)values (2,3);
384384
droptable atacc1;
385+
386+
-- test primary key constraint adding
387+
388+
createtableatacc1 ( testint );
389+
-- add a primary key constraint
390+
altertable atacc1 addconstraint atacc_test1primary key (test);
391+
-- insert first value
392+
insert into atacc1 (test)values (2);
393+
-- should fail
394+
insert into atacc1 (test)values (2);
395+
-- should succeed
396+
insert into atacc1 (test)values (4);
397+
-- inserting NULL should fail
398+
insert into atacc1 (test)values(NULL);
399+
-- try adding a primary key oid constraint
400+
altertable atacc1 addconstraint atacc_oid1primary key(oid);
401+
droptable atacc1;
402+
403+
-- let's do one where the primary key constraint fails when added
404+
createtableatacc1 ( testint );
405+
-- insert soon to be failing rows
406+
insert into atacc1 (test)values (2);
407+
insert into atacc1 (test)values (2);
408+
-- add a primary key (fails)
409+
altertable atacc1 addconstraint atacc_test1primary key (test);
410+
insert into atacc1 (test)values (3);
411+
droptable atacc1;
412+
413+
-- let's do another one where the primary key constraint fails when added
414+
createtableatacc1 ( testint );
415+
-- insert soon to be failing row
416+
insert into atacc1 (test)values (NULL);
417+
-- add a primary key (fails)
418+
altertable atacc1 addconstraint atacc_test1primary key (test);
419+
insert into atacc1 (test)values (3);
420+
droptable atacc1;
421+
422+
-- let's do one where the primary key constraint fails
423+
-- because the column doesn't exist
424+
createtableatacc1 ( testint );
425+
-- add a primary key constraint (fails)
426+
altertable atacc1 addconstraint atacc_test1primary key (test1);
427+
droptable atacc1;
428+
429+
-- something a little more complicated
430+
createtableatacc1 ( testint, test2int);
431+
-- add a primary key constraint
432+
altertable atacc1 addconstraint atacc_test1primary key (test, test2);
433+
-- try adding a second primary key - should fail
434+
altertable atacc1 addconstraint atacc_test2primary key (test);
435+
-- insert initial value
436+
insert into atacc1 (test,test2)values (4,4);
437+
-- should fail
438+
insert into atacc1 (test,test2)values (4,4);
439+
insert into atacc1 (test,test2)values (NULL,3);
440+
insert into atacc1 (test,test2)values (3,NULL);
441+
insert into atacc1 (test,test2)values (NULL,NULL);
442+
-- should all succeed
443+
insert into atacc1 (test,test2)values (4,5);
444+
insert into atacc1 (test,test2)values (5,4);
445+
insert into atacc1 (test,test2)values (5,5);
446+
droptable atacc1;
447+
448+
-- lets do some naming tests
449+
createtableatacc1 (testint, test2int,primary key(test));
450+
-- only first should succeed
451+
insert into atacc1 (test2, test)values (3,3);
452+
insert into atacc1 (test2, test)values (2,3);
453+
insert into atacc1 (test2, test)values (1,NULL);
454+
droptable atacc1;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp