Skip to content

Commit bd9b328

Browse files
bmomjianbmomjian
committed
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
1 parent 68e190c commit bd9b328

File tree

4 files changed

+173
-12
lines changed

4 files changed

+173
-12
lines changed

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 11 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.26 2001/09/03 12:57:49 petere Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.27 2001/09/07 21:57:53 momjian Exp $
33
Postgres documentation
44
-->
55

@@ -211,9 +211,9 @@ ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
211211
</para>
212212

213213
<para>
214-
In the current implementation, only FOREIGN KEY and CHECK constraints can
215-
be added to a table. To create a unique constraint, create
216-
a unique index (see <xref linkend="SQL-CREATEINDEX"
214+
In the current implementation, only UNIQUE, FOREIGN KEY and CHECK constraints can
215+
be added to a table. To create a primary constraint, create
216+
a unique, not null index (see <xref linkend="SQL-CREATEINDEX"
217217
endterm="SQL-CREATEINDEX-title">).
218218
</para>
219219

@@ -297,6 +297,13 @@ ALTER TABLE distributors DROP CONSTRAINT zipchk
297297
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses(address) MATCH FULL
298298
</programlisting>
299299
</para>
300+
301+
<para>
302+
To add a (multi-column) unique constraint to a table:
303+
<programlisting>
304+
ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode)
305+
</programlisting>
306+
</para>
300307
</refsect1>
301308

302309
<refsect1 id="R1-SQL-ALTERTABLE-3">

‎src/backend/commands/command.c

