foreign key constraint

From: Dennis Gearon <gearond(at)cvc(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: foreign key constraint
Date: 2003-03-04 16:01:50
Message-ID: 84BFAMHD8085ZXW2Y713Z1Z6Z75NQN.3e64cdee@cal-lab
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Is there any way to add a foreign key constraint after a table is created? I got this error
-----------------
PostgreSQL said: ERROR: parser: parse error at or near "FOREIGN"
Your query:
ALTER TABLE testUsrEmails ADD CONSTRAINT FOREIGN KEY (usr_id) REFERENCES testUsrs (usr_id)
-----------------

OR, how is it possible to add foreign key constraints, ('cross constraints'), between two tables.
I tried it in a transaction, but it didn't work. The first constraint in the table definition
was DEFERRABLE INITIALLY DEFERRED, but I don't think the parser cared :-) I got this error:

-----------------
PostgreSQL said: ERROR: Relation "testusremails" does not exist (it's the next table in the
creation list)
Your query:
CREATE TABLE testUsrs(
usr_id serial NOT NULL PRIMARY KEY,
usr_email_id_pri int4 NOT NULL,
login varchar(32) NOT NULL UNIQUE,
hashed_pw text NOT NULL,
sur_name text NOT NULL,
first_name text NOT NULL,
middle_name text DEFAULT 'none' NOT NULL,
created timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
FOREIGN KEY (usr_email_id_pri) REFERENCES testUsrEmails (usr_email_id) DEFERRABLE INITIALLY
DEFERRED);
-----------------

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2003-03-04 16:14:58 Re: foreign key constraint
Previous Message Teodor Sigaev 2003-03-04 15:26:09 Re: tsearch trouble REPOST