From: | Rich Shepard <rshepard(at)appl-ecosys(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Add Foreign Keys To Table |
Date: | 2011-07-07 17:40:11 |
Message-ID: | alpine.LNX.2.00.1107071032330.20399@salmo.appl-ecosys.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alan Hodgson | 2011-07-07 18:19:56 | Re: Add Foreign Keys To Table |
Previous Message | Rick Genter | 2011-07-07 17:39:48 | Re: Insufficient privileges. |