DB design and foreign keys

Поиск
Список
Период
Сортировка
От Gianluca Riccardi
Тема DB design and foreign keys
Дата
Msg-id 439F0FE8.1050503@moonwatcher.it
обсуждение исходный текст
Ответы Re: DB design and foreign keys  (John McCawley <nospam@hardgeus.com>)
Re: DB design and foreign keys  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: DB design and foreign keys  (Jaime Casanova <systemguards@gmail.com>)
Re: DB design and foreign keys  (Richard Huxton <dev@archonet.com>)
Re: DB design and foreign keys  (Scott Marlowe <smarlowe@g2switchworks.com>)
Re: DB design and foreign keys  (Gianluca Riccardi <ml-reader@moonwatcher.it>)
Список pgsql-sql
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)
NOTNULL,  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
charactervarying(250) NOT NULL,  email2 character varying(250) NOT NULL,  discount1 integer,  discount2 integer,
PRIMARYKEY (customer_code)
 
);

CREATE TABLE users  (  id smallint NOT NULL,  login varchar(20) NOT NULL,  pwd varchar(20) NOT NULL,  name varchar(20)
NOTNULL,  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,
dimensionsvarchar(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
integerREFERENCES customers (customer_code) NOT NULL,  product_code varchar(60) REFERENCES products (code) NOT NULL,
qtyint 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


В списке pgsql-sql по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: lo function changed in PostgreSQL 8.1.1
Следующее
От: John McCawley
Дата:
Сообщение: Re: DB design and foreign keys