From: dan@binaryfrost.net (Dan S)
Subject: Referencing a column from another table in a different database with
	a foreign key constraint.
Date: 19 May 2003 04:39:10 -0700
Organization: http://groups.google.com/
Lines: 40
Message-ID: <247f0de3.0305190339.6b131f99@posting.google.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
To: pgsql-sql@postgresql.org

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