Thread: ALTER CONSTRAINT FOREIGN KEY

Question,


When I created a TABLE clone I initially defined a column:
gb_accessions_id INTEGER REFERENCES gb_accessions(gb_accessions_id) NULL


I have renamed TABLE gb_accessions to TABLE gb, and its column 
gb_accessions_id to gb_id.

I next renamed the column 'gb_accessions_id' in TABLE clone to 'gb_id'.
In renaming this column in TABLE clone I appear to have lost the 
reference to TABLE gb (see below).

Questions:

1. During which step in my renaming did this occur?
2. How should I have carried this out?
3. Can I add back REFERENCES gb(gb_id)?




Database chlamy_est - Table clone

Field    Type    LengthNot Null    Default clone_id     int4     4  Yes     nextval('"clone_clone_id_seq"'::text)
project    int4     4  Yes plate     int4     4  Yes row     char     1  Yes col     int4     4  Yes read     char
1 Yes ver     int4     4  Yes seq     text  var  Yes qual     text  var  Yes length     int4     4  Yes qual_length
int4    4  Yes mean_qual_after_trim     int4     4  Yes qual_start     int4     4  Yes qual_end     int4     4  Yes
comment    text  var  Yes gb_id     int4     4  No
 


Keyname    Unique    Primary    Field
clone_pkey    Yes    Yes    clone_id
clone_project_key    Yes    No    col
clone_project_key    Yes    No    plate
clone_project_key    Yes    No    project
clone_project_key    Yes    No    read
clone_project_key    Yes    No    row
clone_project_key    Yes    No    ver


Thanks.
-- 
Regards,
Chuck

On Thu, 7 Mar 2002, Charles Hauser wrote:

> When I created a TABLE clone I initially defined a column:
> gb_accessions_id INTEGER REFERENCES gb_accessions(gb_accessions_id) NULL
>
>
> I have renamed TABLE gb_accessions to TABLE gb, and its column
> gb_accessions_id to gb_id.
>
> I next renamed the column 'gb_accessions_id' in TABLE clone to 'gb_id'.
> In renaming this column in TABLE clone I appear to have lost the
> reference to TABLE gb (see below).
>
> Questions:
>
> 1. During which step in my renaming did this occur?

As soon as you renamed either the table or column the constraint got
broken.

> 2. How should I have carried this out?

Pretty much you need to kill the references constraint that was there.
This involes dropping the triggeres involved in the constraint (see
techdocs.postgresql.org for more details - they've got some writings
that will help)

> 3. Can I add back REFERENCES gb(gb_id)?
Yes, use alter table add constraint to add the constraint back.