Thread: Add Foreign Keys To Table

Add Foreign Keys To Table

From
Rich Shepard
Date:
   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

Re: Add Foreign Keys To Table

From
Alan Hodgson
Date:
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


Re: Add Foreign Keys To Table

From
Rich Shepard
Date:
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

Re: Add Foreign Keys To Table

From
Alan Hodgson
Date:
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.

Re: Add Foreign Keys To Table

From
Rich Shepard
Date:
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


Re: Add Foreign Keys To Table

From
Alan Hodgson
Date:
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.

Re: Add Foreign Keys To Table

From
"David Johnston"
Date:
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


Re: Add Foreign Keys To Table

From
John R Pierce
Date:
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


Re: Add Foreign Keys To Table

From
Rich Shepard
Date:
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

Re: Add Foreign Keys To Table

From
Rich Shepard
Date:
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

Re: Add Foreign Keys To Table

From
John R Pierce
Date:
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


Re: Add Foreign Keys To Table

From
"David Johnston"
Date:

-----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.




Re: Add Foreign Keys To Table

From
Rich Shepard
Date:
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

Re: Add Foreign Keys To Table

From
"Dr. Tingrong Lu"
Date:
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
>