Re: Table creation syntax

Rich Shepard <rshepard(at)appl-ecosys(dot)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