From bd9b32803bee2a85e41deb5e546c3b0e16912e2b Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Fri, 7 Sep 2001 21:57:53 +0000 Subject: [PATCH] Here is my much-promised patch to let people add UNIQUE constraints after table creation time. Big deal you say - but this patch is the basis of the next thing which is adding PRIMARY KEYs after table creation time. (Which is currently impossible without twiddling catalogs) Rundown ------- * I have made the makeObjectName function of analyze.c non-static, and exported it in analyze.h * I have included analyze.h and defrem.h into command.c, to support makingObjectNames and creating indices * I removed the 'case CONSTR_PRIMARY' clause so that it properly fails and says you can't add primary keys, rather than just doing nothing and reporting nothing!!! * I have modified the docs. Algorithm --------- * If name specified is null, search for a new valid constraint name. I'm not sure if I should "lock" my generated name somehow tho - should I open the relation before doing this step? * Open relation in access exclusive mode * Check that the constraint does not already exist * Define the new index * Warn if they're doubling up on an existing index Christopher Kings-Lynne --- doc/src/sgml/ref/alter_table.sgml | 15 ++- src/backend/commands/command.c | 161 +++++++++++++++++++++++++++++- src/backend/parser/analyze.c | 4 +- src/include/parser/analyze.h | 5 +- 4 files changed, 173 insertions(+), 12 deletions(-) diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index f5f22cb85d..eb769468c6 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -1,5 +1,5 @@ @@ -211,9 +211,9 @@ ALTER TABLE table - In the current implementation, only FOREIGN KEY and CHECK constraints can - be added to a table. To create a unique constraint, create - a unique index (see ). @@ -297,6 +297,13 @@ ALTER TABLE distributors DROP CONSTRAINT zipchk ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses(address) MATCH FULL + + + To add a (multi-column) unique constraint to a table: + +ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode) + + diff --git a/src/backend/commands/command.c b/src/backend/commands/command.c index 81ce6e1185..36cf799710 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.141 2001/08/21 16:36:01 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/commands/Attic/command.c,v 1.142 2001/09/07 21:57:53 momjian Exp $ * * NOTES * The PerformAddAttribute() code, like most of the relation @@ -32,6 +32,7 @@ #include "catalog/pg_type.h" #include "commands/command.h" #include "commands/trigger.h" +#include "commands/defrem.h" /* For add constraint unique, primary */ #include "executor/execdefs.h" #include "executor/executor.h" #include "miscadmin.h" @@ -42,6 +43,7 @@ #include "parser/parse_expr.h" #include "parser/parse_oper.h" #include "parser/parse_relation.h" +#include "parser/analyze.h" /* For add constraint unique, primary */ #include "utils/acl.h" #include "utils/builtins.h" #include "utils/fmgroids.h" @@ -1339,11 +1341,160 @@ AlterTableAddConstraint(char *relationName, break; } - case CONSTR_PRIMARY: + case CONSTR_UNIQUE: { - - break; - } + char *iname = constr->name; + bool istemp = is_temp_rel_name(relationName); + Relation rel; + List *indexoidlist; + List *indexoidscan; + Form_pg_attribute *rel_attrs; + int num_keys = 0; + int keys_matched = 0; + bool index_found = false; + bool index_found_unique = false; + bool index_found_primary = false; + + /* If the constraint name is not specified, generate a name */ + if (iname == NULL) { + Oid indoid; + int pass = 0; + char *typename = palloc(NAMEDATALEN); + Ident *key; + + /* Assume that the length of the attr list is already > 0 */ + + /* Get the first attribute so we can use its name */ + key = (Ident *)lfirst(constr->keys); + + /* Initialise typename to 'key' */ + snprintf(typename, NAMEDATALEN, "key"); + + for (;;) + { + iname = makeObjectName(relationName, key->name, typename); + + /* Check for a conflict */ + indoid = RelnameFindRelid(iname); + + /* If the oid was not found, then we have a safe name */ + if ((!istemp && !OidIsValid(indoid)) || + (istemp && !is_temp_rel_name(iname))) + break; + + /* Found a conflict, so try a new name component */ + pfree(iname); + snprintf(typename, NAMEDATALEN, "key%d", ++pass); + } + } + + /* Need to check for unique key already on field(s) */ + rel = heap_openr(relationName, AccessExclusiveLock); + + /* + * First we check for limited correctness of the + * constraint + */ + + rel_attrs = rel->rd_att->attrs; + + /* Retrieve the oids of all indices on the relation */ + indexoidlist = RelationGetIndexList(rel); + index_found = false; + index_found_unique = false; + index_found_primary = false; + + /* Loop over all indices on the relation */ + foreach(indexoidscan, indexoidlist) + { + Oid indexoid = lfirsti(indexoidscan); + HeapTuple indexTuple; + Form_pg_index indexStruct; + List *keyl; + int i; + + indexTuple = SearchSysCache(INDEXRELID, + ObjectIdGetDatum(indexoid), + 0, 0, 0); + + if (!HeapTupleIsValid(indexTuple)) + elog(ERROR, "ALTER TABLE/ADD CONSTRAINT: Index \"%u\" not found", + indexoid); + indexStruct = (Form_pg_index) GETSTRUCT(indexTuple); + + /* + * Make sure this index has the same number of + * keys as the constraint -- It obviously won't match otherwise. + */ + for (i = 0; i < INDEX_MAX_KEYS && indexStruct->indkey[i] != 0; i++); + num_keys = length(constr->keys); + keys_matched = 0; + + if (i == num_keys) + { + /* Loop over each key in the constraint and check that there is a + corresponding key in the index. */ + i = 0; + foreach(keyl, constr->keys) + { + Ident *key = lfirst(keyl); + + /* Look at key[i] in the index and check that it is over the same column + as key[i] in the constraint. This is to differentiate between (a,b) + and (b,a) */ + if (i < INDEX_MAX_KEYS && indexStruct->indkey[i] != 0) + { + int keyno = indexStruct->indkey[i]; + + if (keyno > 0) + { + char *name = NameStr(rel_attrs[keyno - 1]->attname); + if (strcmp(name, key->name) == 0) keys_matched++; + } + } + else elog(ERROR, "ALTER TABLE/ADD CONSTRAINT: Key \"%u[%u]\" not found", indexoid, i); + i++; + } + if (keys_matched == num_keys) { + index_found = true; + index_found_unique = indexStruct->indisunique; + index_found_primary = indexStruct->indisprimary; + if (index_found_unique || index_found_primary) break; + } + } + ReleaseSysCache(indexTuple); + } + + freeList(indexoidlist); + + if (index_found_primary) + elog(ERROR, "Unique primary key already defined on relation \"%s\"", relationName); + else if (index_found_unique) + elog(ERROR, "Unique constraint already defined on the specified attributes in relation \"%s\"", relationName); + + /* If everything is ok, create the new index (constraint) */ + DefineIndex( + relationName, + iname, + "btree", + constr->keys, + true, + false, + NULL, + NIL); + + /* Issue notice */ + elog(NOTICE, "ALTER TABLE/ADD UNIQUE will create implicit index '%s' for table '%s'", + iname, relationName); + if (index_found) + elog(NOTICE, "Unique constraint supercedes existing index on relation \"%s\". Drop the existing index to remove redundancy.", relationName); + pfree(iname); + + /* Finally, close relation */ + heap_close(rel, NoLock); + + break; + } default: elog(ERROR, "ALTER TABLE / ADD CONSTRAINT is not implemented for that constraint type."); } diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 528cc91c96..4f1bf724db 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Header: /cvsroot/pgsql/src/backend/parser/analyze.c,v 1.197 2001/08/24 20:03:45 petere Exp $ + * $Header: /cvsroot/pgsql/src/backend/parser/analyze.c,v 1.198 2001/09/07 21:57:53 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -562,7 +562,7 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt) * from the truncated characters. Currently it seems best to keep it simple, * so that the generated names are easily predictable by a person. */ -static char * +char * makeObjectName(char *name1, char *name2, char *typename) { char *name; diff --git a/src/include/parser/analyze.h b/src/include/parser/analyze.h index 02c583f270..33c76ae877 100644 --- a/src/include/parser/analyze.h +++ b/src/include/parser/analyze.h @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: analyze.h,v 1.14 2001/01/24 19:43:26 momjian Exp $ + * $Id: analyze.h,v 1.15 2001/09/07 21:57:53 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -19,4 +19,7 @@ extern List *parse_analyze(Node *parseTree, ParseState *parentParseState); extern void CheckSelectForUpdate(Query *qry); +/* This was exported to allow ADD CONSTRAINT to make use of it */ +extern char *makeObjectName(char *name1, char *name2, char *typename); + #endif /* ANALYZE_H */ -- 2.40.0