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?