Foreign key error

From: "paul butler" <polb2(at)blueyonder(dot)co(dot)uk>
To: pgsql-novice(at)postgresql(dot)org
Subject: Foreign key error
Date: 2002-07-18 13:30:18
Message-ID: T5c2a83deeeac1785b32d9@pcow057o.blueyonder.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Dear List,

I'm trying to buld a db using a longish script, but it keeps failing
when creating a foreign key

CREATE TABLE organisations(
orgName varchar(100) NOT NULL PRIMARY KEY,
orgabbreviation varchar(10),
orgDescription text,
orgAims text,
orgAddress varchar(100),
orgaddress2 varchar(100),
orgaddress3 varchar(100),
orgArea varchar(50),
orgCity varchar(50),
orgPostcode varchar(4),
orgPostcode2 varchar(4),
orgTelephone varchar(50),
orgFax varchar(50),
orgEmail varchar(255),
orgWebsite varchar(255),
orgLogin varchar(50),
orgPassword varchar(50),
orgcomment text,
orgNominees varchar(50),
orgUpdate date DEFAULT Now(),
orgOpeningHours varchar(50),
orgPhoneHours varchar(50),
orgDisabledAcess bool DEFAULT 'f' NOT NULL,
orgHomeVisit bool DEFAULT 'f' NOT NULL,
orgAppointReqd bool DEFAULT 'f' NOT NULL,
orgAppoint varchar(50),
orgUsers int2 DEFAULT 0,
Scope varchar(40),
referralcriteria varchar(255),
referralsystem bool DEFAULT 'f');

CREATE TABLE services(
orgName varchar(100) NOT NULL,
servicedetail varchar(30) NOT NULL,
serviceType varchar(40) CHECK (serviceType in
('Education','Training','Employment','Health & Community
Care','Social Support','Information and Advice')) NOT NULL,

--works here

FOREIGN KEY (orgName) REFERENCES organisations
(orgName)ON DELETE CASCADE ON UPDATE CASCADE NOT
DEFERRABLE INITIALLY IMMEDIATE,
PRIMARY KEY (orgName,servicedetail,servicetype));

CREATE TABLE orgactivities(
orgName varchar(100) NOT NULL,
orgactivity varchar(255) NOT NULL,

--Fails here:(removing this line allows the script to complete)

FOREIGN KEY (orgName) REFERENCES organisations
(orgName)ON DELETE CASCADE ON UPDATE CASCADE NOT
DEFERRABLE INITIALLY IMMEDIATE,
PRIMARY KEY (orgName,orgactivity));

And I get a message saying key does not exist in 'organisations'

Alter table commands fail as well

Pg 7.1 I think

Any ideas?

Browse pgsql-novice by date

  From Date Subject
Next Message Derek Barrett 2002-07-18 16:12:16 Headcount: Anyone successfully using PHP/Postgres/AOLServer in production?
Previous Message Henshall, Stuart - WCP 2002-07-18 12:36:49 Re: mirroring databases