Thread: Referencing a column from another table in a different database with a foreign key constraint.

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.

Can anyone put my syntax straight please?

Thanks in advance.

-- 

Mr. Daniel J. Smale
Remote Sensing Group
Plymouth Marine Laboratory

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.