Lines changed: 156 additions & 5 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.141 2001/08/21 16:36:01 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/commands/Attic/command.c,v 1.142 2001/09/07 21:57:53 momjian Exp $
1212
*
1313
* NOTES
1414
* The PerformAddAttribute() code, like most of the relation
@@ -32,6 +32,7 @@
3232
#include "catalog/pg_type.h"
3333
#include "commands/command.h"
3434
#include "commands/trigger.h"
35+
#include "commands/defrem.h" /* For add constraint unique, primary */
3536
#include "executor/execdefs.h"
3637
#include "executor/executor.h"
3738
#include "miscadmin.h"
@@ -42,6 +43,7 @@
4243
#include "parser/parse_expr.h"
4344
#include "parser/parse_oper.h"
4445
#include "parser/parse_relation.h"
46+
#include "parser/analyze.h" /* For add constraint unique, primary */
4547
#include "utils/acl.h"
4648
#include "utils/builtins.h"
4749
#include "utils/fmgroids.h"
@@ -1339,11 +1341,160 @@ AlterTableAddConstraint(char *relationName,
13391341

13401342
break;
13411343
}
1342-
case CONSTR_PRIMARY:
1344+
case CONSTR_UNIQUE:
13431345
{
1344-
1345-
break;
1346-
}
1346+
char *iname = constr->name;
1347+
bool istemp = is_temp_rel_name(relationName);
1348+
Relation rel;
1349+
List *indexoidlist;
1350+
List *indexoidscan;
1351+
Form_pg_attribute *rel_attrs;
1352+
int num_keys = 0;
1353+
int keys_matched = 0;
1354+
bool index_found = false;
1355+
bool index_found_unique = false;
1356+
bool index_found_primary = false;
1357+
1358+
/* If the constraint name is not specified, generate a name */
1359+
if (iname == NULL) {
1360+
Oid indoid;
1361+
int pass = 0;
1362+
char *typename = palloc(NAMEDATALEN);
1363+
Ident *key;
1364+
1365+
/* Assume that the length of the attr list is already > 0 */
1366+
1367+
/* Get the first attribute so we can use its name */
1368+
key = (Ident *)lfirst(constr->keys);
1369+
1370+
/* Initialise typename to 'key' */
1371+
snprintf(typename, NAMEDATALEN, "key");
1372+
1373+
for (;;)
1374+
{
1375+
iname = makeObjectName(relationName, key->name, typename);
1376+
1377+
/* Check for a conflict */
1378+
indoid = RelnameFindRelid(iname);
1379+
1380+
/* If the oid was not found, then we have a safe name */
1381+
if ((!istemp && !OidIsValid(indoid)) ||
1382+
(istemp && !is_temp_rel_name(iname)))
1383+
break;
1384+
1385+
/* Found a conflict, so try a new name component */
1386+
pfree(iname);
1387+
snprintf(typename, NAMEDATALEN, "key%d", ++pass);
1388+
}
1389+
}
1390+
1391+
/* Need to check for unique key already on field(s) */
1392+
rel = heap_openr(relationName, AccessExclusiveLock);
1393+
1394+
/*
1395+
* First we check for limited correctness of the
1396+
* constraint
1397+
*/
1398+
1399+
rel_attrs = rel->rd_att->attrs;
1400+
1401+
/* Retrieve the oids of all indices on the relation */
1402+
indexoidlist = RelationGetIndexList(rel);
1403+
index_found = false;
1404+
index_found_unique = false;
1405+
index_found_primary = false;
1406+
1407+
/* Loop over all indices on the relation */
1408+
foreach(indexoidscan, indexoidlist)
1409+
{
1410+
Oid indexoid = lfirsti(indexoidscan);
1411+
HeapTuple indexTuple;
1412+
Form_pg_index indexStruct;
1413+
List *keyl;
1414+
int i;
1415+
1416+
indexTuple = SearchSysCache(INDEXRELID,
1417+
ObjectIdGetDatum(indexoid),
1418+
0, 0, 0);
1419+
1420+
if (!HeapTupleIsValid(indexTuple))
1421+
elog(ERROR, "ALTER TABLE/ADD CONSTRAINT: Index \"%u\" not found",
1422+
indexoid);
1423+
indexStruct = (Form_pg_index) GETSTRUCT(indexTuple);
1424+
1425+
/*
1426+
* Make sure this index has the same number of
1427+
* keys as the constraint -- It obviously won't match otherwise.
1428+
*/
1429+
for (i = 0; i < INDEX_MAX_KEYS && indexStruct->indkey[i] != 0; i++);
1430+
num_keys = length(constr->keys);
1431+
keys_matched = 0;
1432+
1433+
if (i == num_keys)
1434+
{
1435+
/* Loop over each key in the constraint and check that there is a
1436+
corresponding key in the index. */
1437+
i = 0;
1438+
foreach(keyl, constr->keys)
1439+
{
1440+
Ident *key = lfirst(keyl);
1441+
1442+
/* Look at key[i] in the index and check that it is over the same column
1443+
as key[i] in the constraint. This is to differentiate between (a,b)
1444+
and (b,a) */
1445+
if (i < INDEX_MAX_KEYS && indexStruct->indkey[i] != 0)
1446+
{
1447+
int keyno = indexStruct->indkey[i];
1448+
1449+
if (keyno > 0)
1450+
{
1451+
char *name = NameStr(rel_attrs[keyno - 1]->attname);
1452+
if (strcmp(name, key->name) == 0) keys_matched++;
1453+
}
1454+
}
1455+
else elog(ERROR, "ALTER TABLE/ADD CONSTRAINT: Key \"%u[%u]\" not found", indexoid, i);
1456+
i++;
1457+
}
1458+
if (keys_matched == num_keys) {
1459+
index_found = true;
1460+
index_found_unique = indexStruct->indisunique;
1461+
index_found_primary = indexStruct->indisprimary;
1462+
if (index_found_unique || index_found_primary) break;
1463+
}
1464+
}
1465+
ReleaseSysCache(indexTuple);
1466+
}
1467+
1468+
freeList(indexoidlist);
1469+
1470+
if (index_found_primary)
1471+
elog(ERROR, "Unique primary key already defined on relation \"%s\"", relationName);
1472+
else if (index_found_unique)
1473+
elog(ERROR, "Unique constraint already defined on the specified attributes in relation \"%s\"", relationName);
1474+
1475+
/* If everything is ok, create the new index (constraint) */
1476+
DefineIndex(
1477+
relationName,
1478+
iname,
1479+
"btree",
1480+
constr->keys,
1481+
true,
1482+
false,
1483+
NULL,
1484+
NIL);
1485+
1486+
/* Issue notice */
1487+
elog(NOTICE, "ALTER TABLE/ADD UNIQUE will create implicit index '%s' for table '%s'",
1488+
iname, relationName);
1489+
if (index_found)
1490+
elog(NOTICE, "Unique constraint supercedes existing index on relation \"%s\". Drop the existing index to remove redundancy.", relationName);
1491+
pfree(iname);
1492+
1493+
/* Finally, close relation */
1494+
heap_close(rel, NoLock);
1495+
1496+
break;
1497+
}
13471498
default:
13481499
elog(ERROR, "ALTER TABLE / ADD CONSTRAINT is not implemented for that constraint type.");
13491500
}

‎src/backend/parser/analyze.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
9-
* $Header: /cvsroot/pgsql/src/backend/parser/analyze.c,v 1.197 2001/08/24 20:03:45 petere Exp $
9+
* $Header: /cvsroot/pgsql/src/backend/parser/analyze.c,v 1.198 2001/09/07 21:57:53 momjian Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -562,7 +562,7 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
562562
* from the truncated characters. Currently it seems best to keep it simple,
563563
* so that the generated names are easily predictable by a person.
564564
*/
565-
static char *
565+
char *
566566
makeObjectName(char *name1, char *name2, char *typename)
567567
{
568568
char *name;

‎src/include/parser/analyze.h

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
9-
* $Id: analyze.h,v 1.14 2001/01/24 19:43:26 momjian Exp $
9+
* $Id: analyze.h,v 1.15 2001/09/07 21:57:53 momjian Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -19,4 +19,7 @@ extern List *parse_analyze(Node *parseTree, ParseState *parentParseState);
1919

2020
extern void CheckSelectForUpdate(Query *qry);
2121

22+
/* This was exported to allow ADD CONSTRAINT to make use of it */
23+
extern char *makeObjectName(char *name1, char *name2, char *typename);
24+
2225
#endif /* ANALYZE_H */

0 commit comments

Comments
 (0)