Skip to content

Commit 981a7d3

Browse files
tglsfdctglsfdc
committed
From Stephan Szabo:
I believe this should fix the issue that Philip Warner noticed about the check for unique constraints meeting the referenced keys of a foreign key constraint allowing the specification of a subset of a foreign key instead of rejecting it. I also added tests for a base case of this to the foreign key and alter table tests and patches for expected output.
1 parent 5ce8ab9 commit 981a7d3

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-
int pkattno = 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+
int pkattno = 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+
int count=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-
int pkattno = 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+
int pkattno = 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
CREATE TABLE tmp3 (a int, b int);
171171

172+
CREATE TABLE tmp4 (a int, b int, unique(a,b));
173+
174+
CREATE TABLE tmp5 (a int, b int);
175+
172176
-- Insert rows into tmp2 (pktable)
173177
INSERT INTO tmp2 values (1);
174178
INSERT INTO tmp2 values (2);
@@ -195,6 +199,15 @@ DELETE FROM tmp3 where a=5;
195199
-- Try (and succeed)
196200
ALTER TABLE tmp3 add constraint tmpconstr foreign 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+
ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a) match full;
206+
207+
DROP TABLE tmp5;
208+
209+
DROP TABLE tmp4;
210+
198211
DROP TABLE tmp3;
199212

200213
DROP TABLE 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
DROP TABLE FKTABLE_FAIL1;
419419
DROP TABLE FKTABLE_FAIL2;
420420
DROP TABLE PKTABLE;
421+
422+
-- Test for referencing column number smaller than referenced constraint
423+
CREATE TABLE PKTABLE (ptest1 int, ptest2 int, UNIQUE(ptest1, ptest2));
424+
CREATE TABLE FKTABLE_FAIL1 (ftest1 int REFERENCES pktable(ptest1));
425+
426+
DROP TABLE FKTABLE_FAIL1;
427+
DROP TABLE PKTABLE;

0 commit comments

Comments
 (0)