Re: Table creation syntax

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