Thread: Add Foreign Keys To Table
I'm having difficulty finding the correct syntax to modify an existing table. The modification is to add two columns, each a foreign reference to the two key columns of another table. The other table: CREATE TABLE station_type ( sta_type VARCHAR(50), secondary_type VARCHAR(50), natural_indic BOOL, PRIMARY KEY (sta_type, secondary_type) ); When I try to alter the other table to add columns: sta_type VARCHAR(50) REFERENCES station_type(sta_type) and secondary_type VARCHAR(50) REFERENCES station_type(secondary_type) I get syntax errors. For example, alter table station_information add column sta_type varchar(50) references station_type(sta_type); ERROR: there is no unique constraint matching given keys for referenced table "station_type" alter table station_information add column sta_type varchar(50) not null references station_type(sta_type); ERROR: there is no unique constraint matching given keys for referenced table "station_type" alter table station_information add column sta_type varchar(50) unique not null references station_type(sta_type); NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "station_information_sta_type_key" for table "station_information" ERROR: there is no unique constraint matching given keys for referenced table "station_type" Reading the alter table document page for 9.x does not show me what I'm doing incorrectly. Rich
On July 7, 2011 10:40:11 AM Rich Shepard wrote: > alter table station_information add column sta_type varchar(50) > unique not null references station_type(sta_type); > NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index > "station_information_sta_type_key" for table "station_information" > ERROR: there is no unique constraint matching given keys for referenced > table "station_type" > Reading the alter table document page for 9.x does not show me what I'm > doing incorrectly. You need a unique index on station_type.sta_type
On Thu, 7 Jul 2011, Alan Hodgson wrote: > You need a unique index on station_type.sta_type Alan, station_type(sta_type) is part of a composite primary key. Doesn't primary key automatically imply unique and not null? Thanks, Rich
On July 7, 2011 11:55:25 AM Rich Shepard wrote: > On Thu, 7 Jul 2011, Alan Hodgson wrote: > > You need a unique index on station_type.sta_type > > Alan, > > station_type(sta_type) is part of a composite primary key. Doesn't > primary key automatically imply unique and not null? It implies the composite is unique. Not sta_type.
On Thu, 7 Jul 2011, Alan Hodgson wrote: > It implies the composite is unique. Not sta_type. OK. Now I understand. How, then, do I add a unique constraint to each component of the composite key so I can add them as foreign keys to the station_information table? Or, is there another way to add those two columns to station_information and then add the foreign key constraints? Thanks for clarifying, Rich
On July 7, 2011 12:30:35 PM Rich Shepard wrote: > On Thu, 7 Jul 2011, Alan Hodgson wrote: > > It implies the composite is unique. Not sta_type. > > OK. Now I understand. How, then, do I add a unique constraint to each > component of the composite key so I can add them as foreign keys to the > station_information table? Or, is there another way to add those two > columns to station_information and then add the foreign key constraints? > > Thanks for clarifying, > create unique index index_name on table (column). Or I think you can create a foreign key on a composite like "foreign key (column1,column2) references table (column1,column2)" which probably makes more sense if that is a natural key.
To do what you want to do look up "CREATE INDEX" in the documentation. You may wish to provide the PK/FK schema for the tables in questions as it appears - at first take - that you are doing something wrong If you have a compound Primary Key with component fields that are also "UNIQUE". You probably need to add "BOTH" fields to "station_information" and then say something like. FOREIGN KEY (field1, field2) REFERENCES table (field1, field2) ... David J. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Rich Shepard Sent: Thursday, July 07, 2011 3:31 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Add Foreign Keys To Table On Thu, 7 Jul 2011, Alan Hodgson wrote: > It implies the composite is unique. Not sta_type. OK. Now I understand. How, then, do I add a unique constraint to each component of the composite key so I can add them as foreign keys to the station_information table? Or, is there another way to add those two columns to station_information and then add the foreign key constraints? Thanks for clarifying, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 07/07/11 10:40 AM, Rich Shepard wrote: > I'm having difficulty finding the correct syntax to modify an existing > table. The modification is to add two columns, each a foreign > reference to > the two key columns of another table. > > The other table: > > CREATE TABLE station_type ( > sta_type VARCHAR(50), > secondary_type VARCHAR(50), > natural_indic BOOL, > PRIMARY KEY (sta_type, secondary_type) > ); > > When I try to alter the other table to add columns: > sta_type VARCHAR(50) REFERENCES station_type(sta_type) and > secondary_type VARCHAR(50) REFERENCES station_type(secondary_type) > I get syntax errors. Since your PK of station_type is a composite, your foreign key must also be composite. CREATE TABLE stuffed ( id serial; otherestuffs text; sta varchar(50), sec varchar(50), FOREIGN KEY (sta, sec) REFERENCES station_type(sta_type, secondary_type) ; ); -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On Thu, 7 Jul 2011, Alan Hodgson wrote: > create unique index index_name on table (column). Alan, This worked like a charm. Many thanks for the lesson, Rich
On Thu, 7 Jul 2011, John R Pierce wrote: > Since your PK of station_type is a composite, your foreign key must also be > composite. > > CREATE TABLE stuffed ( > id serial; > otherestuffs text; > sta varchar(50), > sec varchar(50), > FOREIGN KEY (sta, sec) REFERENCES station_type(sta_type, secondary_type) > ); Thanks, John, for showing me how to apply this approach. Rich
On 07/07/11 1:02 PM, Rich Shepard wrote: > On Thu, 7 Jul 2011, Alan Hodgson wrote: > >> create unique index index_name on table (column). > > Alan, > > This worked like a charm. > > Many thanks for the lesson, > > Rich > if your original table has Primary Key of (sta_type, secondary_type) I would not expect EITHER of those fields to be unique by themselves.... Surely there can be more than one of the same sta_type with different secondary_type's, just as there could be more than one secondary_type with different sta_types.... if either of these fields is unique of and by itself, it doesn't make sense to use the combined primary key. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
-----Original Message----- From: Rich Shepard [mailto:rshepard@appl-ecosys.com] Sent: Thursday, July 07, 2011 4:05 PM To: David Johnston Subject: RE: [GENERAL] Add Foreign Keys To Table On Thu, 7 Jul 2011, David Johnston wrote: > To do what you want to do look up "CREATE INDEX" in the documentation. David, Now I understand this. > You probably need to add "BOTH" fields to "station_information" and > then say something like. > > FOREIGN KEY (field1, field2) REFERENCES table (field1, field2) ... And this would be for each of the two added fields? Same syntax? I've not used this approach before. Thanks, Rich >>>>>>>>>>>>>>>>>>>>>>>>> You would HAVE to do it at the TABLE level since a column-level constraint can only reference that single column. David J.
On Thu, 7 Jul 2011, John R Pierce wrote: > if your original table has Primary Key of (sta_type, secondary_type) I > would not expect EITHER of those fields to be unique by themselves.... > Surely there can be more than one of the same sta_type with different > secondary_type's, just as there could be more than one secondary_type with > different sta_types.... if either of these fields is unique of and by > itself, it doesn't make sense to use the combined primary key. John, Ah, yes. Of course. That's why the compound primary key is required. I'll redo the table the proper way. Thanks, Rich
You have a wrong concept of foreign keys. "sta_type" is not a key of table station_type, which cannot be referened as a foreign key. -------------------------------------------------- From: "Rich Shepard" <rshepard@appl-ecosys.com> Sent: Friday, July 08, 2011 1:40 AM To: <pgsql-general@postgresql.org> Subject: [GENERAL] Add Foreign Keys To Table > I'm having difficulty finding the correct syntax to modify an existing > table. The modification is to add two columns, each a foreign reference to > the two key columns of another table. > > The other table: > > CREATE TABLE station_type ( > sta_type VARCHAR(50), > secondary_type VARCHAR(50), > natural_indic BOOL, > PRIMARY KEY (sta_type, secondary_type) > ); > > When I try to alter the other table to add columns: > sta_type VARCHAR(50) REFERENCES station_type(sta_type) and > secondary_type VARCHAR(50) REFERENCES station_type(secondary_type) > I get syntax errors. For example, > > alter table station_information add column sta_type varchar(50) references > station_type(sta_type); > ERROR: there is no unique constraint matching given keys for referenced > table "station_type" > > alter table station_information add column sta_type varchar(50) not null > references station_type(sta_type); > ERROR: there is no unique constraint matching given keys for referenced > table "station_type" > > alter table station_information add column sta_type varchar(50) > unique not null references station_type(sta_type); > NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index > "station_information_sta_type_key" for table "station_information" > ERROR: there is no unique constraint matching given keys for referenced > table "station_type" > > Reading the alter table document page for 9.x does not show me what I'm > doing incorrectly. > > Rich > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >