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

Commit981a7d3

Browse files
committed
From Stephan Szabo:
I believe this should fix the issue that Philip Warnernoticed about the check for unique constraints meeting thereferenced keys of a foreign key constraint allowing thespecification of a subset of a foreign key instead ofrejecting it. I also added tests for a base case ofthis to the foreign key and alter table tests and patchesfor expected output.
1 parent5ce8ab9 commit981a7d3

File tree

6 files changed

+98
-39
lines changed

6 files changed

+98
-39
lines changed

‎src/backend/commands/command.c

Lines changed: 22 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/commands/Attic/command.c,v 1.112 2000/11/16 22:30:19 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/commands/Attic/command.c,v 1.113 2000/12/05 19:57:55 tgl Exp $
1212
*
1313
* NOTES
1414
* The PerformAddAttribute() code, like most of the relation
@@ -1287,26 +1287,33 @@ AlterTableAddConstraint(char *relationName,
12871287
{
12881288
List*attrl;
12891289

1290-
/* go through the fkconstraint->pk_attrs list */
1291-
foreach(attrl,fkconstraint->pk_attrs)
1292-
{
1293-
Ident*attr=lfirst(attrl);
1294-
found= false;
1295-
for (i=0;i<INDEX_MAX_KEYS&&indexStruct->indkey[i]!=0;i++)
1290+
/* Make sure this index has the same number of keys -- It obviously
1291+
* won't match otherwise. */
1292+
for (i=0;i<INDEX_MAX_KEYS&&indexStruct->indkey[i]!=0;i++);
1293+
if (i!=length(fkconstraint->pk_attrs))
1294+
found=false;
1295+
else {
1296+
/* go through the fkconstraint->pk_attrs list */
1297+
foreach(attrl,fkconstraint->pk_attrs)
12961298
{
1297-
intpkattno=indexStruct->indkey[i];
1298-
if (pkattno>0)
1299+
Ident*attr=lfirst(attrl);
1300+
found= false;
1301+
for (i=0;i<INDEX_MAX_KEYS&&indexStruct->indkey[i]!=0;i++)
12991302
{
1300-
char*name=NameStr(rel_attrs[pkattno-1]->attname);
1301-
if (strcmp(name,attr->name)==0)
1303+
intpkattno=indexStruct->indkey[i];
1304+
if (pkattno>0)
13021305
{
1303-
found= true;
1304-
break;
1306+
char*name=NameStr(rel_attrs[pkattno-1]->attname);
1307+
if (strcmp(name,attr->name)==0)
1308+
{
1309+
found= true;
1310+
break;
1311+
}
13051312
}
13061313
}
1314+
if (!found)
1315+
break;
13071316
}
1308-
if (!found)
1309-
break;
13101317
}
13111318
}
13121319
ReleaseSysCache(indexTuple);

‎src/backend/parser/analyze.c

Lines changed: 37 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Portions Copyright (c) 1996-2000, PostgreSQL, Inc
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
9-
*$Id: analyze.c,v 1.169 2000/12/05 19:15:10 tgl Exp $
9+
*$Id: analyze.c,v 1.170 2000/12/05 19:57:55 tgl Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -1209,18 +1209,26 @@ transformCreateStmt(ParseState *pstate, CreateStmt *stmt)
12091209
List*pkattrs;
12101210
Ident*pkattr;
12111211
if (ind->unique) {
1212-
foreach(pkattrs,fkconstraint->pk_attrs) {
1212+
intcount=0;
1213+
foreach(indparms,ind->indexParams) {
1214+
count++;
1215+
}
1216+
if (count!=length(fkconstraint->pk_attrs))
12131217
found=0;
1214-
pkattr=lfirst(pkattrs);
1215-
foreach(indparms,ind->indexParams) {
1216-
indparm=lfirst(indparms);
1217-
if (strcmp(indparm->name,pkattr->name)==0) {
1218-
found=1;
1219-
break;
1218+
else {
1219+
foreach(pkattrs,fkconstraint->pk_attrs) {
1220+
found=0;
1221+
pkattr=lfirst(pkattrs);
1222+
foreach(indparms,ind->indexParams) {
1223+
indparm=lfirst(indparms);
1224+
if (strcmp(indparm->name,pkattr->name)==0) {
1225+
found=1;
1226+
break;
1227+
}
12201228
}
1229+
if (!found)
1230+
break;
12211231
}
1222-
if (!found)
1223-
break;
12241232
}
12251233
}
12261234
if (found)
@@ -2634,26 +2642,31 @@ transformFkeyCheckAttrs(FkConstraint *fkconstraint)
26342642
{
26352643
List*attrl;
26362644

2637-
/* go through the fkconstraint->pk_attrs list */
2638-
foreach(attrl,fkconstraint->pk_attrs)
2639-
{
2640-
Ident*attr=lfirst(attrl);
2641-
found= false;
2642-
for (i=0;i<INDEX_MAX_KEYS&&indexStruct->indkey[i]!=0;i++)
2645+
for (i=0;i<INDEX_MAX_KEYS&&indexStruct->indkey[i]!=0;i++);
2646+
if (i!=length(fkconstraint->pk_attrs))
2647+
found=false;
2648+
else {
2649+
/* go through the fkconstraint->pk_attrs list */
2650+
foreach(attrl,fkconstraint->pk_attrs)
26432651
{
2644-
intpkattno=indexStruct->indkey[i];
2645-
if (pkattno>0)
2652+
Ident*attr=lfirst(attrl);
2653+
found= false;
2654+
for (i=0;i<INDEX_MAX_KEYS&&indexStruct->indkey[i]!=0;i++)
26462655
{
2647-
char*name=NameStr(pkrel_attrs[pkattno-1]->attname);
2648-
if (strcmp(name,attr->name)==0)
2656+
intpkattno=indexStruct->indkey[i];
2657+
if (pkattno>0)
26492658
{
2650-
found= true;
2651-
break;
2659+
char*name=NameStr(pkrel_attrs[pkattno-1]->attname);
2660+
if (strcmp(name,attr->name)==0)
2661+
{
2662+
found= true;
2663+
break;
2664+
}
26522665
}
26532666
}
2667+
if (!found)
2668+
break;
26542669
}
2655-
if (!found)
2656-
break;
26572670
}
26582671
}
26592672
ReleaseSysCache(indexTuple);

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

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -273,6 +273,9 @@ SELECT unique1 FROM tenk1 WHERE unique1 < 5;
273273
CREATE TABLE tmp2 (a int primary key);
274274
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'tmp2_pkey' for table 'tmp2'
275275
CREATE TABLE tmp3 (a int, b int);
276+
CREATE TABLE tmp4 (a int, b int, unique(a,b));
277+
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'tmp4_a_key' for table 'tmp4'
278+
CREATE TABLE tmp5 (a int, b int);
276279
-- Insert rows into tmp2 (pktable)
277280
INSERT INTO tmp2 values (1);
278281
INSERT INTO tmp2 values (2);
@@ -299,6 +302,13 @@ DELETE FROM tmp3 where a=5;
299302
-- Try (and succeed)
300303
ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
301304
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s)
305+
-- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on
306+
-- tmp4 is a,b
307+
ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full;
308+
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s)
309+
ERROR: UNIQUE constraint matching given keys for referenced table "tmp4" not found
310+
DROP TABLE tmp5;
311+
DROP TABLE tmp4;
302312
DROP TABLE tmp3;
303313
NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "tmp2"
304314
NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "tmp2"

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

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -703,3 +703,12 @@ ERROR: table "fktable_fail1" does not exist
703703
DROP TABLE FKTABLE_FAIL2;
704704
ERROR: table "fktable_fail2" does not exist
705705
DROP TABLE PKTABLE;
706+
-- Test for referencing column number smaller than referenced constraint
707+
CREATE TABLE PKTABLE (ptest1 int, ptest2 int, UNIQUE(ptest1, ptest2));
708+
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'pktable_ptest1_key' for table 'pktable'
709+
CREATE TABLE FKTABLE_FAIL1 (ftest1 int REFERENCES pktable(ptest1));
710+
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
711+
ERROR: UNIQUE constraint matching given keys for referenced table "pktable" not found
712+
DROP TABLE FKTABLE_FAIL1;
713+
ERROR: table "fktable_fail1" does not exist
714+
DROP TABLE PKTABLE;

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

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -169,6 +169,10 @@ CREATE TABLE tmp2 (a int primary key);
169169

170170
CREATETABLEtmp3 (aint, bint);
171171

172+
CREATETABLEtmp4 (aint, bint, unique(a,b));
173+
174+
CREATETABLEtmp5 (aint, bint);
175+
172176
-- Insert rows into tmp2 (pktable)
173177
INSERT INTO tmp2values (1);
174178
INSERT INTO tmp2values (2);
@@ -195,6 +199,15 @@ DELETE FROM tmp3 where a=5;
195199
-- Try (and succeed)
196200
ALTERTABLE tmp3 addconstraint tmpconstrforeign key (a)references tmp2 match full;
197201

202+
-- Try (and fail) to create constraint from tmp5(a) to tmp4(a) - unique constraint on
203+
-- tmp4 is a,b
204+
205+
ALTERTABLE tmp5 addconstraint tmpconstrforeign key(a)references tmp4(a) match full;
206+
207+
DROPTABLE tmp5;
208+
209+
DROPTABLE tmp4;
210+
198211
DROPTABLE tmp3;
199212

200213
DROPTABLE tmp2;

‎src/test/regress/sql/foreign_key.sql

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -418,3 +418,10 @@ CREATE TABLE FKTABLE_FAIL2 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest1)
418418
DROPTABLE FKTABLE_FAIL1;
419419
DROPTABLE FKTABLE_FAIL2;
420420
DROPTABLE PKTABLE;
421+
422+
-- Test for referencing column number smaller than referenced constraint
423+
CREATETABLEPKTABLE (ptest1int, ptest2int, UNIQUE(ptest1, ptest2));
424+
CREATETABLEFKTABLE_FAIL1 (ftest1intREFERENCES pktable(ptest1));
425+
426+
DROPTABLE FKTABLE_FAIL1;
427+
DROPTABLE PKTABLE;

0 commit comments

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