Re: DB design and foreign keys - Mailing list pgsql-sql

Table orders defines the column order_code as a serial, which simple 
makes a trigger which gives a new value to the column on insert.  Note 
that there is NO guarantee that ths column will be unique.  You can 
manually update the value to whatever you want.  If you wish this column 
to be unique, you must specify it on creation, or later do an alter 
table add constraint to the column.

A foreign key requires that the referenced column be unique (DB 
enforced, not just coincidentally unique), and that' s why your table 
creation is failing.

Gianluca Riccardi wrote:

> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster