Thread: Rename or Re-Create Constraints?
I've searched and really can't find a definitive example or someone
renaming a constraint. I renamed a table yesterday and noticed that
the constraint name was still named the old table name:
inkpress=# ALTER TABLE accounts RENAME TO fashion;
ALTER TABLE
inkpress=# \d fashion
Table "public.fashion"
Column | Type | Modifiers
---------+-----------------------+-----------
id | integer | not null
vendor | character varying(40) | not null
account | integer | not null
email | character varying(40) | not null
state | character(2) | not null
Indexes:
"accounts_pkey" PRIMARY KEY, btree (id)
"accounts_account_key" UNIQUE, btree (account)
"accounts_email_key" UNIQUE, btree (email)
"accounts_vendor_key" UNIQUE, btree (vendor)
1. Do I need to remove all the table constraints or is there a way to
rename them?
2. When renaming the table, is there a way to rename both the table
and all associated constraints?
I've looked over the following guide and am more confused than ever:
http://www.postgresql.org/docs/8.1/static/sql-altertable.html
Carlos Mennens <carlos.mennens@gmail.com> writes:
> 1. Do I need to remove all the table constraints or is there a way to
> rename them?
I believe you can rename the underlying indexes and the constraints will
follow them. (This works in HEAD anyway, not sure how far back.)
> 2. When renaming the table, is there a way to rename both the table
> and all associated constraints?
No, there's nothing automatic for that. IIRC there used to be code to
try to do this when you renamed a single column; but we took it out,
probably because it risked unexpected failures due to index name
collisions.
regards, tom lane
On Fri, Apr 8, 2011 at 8:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > I believe you can rename the underlying indexes and the constraints will > follow them. (This works in HEAD anyway, not sure how far back.) I'm sorry but I don't understand what that means or how to relate that to a SQL command to rename the constraint. Do you have an example of how that command would look? >> 2. When renaming the table, is there a way to rename both the table >> and all associated constraints? > > No, there's nothing automatic for that. IIRC there used to be code to > try to do this when you renamed a single column; but we took it out, > probably because it risked unexpected failures due to index name > collisions. > > regards, tom lane >
Carlos Mennens wrote: > On Fri, Apr 8, 2011 at 8:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> I believe you can rename the underlying indexes and the constraints will >> follow them. (This works in HEAD anyway, not sure how far back.) >> > > I'm sorry but I don't understand what that means or how to relate that > to a SQL command to rename the constraint. Do you have an example of > how that command would look? > > >>> 2. When renaming the table, is there a way to rename both the table >>> and all associated constraints? >>> >> No, there's nothing automatic for that. IIRC there used to be code to >> try to do this when you renamed a single column; but we took it out, >> probably because it risked unexpected failures due to index name >> collisions. >> >> regards, tom lane >> >> I wonder if OP wouldn't be best advised to externalize his DDL where by simple editing would take care of the issue?
On Fri, Apr 8, 2011 at 8:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I believe you can rename the underlying indexes and the constraints will
> follow them. (This works in HEAD anyway, not sure how far back.)
Below is my table:
inkpress=# \d marketing
Table "public.marketing"
Column | Type | Modifiers
---------+-----------------------+-----------
id | integer | not null
vendor | character varying(40) | not null
account | integer | not null
email | character varying(40) | not null
state | character(2) | not null
Indexes:
"accounts_pkey" PRIMARY KEY, btree (id)
"accounts_account_key" UNIQUE, btree (account)
"accounts_email_key" UNIQUE, btree (email)
"accounts_vendor_key" UNIQUE, btree (vendor)
I renamed the table name from 'accounts' to 'marketing' however all
the constraints listed under 'indexes' are still named 'accounts_*'
and I've tried to rename them but I can't find any information with an
example command to rename the constraints:
ALTER TABLE marketing ...???
I can't find any update / alter SQL commands to correct the constraint
inconsistency.
:(
Carlos Mennens <carlos.mennens@gmail.com> writes:
> On Fri, Apr 8, 2011 at 8:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I believe you can rename the underlying indexes and the constraints will
>> follow them. �(This works in HEAD anyway, not sure how far back.)
> I renamed the table name from 'accounts' to 'marketing' however all
> the constraints listed under 'indexes' are still named 'accounts_*'
> and I've tried to rename them but I can't find any information with an
> example command to rename the constraints:
ALTER INDEX accounts_pkey RENAME TO whatever
On very old versions of PG you may have to spell that "ALTER TABLE"
instead of "ALTER INDEX", but it's the same thing either way.
regards, tom lane
On Sat, Apr 9, 2011 at 12:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > ALTER INDEX accounts_pkey RENAME TO whatever > > On very old versions of PG you may have to spell that "ALTER TABLE" > instead of "ALTER INDEX", but it's the same thing either way. Thank you so much for clearing that up for me Tom! I just couldn't find anything documented or do I understand SQL enough to work through that w/o an example. I read the PostgreSQL documentation all morning and just couldn't find it. Also to make sure I did this correct, if I had an existing table w/o a PRIMARY KEY index / constraint, is the following correct? CREATE UNIQUE INDEX users_pkey ON public.users (id); CREATE INDEX I'm guessing that's how I generate a index / constraint on an existing table when it was generated during the table creation SQL command, right? Is there a difference between an INDEX and a CONSTRAINT?
On Saturday, April 09, 2011 2:59:06 pm Carlos Mennens wrote:
> On Sat, Apr 9, 2011 at 12:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > ALTER INDEX accounts_pkey RENAME TO whatever
> >
> > On very old versions of PG you may have to spell that "ALTER TABLE"
> > instead of "ALTER INDEX", but it's the same thing either way.
>
> Thank you so much for clearing that up for me Tom! I just couldn't
> find anything documented or do I understand SQL enough to work through
> that w/o an example.
>
> I read the PostgreSQL documentation all morning and just couldn't find
> it. Also to make sure I did this correct, if I had an existing table
> w/o a PRIMARY KEY index / constraint, is the following correct?
You want to create a PRIMARY KEY correct? If so starting from scratch:
test(5432)aklaver=>create table pk_test(id integer,fld_1 text);
CREATE TABLE
test(5432)aklaver=>\d pk_test
Table "public.pk_test"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
fld_1 | text |
test(5432)aklaver=>ALTER TABLE pk_test ADD CONSTRAINT pk PRIMARY KEY(id);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "pk" for table
"pk_test"
ALTER TABLE
test(5432)aklaver=>\d pk_test
Table "public.pk_test"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
fld_1 | text |
Indexes:
"pk" PRIMARY KEY, btree (id)
>
> CREATE UNIQUE INDEX users_pkey ON public.users (id);
> CREATE INDEX
>
> I'm guessing that's how I generate a index / constraint on an existing
> table when it was generated during the table creation SQL command,
> right?
>
> Is there a difference between an INDEX and a CONSTRAINT?
--
Adrian Klaver
adrian.klaver@gmail.com
On Saturday, April 09, 2011 2:59:06 pm Carlos Mennens wrote: > > > Is there a difference between an INDEX and a CONSTRAINT? Oops forgot to add this to my previous reply. The short version, an INDEX is one form of a CONSTRAINT. For the long version look at the CREATE TABLE section of the docs. It shows the various CONSTRAINTS available for both columns and tables i.e NOT NULL, FOREIGN KEY, etc -- Adrian Klaver adrian.klaver@gmail.com