Re: Add Foreign Keys To Table

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