Rich Shepard <rshepard@appl-ecosys.com> writes:
> 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
Works fine for me on 7.2, after assuming that
create table vendor (vendorID int2 primary key);
is enough to replicate the missing table.
Are you sure the error message is pointing at this CREATE TABLE and not
a different one?
regards, tom lane