Re: Add Foreign Keys To Table

From: Dr. Tingrong Lu
Subject: Re: Add Foreign Keys To Table
Date: ,
Msg-id: BLU165-ds1072240CCF02AFE96E9017D6400@phx.gbl
(view: Whole thread, Raw)
In response to: Add Foreign Keys To Table  (Rich Shepard)
List: pgsql-general

Tree view

Add Foreign Keys To Table  (Rich Shepard, )
 Re: Add Foreign Keys To Table  (Alan Hodgson, )
  Re: Add Foreign Keys To Table  (Rich Shepard, )
   Re: Add Foreign Keys To Table  (Alan Hodgson, )
    Re: Add Foreign Keys To Table  (Rich Shepard, )
     Re: Add Foreign Keys To Table  (Alan Hodgson, )
      Re: Add Foreign Keys To Table  (Rich Shepard, )
       Re: Add Foreign Keys To Table  (John R Pierce, )
        Re: Add Foreign Keys To Table  (Rich Shepard, )
     Re: Add Foreign Keys To Table  ("David Johnston", )
 Re: Add Foreign Keys To Table  (John R Pierce, )
  Re: Add Foreign Keys To Table  (Rich Shepard, )
 Re: Add Foreign Keys To Table  ("David Johnston", )
 Re: Add Foreign Keys To Table  ("Dr. Tingrong Lu", )

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" <>
Sent: Friday, July 08, 2011 1:40 AM
To: <>
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 ()
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


pgsql-general by date:

From: Darren Duncan
Date:
Subject: Re: [HACKERS] Creating temp tables inside read only transactions
From: "Albe Laurenz"
Date:
Subject: Re: Insufficient privileges.