Postgres Pro
Downloads
Home   >   mailing lists

Thread: Table creation syntax

Table creation syntax

From
Rich Shepard
Date:
  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@appl-ecosys.com
                         http://www.appl-ecosys.com

Re: Table creation syntax

From
Tom Lane
Date:
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

Re: Table creation syntax

From
Rich Shepard
Date:
On Tue, 19 Feb 2002, Tom Lane wrote:

> 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?

Tom,

  Yes, it was pointing to the last line of the second table. I'm using 7.2,
too, btw. I removed the REFERENCES line and just put it back. Now it works
just fine. Of course, I tried it at least a half-dozen times before writing
and kept getting the same error. Sigh.

Many thanks for your patience and help,

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@appl-ecosys.com
                         http://www.appl-ecosys.com