From: | Rich Shepard <rshepard(at)appl-ecosys(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Table creation syntax |
Date: | 2002-02-19 18:31:14 |
Message-ID: | Pine.LNX.4.44.0202191022440.2118-100000@salmo.appl-ecosys.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I'm try to create these two tables:
CREATE TABLE ap_invoice (
ref_no VARCHAR(20) PRIMARY KEY,
vendorID INT2 NOT NULL REFERENCES vendor (vendorID) ON UPDATE CASCADE ON
DELETE NO ACTION,
invDate DATE NOT NULL DEFAULT CURRENT_DATE,
dueDate DATE NOT NULL,
total_due NUMERIC(8,2) NOT NULL
);
CREATE TABLE ap_invoice_detail (
ref_no VARCHAR(20) REFERENCES ap_invoice (ref_no) ON UPDATE CASCADE ON
DELETE CASCADE,
item INT2 UNIQUE NOT NULL,
expense_acct CHAR(4) NOT NULL,
detDesc VARCHAR(24),
amount NUMERIC(8,2) NOT NULL,
PRIMARY KEY (ref_no, item)
);
When I submit these from within psql, I get an error message:
psql:/home/rshepard/development/accounting/paisley.sql:138: ERROR: UNIQUE
constraint matching given keys for referenced table "ap_invoice" not found
I don't understand where I went wrong here. Is it because the second table
has a multi-column primary key but only one of those columns references the
header table?
What I'm trying to do is have a many-to-one relationship between the
invoice detail table and the invoice header table. All the other tables were
created without error (except for the typos, and I corrected all those).
Please explain the error message so I can correct this one and avoid it in
the future.
Thanks,
Rich
Dr. Richard B. Shepard, President
Applied Ecosystem Services, Inc. (TM)
2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
+ 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard(at)appl-ecosys(dot)com
http://www.appl-ecosys.com
From | Date | Subject | |
---|---|---|---|
Next Message | Culley Harrelson | 2002-02-19 18:52:24 | Re: Report generation |
Previous Message | Rich Shepard | 2002-02-19 18:13:00 | Re: creating tables from a disk file |