Search Postgresql Archives

Re: Add Foreign Keys To Table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux