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

Commitbd9b328

Browse files
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 thenext thing which is adding PRIMARY KEYs after table creation time. (Whichis currently impossible without twiddling catalogs)Rundown-------* I have made the makeObjectName function of analyze.c non-static, andexported it in analyze.h* I have included analyze.h and defrem.h into command.c, to supportmakingObjectNames and creating indices* I removed the 'case CONSTR_PRIMARY' clause so that it properly fails andsays you can't add primary keys, rather than just doing nothing andreporting nothing!!!* I have modified the docs.Algorithm---------* If name specified is null, search for a new valid constraint name. I'mnot sure if I should "lock" my generated name somehow tho - should I openthe 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 indexChristopher Kings-Lynne
1 parent68e190c commitbd9b328

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 aunique constraint, create
216-
a unique index (see <xref linkend="SQL-CREATEINDEX"
214+
In the current implementation, onlyUNIQUE,FOREIGN KEY and CHECK constraints can
215+
be added to a table. To create aprimary 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-
caseCONSTR_PRIMARY:
1344+
caseCONSTR_UNIQUE:
13431345
{
1344-
1345-
break;
1346-
}
1346+
char*iname=constr->name;
1347+
boolistemp=is_temp_rel_name(relationName);
1348+
Relationrel;
1349+
List*indexoidlist;
1350+
List*indexoidscan;
1351+
Form_pg_attribute*rel_attrs;
1352+
intnum_keys=0;
1353+
intkeys_matched=0;
1354+
boolindex_found= false;
1355+
boolindex_found_unique= false;
1356+
boolindex_found_primary= false;
1357+
1358+
/* If the constraint name is not specified, generate a name */
1359+
if (iname==NULL) {
1360+
Oidindoid;
1361+
intpass=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+
Oidindexoid=lfirsti(indexoidscan);
1411+
HeapTupleindexTuple;
1412+
Form_pg_indexindexStruct;
1413+
List*keyl;
1414+
inti;
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+
intkeyno=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+
elseelog(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+
elseif (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-
staticchar*
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
externvoidCheckSelectForUpdate(Query*qry);
2121

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

0 commit comments

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