aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/commands/command.c95
-rw-r--r--src/backend/parser/analyze.c151
-rw-r--r--src/test/regress/expected/alter_table.out8
-rw-r--r--src/test/regress/expected/foreign_key.out13
-rw-r--r--src/test/regress/sql/alter_table.sql6
-rw-r--r--src/test/regress/sql/foreign_key.sql6
6 files changed, 272 insertions, 7 deletions
diff --git a/src/backend/commands/command.c b/src/backend/commands/command.c
index 60405ebd277..97b3563d9fc 100644
--- a/src/backend/commands/command.c
+++ b/src/backend/commands/command.c
@@ -8,7 +8,7 @@
*
*
* IDENTIFICATION
- * $Header: /cvsroot/pgsql/src/backend/commands/Attic/command.c,v 1.96 2000/08/25 18:05:54 tgl Exp $
+ * $Header: /cvsroot/pgsql/src/backend/commands/Attic/command.c,v 1.97 2000/08/29 04:20:43 momjian Exp $
*
* NOTES
* The PerformAddAttribute() code, like most of the relation
@@ -45,9 +45,10 @@
#include "commands/view.h"
#include "utils/temprel.h"
#include "executor/spi_priv.h"
+#include "catalog/pg_index.h"
+#include "utils/relcache.h"
#ifdef _DROP_COLUMN_HACK__
-#include "catalog/pg_index.h"
#include "parser/parse.h"
#endif /* _DROP_COLUMN_HACK__ */
#include "access/genam.h"
@@ -1241,12 +1242,18 @@ AlterTableAddConstraint(char *relationName,
case T_FkConstraint:
{
FkConstraint *fkconstraint = (FkConstraint *) newConstraint;
- Relation rel;
+ Relation rel, pkrel;
HeapScanDesc scan;
HeapTuple tuple;
Trigger trig;
List *list;
int count;
+ List *indexoidlist,
+ *indexoidscan;
+ Form_pg_index indexStruct = NULL;
+ Form_pg_attribute *rel_attrs = NULL;
+ int i;
+ int found=0;
if (get_temp_rel_by_username(fkconstraint->pktable_name)!=NULL &&
get_temp_rel_by_username(relationName)==NULL) {
@@ -1273,8 +1280,10 @@ AlterTableAddConstraint(char *relationName,
* doesn't delete rows out from under us.
*/
- rel = heap_openr(fkconstraint->pktable_name, AccessExclusiveLock);
- heap_close(rel, NoLock);
+ pkrel = heap_openr(fkconstraint->pktable_name, AccessExclusiveLock);
+ if (pkrel == NULL)
+ elog(ERROR, "referenced table \"%s\" not found",
+ fkconstraint->pktable_name);
/*
* Grab an exclusive lock on the fk table, and then scan
@@ -1284,6 +1293,82 @@ AlterTableAddConstraint(char *relationName,
* and that's that.
*/
rel = heap_openr(relationName, AccessExclusiveLock);
+ if (rel == NULL)
+ elog(ERROR, "table \"%s\" not found",
+ relationName);
+
+ /* First we check for limited correctness of the constraint */
+
+ rel_attrs = pkrel->rd_att->attrs;
+ indexoidlist = RelationGetIndexList(pkrel);
+
+ foreach(indexoidscan, indexoidlist)
+ {
+ Oid indexoid = lfirsti(indexoidscan);
+ HeapTuple indexTuple;
+ List *attrl;
+ indexTuple = SearchSysCacheTuple(INDEXRELID,
+ ObjectIdGetDatum(indexoid),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(indexTuple))
+ elog(ERROR, "transformFkeyGetPrimaryKey: index %u not found",
+ indexoid);
+ indexStruct = (Form_pg_index) GETSTRUCT(indexTuple);
+
+ if (indexStruct->indisunique) {
+ /* go through the fkconstraint->pk_attrs list */
+ foreach(attrl, fkconstraint->pk_attrs) {
+ Ident *attr=lfirst(attrl);
+ found=0;
+ for (i = 0; i < INDEX_MAX_KEYS && indexStruct->indkey[i] != 0; i++)
+ {
+ int pkattno = indexStruct->indkey[i];
+ if (pkattno>0) {
+ char *name = NameStr(rel_attrs[pkattno-1]->attname);
+ if (strcmp(name, attr->name)==0) {
+ found=1;
+ break;
+ }
+ }
+ }
+ if (!found)
+ break;
+ }
+ }
+ if (found)
+ break;
+ indexStruct = NULL;
+ }
+ if (!found)
+ elog(ERROR, "UNIQUE constraint matching given keys for referenced table \"%s\" not found",
+ fkconstraint->pktable_name);
+
+ freeList(indexoidlist);
+ heap_close(pkrel, NoLock);
+
+ rel_attrs = rel->rd_att->attrs;
+ if (fkconstraint->fk_attrs!=NIL) {
+ int found=0;
+ List *fkattrs;
+ Ident *fkattr;
+ foreach(fkattrs, fkconstraint->fk_attrs) {
+ int count=0;
+ found=0;
+ fkattr=lfirst(fkattrs);
+ for (; count < rel->rd_att->natts; count++) {
+ char *name = NameStr(rel->rd_att->attrs[count]->attname);
+ if (strcmp(name, fkattr->name)==0) {
+ found=1;
+ break;
+ }
+ }
+ if (!found)
+ break;
+ }
+ if (!found)
+ elog(ERROR, "columns referenced in foreign key constraint not found.");
+ }
+
trig.tgoid = 0;
if (fkconstraint->constr_name)
trig.tgname = fkconstraint->constr_name;
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index f3cad18de86..fe21804a2c4 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -6,7 +6,7 @@
* Portions Copyright (c) 1996-2000, PostgreSQL, Inc
* Portions Copyright (c) 1994, Regents of the University of California
*
- * $Id: analyze.c,v 1.155 2000/08/22 12:59:04 ishii Exp $
+ * $Id: analyze.c,v 1.156 2000/08/29 04:20:44 momjian Exp $
*
*-------------------------------------------------------------------------
*/
@@ -52,6 +52,7 @@ static void transformForUpdate(Query *qry, List *forUpdate);
static void transformFkeyGetPrimaryKey(FkConstraint *fkconstraint);
static void transformConstraintAttrs(List *constraintList);
static void transformColumnType(ParseState *pstate, ColumnDef *column);
+static void transformFkeyCheckAttrs(FkConstraint *fkconstraint);
/* kluge to return extra info from transformCreateStmt() */
static List *extras_before;
@@ -1062,6 +1063,33 @@ transformCreateStmt(ParseState *pstate, CreateStmt *stmt)
if (fkconstraint->constr_name == NULL)
fkconstraint->constr_name = "<unnamed>";
+ /*
+ * Check to see if the attributes mentioned by the constraint
+ * actually exist on this table.
+ */
+ if (fkconstraint->fk_attrs!=NIL) {
+ int found=0;
+ List *cols;
+ List *fkattrs;
+ Ident *fkattr;
+ ColumnDef *col;
+ foreach(fkattrs, fkconstraint->fk_attrs) {
+ found=0;
+ fkattr=lfirst(fkattrs);
+ foreach(cols, columns) {
+ col=lfirst(cols);
+ if (strcmp(col->colname, fkattr->name)==0) {
+ found=1;
+ break;
+ }
+ }
+ if (!found)
+ break;
+ }
+ if (!found)
+ elog(ERROR, "columns referenced in foreign key constraint not found.");
+ }
+
/*
* If the attribute list for the referenced table was omitted,
* lookup for the definition of the primary key. If the
@@ -1096,7 +1124,43 @@ transformCreateStmt(ParseState *pstate, CreateStmt *stmt)
fkconstraint->pktable_name);
}
}
-
+ else {
+ if (strcmp(fkconstraint->pktable_name, stmt->relname)!=0)
+ transformFkeyCheckAttrs(fkconstraint);
+ else {
+ /* Get a unique/pk constraint from above */
+ List *index;
+ int found=0;
+ foreach(index, ilist)
+ {
+ IndexStmt *ind=lfirst(index);
+ IndexElem *indparm;
+ List *indparms;
+ List *pkattrs;
+ Ident *pkattr;
+ if (ind->unique) {
+ foreach(pkattrs, fkconstraint->pk_attrs) {
+ found=0;
+ pkattr=lfirst(pkattrs);
+ foreach(indparms, ind->indexParams) {
+ indparm=lfirst(indparms);
+ if (strcmp(indparm->name, pkattr->name)==0) {
+ found=1;
+ break;
+ }
+ }
+ if (!found)
+ break;
+ }
+ }
+ if (found)
+ break;
+ }
+ if (!found)
+ elog(ERROR, "UNIQUE constraint matching given keys for referenced table \"%s\" not found",
+ fkconstraint->pktable_name);
+ }
+ }
/*
* Build a CREATE CONSTRAINT TRIGGER statement for the CHECK
* action.
@@ -2030,6 +2094,89 @@ transformForUpdate(Query *qry, List *forUpdate)
/*
+ * transformFkeyCheckAttrs -
+ *
+ * Try to make sure that the attributes of a referenced table
+ * belong to a unique (or primary key) constraint.
+ *
+ */
+static void
+transformFkeyCheckAttrs(FkConstraint *fkconstraint)
+{
+ Relation pkrel;
+ Form_pg_attribute *pkrel_attrs;
+ List *indexoidlist,
+ *indexoidscan;
+ Form_pg_index indexStruct = NULL;
+ int i;
+ int found=0;
+
+ /* ----------
+ * Open the referenced table and get the attributes list
+ * ----------
+ */
+ pkrel = heap_openr(fkconstraint->pktable_name, AccessShareLock);
+ if (pkrel == NULL)
+ elog(ERROR, "referenced table \"%s\" not found",
+ fkconstraint->pktable_name);
+ pkrel_attrs = pkrel->rd_att->attrs;
+
+ /* ----------
+ * Get the list of index OIDs for the table from the relcache,
+ * and look up each one in the pg_index syscache for each unique
+ * one, and then compare the attributes we were given to those
+ * defined.
+ * ----------
+ */
+ indexoidlist = RelationGetIndexList(pkrel);
+
+ foreach(indexoidscan, indexoidlist)
+ {
+ Oid indexoid = lfirsti(indexoidscan);
+ HeapTuple indexTuple;
+ List *attrl;
+ indexTuple = SearchSysCacheTuple(INDEXRELID,
+ ObjectIdGetDatum(indexoid),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(indexTuple))
+ elog(ERROR, "transformFkeyGetPrimaryKey: index %u not found",
+ indexoid);
+ indexStruct = (Form_pg_index) GETSTRUCT(indexTuple);
+
+ if (indexStruct->indisunique) {
+ /* go through the fkconstraint->pk_attrs list */
+ foreach(attrl, fkconstraint->pk_attrs) {
+ Ident *attr=lfirst(attrl);
+ found=0;
+ for (i = 0; i < INDEX_MAX_KEYS && indexStruct->indkey[i] != 0; i++)
+ {
+ int pkattno = indexStruct->indkey[i];
+ if (pkattno>0) {
+ char *name = NameStr(pkrel_attrs[pkattno - 1]->attname);
+ if (strcmp(name, attr->name)==0) {
+ found=1;
+ break;
+ }
+ }
+ }
+ if (!found)
+ break;
+ }
+ }
+ if (found)
+ break;
+ indexStruct = NULL;
+ }
+ if (!found)
+ elog(ERROR, "UNIQUE constraint matching given keys for referenced table \"%s\" not found",
+ fkconstraint->pktable_name);
+
+ freeList(indexoidlist);
+ heap_close(pkrel, AccessShareLock);
+}
+
+
+/*
* transformFkeyGetPrimaryKey -
*
* Try to find the primary key attributes of a referenced table if
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index a0fef3b55ee..d79a65c983a 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -282,6 +282,14 @@ INSERT INTO tmp2 values (4);
INSERT INTO tmp3 values (1,10);
INSERT INTO tmp3 values (1,20);
INSERT INTO tmp3 values (5,50);
+-- Try (and fail) to add constraint due to invalid source columns
+ALTER TABLE tmp3 add constraint tmpconstr foreign key(c) references tmp2 match full;
+NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s)
+ERROR: columns referenced in foreign key constraint not found.
+-- Try (and fail) to add constraint due to invalide destination columns explicitly given
+ALTER TABLE tmp3 add constraint tmpconstr foreign key(a) references tmp2(b) match full;
+NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s)
+ERROR: UNIQUE constraint matching given keys for referenced table "tmp2" not found
-- Try (and fail) to add constraint due to invalid data
ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s)
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index 6a03b7259f9..105d07adfd8 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -690,3 +690,16 @@ DROP TABLE FKTABLE;
NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable"
NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "pktable"
DROP TABLE PKTABLE;
+CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY);
+NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable'
+CREATE TABLE FKTABLE_FAIL1 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest2) REFERENCES PKTABLE);
+NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+ERROR: columns referenced in foreign key constraint not found.
+CREATE TABLE FKTABLE_FAIL2 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest1) REFERENCES PKTABLE(ptest2));
+NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
+ERROR: UNIQUE constraint matching given keys for referenced table "pktable" not found
+DROP TABLE FKTABLE_FAIL1;
+ERROR: Relation 'fktable_fail1' does not exist
+DROP TABLE FKTABLE_FAIL2;
+ERROR: Relation 'fktable_fail2' does not exist
+DROP TABLE PKTABLE;
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 0642e96ba19..cb7b9a2a469 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -180,6 +180,12 @@ INSERT INTO tmp3 values (1,10);
INSERT INTO tmp3 values (1,20);
INSERT INTO tmp3 values (5,50);
+-- Try (and fail) to add constraint due to invalid source columns
+ALTER TABLE tmp3 add constraint tmpconstr foreign key(c) references tmp2 match full;
+
+-- Try (and fail) to add constraint due to invalide destination columns explicitly given
+ALTER TABLE tmp3 add constraint tmpconstr foreign key(a) references tmp2(b) match full;
+
-- Try (and fail) to add constraint due to invalid data
ALTER TABLE tmp3 add constraint tmpconstr foreign key (a) references tmp2 match full;
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index d08f2ce2d0f..ac29e50579c 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -411,4 +411,10 @@ SELECT * from FKTABLE;
DROP TABLE FKTABLE;
DROP TABLE PKTABLE;
+CREATE TABLE PKTABLE (ptest1 int PRIMARY KEY);
+CREATE TABLE FKTABLE_FAIL1 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest2) REFERENCES PKTABLE);
+CREATE TABLE FKTABLE_FAIL2 ( ftest1 int, CONSTRAINT fkfail1 FOREIGN KEY (ftest1) REFERENCES PKTABLE(ptest2));
+DROP TABLE FKTABLE_FAIL1;
+DROP TABLE FKTABLE_FAIL2;
+DROP TABLE PKTABLE;
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-30 13:41:09 +0000