From: Gianluca Riccardi <ml-reader@moonwatcher.it>
To: pgsql-sql@postgresql.org
Subject: DB design and foreign keys
Date: Tue, 13 Dec 2005 19:16:08 +0100
Message-ID: <439F0FE8.1050503@moonwatcher.it> (raw)
hello all,
i'm usign PostgreSQL 7.4.7 in a Debian 3.1
following is the SQL schema of my (very)small DB for a (very small)web
business application:
-- SQL schema for business-test-db
CREATE TABLE customers (
customer_code serial,
alfa_customer_code varchar(6),
customer_name character varying(250) NOT NULL,
address character varying(250) NOT NULL,
city character varying(250) NOT NULL,
zip_code character varying(8) NOT NULL,
prov character varying(30) NOT NULL,
security character varying(15) NOT NULL,
tel character varying(30),
tel2 character varying(20) NOT NULL,
fax character varying(250),
url character varying(250),
email1 character varying(250) NOT NULL,
email2 character varying(250) NOT NULL,
discount1 integer,
discount2 integer,
PRIMARY KEY (customer_code)
);
CREATE TABLE users (
id smallint NOT NULL,
login varchar(20) NOT NULL,
pwd varchar(20) NOT NULL,
name varchar(20) NOT NULL,
customer_code int REFERENCES customers (customer_code),
valid date,
primary key (id)
);
CREATE TABLE products (
id serial,
code varchar(60) UNIQUE NOT NULL,
description varchar(250) NOT NULL,
dimensions varchar(250) NOT NULL,
price numeric NOT NULL,
state boolean,
PRIMARY KEY (id)
);
CREATE TABLE orders (
id serial,
order_code serial,
customer_code integer REFERENCES customers (customer_code) NOT NULL,
order_date time without time zone NOT NULL,
remote_ip inet NOT NULL,
order_time timestamp with time zone NOT NULL,
order_type varchar(10) NOT NULL,
state varchar(10) NOT NULL,
PRIMARY KEY (id, order_code)
);
CREATE TABLE order_items (
id serial,
order_code integer REFERENCES orders (order_code) NOT NULL,
customer_code integer REFERENCES customers (customer_code) NOT NULL,
product_code varchar(60) REFERENCES products (code) NOT NULL,
qty int NOT NULL,
price numeric REFERENCES products (price) NOT NULL,
row_price numeric,
PRIMARY KEY (id, order_code)
);
--
-- END OF FILE
the tables: customers, users, products and orders are created as the SQL
states.
when i try to create the table order_items postgresql gives the
following error:
business-test-db=# CREATE TABLE order_items (
business-test-db(# id serial,
business-test-db(# order_code integer REFERENCES orders (order_code)
NOT NULL,
business-test-db(# customer_code integer REFERENCES customers
(customer_code) NOT NULL,
business-test-db(# product_code varchar(60) REFERENCES products
(code) NOT NULL,
business-test-db(# qty int NOT NULL,
business-test-db(# price numeric REFERENCES products (price) NOT NULL,
business-test-db(# row_price numeric,
business-test-db(# PRIMARY KEY (id, order_code)
business-test-db(# );
NOTICE: CREATE TABLE will create implicit sequence "order_items_id_seq"
for "serial" column "order_items.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"order_items_pkey" for table "order_items"
ERROR: there is no unique constraint matching given keys for referenced
table "orders"
business-test-db=#
i'm a RTFM man, but i miss the point from the documentation obviously,
because what i don't understand is why the referenced column isn't
considered to be unique.
More doubts come into play when i see that the referenced key
customers(customer_code) by the referencing table orders gives no errors.
I'm not a native english speaker so probably that gives some more
difficulties.
Thanks in advance to all will contribute a focusing help.
best regards from a proude-to-be postgresql user :-),
Gianluca Riccardi
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ml-reader@moonwatcher.it
Subject: Re: DB design and foreign keys
In-Reply-To: <439F0FE8.1050503@moonwatcher.it>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox