Re: DB design and foreign keys

Jaime Casanova wrote:

> [...unnecesary...]
>  
>
>> 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)
>>   
>
>                            ^^^^^^^^^^^^^^^^^^^
>  
>
>> );
>>   
>
> [...unnecesary...]
>  
>
>> CREATE TABLE order_items (
>>  id serial,
>>  order_code integer REFERENCES orders (order_code) NOT NULL,
>>   
>
>                                                                  
> ^^^^^^^^^^^^^^
> [...unnecesary...]
>  
>

>> ERROR:  there is no unique constraint matching given keys for referenced
>> table "orders"
>>   
>
>
> this is because the PK in the orders table has two fields not one...
> so it founds no unique index on orders(order_code)
>
>  
>
then, what would you suggest, to remove the primary key from the 'id' 
column? or to remove the 'id' column at all?

>  
>
> -- 
> Atentamente,
> Jaime Casanova
> (DBA: DataBase Aniquilator ;)
>  
>

thank you for your response, regards,
Gianluca Riccardi