Re: Referencing a column from another table in a different

On 19 May 2003, Dan S wrote:

> I am trying to write a foreign key table constraint which references
> another column from a table in another database from within
> PostgreSQL.
>
> Within the database 'rsg_auth', there is a table called 'user_table'
> which has a column (amongst others) called 'username'.
>
> In another database called 'browser perm' I am trying to create the
> following:
>
> CREATE TABLE user_agreement (
> ua_name VARCHAR(16) NOT NULL,
> ua_desc TEXT NOT NULL,
> sat_name VARCHAR(20) DEFAULT '',
> contract VARCHAR(30) DEFAULT '',
> filetype VARCHAR(30) NOT NULL,
> areacode VARCHAR(30) NOT NULL,
> nrt CHAR NOT NULL DEFAULT 'N' CONSTRAINT chkcons_nrt_y_n CHECK
> (nrt IN ('Y', 'N')),
> PRIMARY KEY (ua_name),
> CONSTRAINT fk_uaname_username FOREIGN KEY (ua_name)
> REFERENCES rsg_auth.user_table(username) ON UPDATE CASCADE
> ON DELETE CASCADE
> )
>
> When I submitt it, I get the following error:
> ERROR: parser: parse error at or near "."
>
> Obviously this has something to do with my reference to the other
> table.

You can't do this currently. If they were in separate schemas of the same
database you should be able to do it with something like the above, but
across databases it won't work.