Table creation syntax

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

Responses

Browse pgsql-general by date

  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