Обсуждение: DB design and foreign keys

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

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

Gianluca Riccardi <ml-reader@moonwatcher.it> writes:
> 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)
> );

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

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

order_code is not by itself unique --- SERIAL doesn't guarantee that.
I'm not sure why you are declaring the primary key of orders as being
the combination of *two* serial columns, but if that's what you really
need and you also want to be able to reference a row by just one of
them, you'll need to apply a separate unique constraint to just the
order_code column.
        regards, tom lane

[...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)

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

Gianluca Riccardi wrote:
> hello all,
> i'm usign PostgreSQL 7.4.7 in a Debian 3.1
> 

> CREATE TABLE orders  (
>   id serial,
>   order_code serial,
...
>   PRIMARY KEY (id, order_code)
> );
> 
> CREATE TABLE order_items (
>   id serial,
>   order_code integer REFERENCES orders (order_code) NOT NULL,

> when i try to create the table order_items postgresql gives the 
> following error:

> ERROR:  there is no unique constraint matching given keys for referenced 
> table "orders"

It means what it says. You have defined table orders with a primary key 
of (id,order_code). This means that the combination of (id,order_code) 
must be unique. So - these could all exist at the same time: (1,1), (1,2), (2,1), (2,2)
You could not then add another (1,2) combination.

Since id and order_code are both just automatically-generated numbers in 
the orders table it doesn't add anything to make both of them part of a 
primary-key. I would delete the id column altogether and just have the 
order_code as the primary-key (since "order_code" carries more meaning 
to a human than "id"). This means your order_items table can then safely 
reference the order_code it wants to.

HTH
--   Richard Huxton  Archonet Ltd

On Tue, 2005-12-13 at 12:16, 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:

> 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)
> );


Given this table layout, I'm gonna take a wild guess and ask if you're
coming from MySQL and expecting the second serial order_code to be a
sub-autoincrement to id?  If so, it won't be.  That's a mysqlism.  If
you want something similar, you'll have to implement it yourself, and
note that such a thing tends to be a poor performer with lots of
parallel updates, and it can also be susceptible to race conditions if
no locking is used.

[cut]

> order_code is not by itself unique --- SERIAL doesn't guarantee that.
>  
>
that was my misunderstanding, i thought (misunderstood) that 'serial' 
implied 'unique'

> I'm not sure why you are declaring the primary key of orders as being
> the combination of *two* serial columns,

i thought it was good design choice and even needed for foreign keys 
referencing on them

> but if that's what you really
> need
>
i'm not shure about that

> and you also want to be able to reference a row by just one of
> them, you'll need to apply a separate unique constraint to just the
> order_code column.
>  
>
sorry, i'm afraid i didn't understand: are you suggesting to apply the 
constraint in case the primary key is kept on the combination of the two 
serial columns? or to remove the primary key of the two serial columns?

>             regards, tom lane
>  
>
thanks a lot for your kind response, best regards,
Gianluca Riccardi

John McCawley wrote:

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


[cut]

that was my misunderstanding

thanks for your reply,
Gianluca Riccardi

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

[cut]

>
> It means what it says. You have defined table orders with a primary 
> key of (id,order_code). This means that the combination of 
> (id,order_code) must be unique. 


yes, that was my thought, and in that context, i thought it could be 
correct in order to have uniqueness for creating foreign keys

> So - these could all exist at the same time:
>  (1,1), (1,2), (2,1), (2,2)
> You could not then add another (1,2) combination.


yes, again, i thought that was the uniqueness i needed...

>
> Since id and order_code are both just automatically-generated numbers 
> in the orders table it doesn't add anything to make both of them part 
> of a primary-key. I would delete the id column altogether and just 
> have the order_code as the primary-key (since "order_code" carries 
> more meaning to a human than "id"). This means your order_items table 
> can then safely reference the order_code it wants to.



> HTH


sure it did, thanks for your response, best regards,
Gianluca Riccardi

[cut]

> Given this table layout, I'm gonna take a wild guess and ask if you're
> coming from MySQL and expecting the second serial order_code to be a
> sub-autoincrement to id?
>
no, always used PostgreSQL, but i'm having a deeper approach now, until 
now i've been using th ORDBMS in a very 'easy' manner :(

>  If so, it won't be.  That's a mysqlism.  If
> you want something similar, you'll have to implement it yourself, and
> note that such a thing tends to be a poor performer with lots of
> parallel updates, and it can also be susceptible to race conditions if
> no locking is used.
>  
>
thank you, regards,
Gianluca Riccardi

so, after the needed modifications the SQL schema is the following

-- SQL schema for business-test-db2

CREATE TABLE customers (  customer_code serial UNIQUE,  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
charactervarying(8) NOT NULL,  prov character varying(30) NOT NULL,  security character varying(15) NOT NULL,  tel
charactervarying(30),  tel2 character varying(20) NOT NULL,  fax character varying(250),  url character varying(250),
email1character 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)
NOTNULL,  customer_code int REFERENCES customers (customer_code),  valid date,  primary key (id)
 
);

CREATE TABLE products   (  code varchar(60) UNIQUE NOT NULL,  description varchar(250) NOT NULL,  dimensions
varchar(250)NOT NULL,  price numeric NOT NULL,  state boolean,  PRIMARY KEY (code)
 
);

CREATE TABLE orders  (  order_code serial UNIQUE NOT NULL,  customer_code integer REFERENCES customers (customer_code)
NOTNULL,  order_date time without time zone NOT NULL,  remote_ip inet NOT NULL,  order_time timestamp with time zone
NOTNULL,  order_type varchar(10) NOT NULL,  state varchar(10) NOT NULL,  PRIMARY KEY (order_code)
 
);

CREATE TABLE order_items (  order_code integer REFERENCES orders (order_code) NOT NULL,  customer_code integer
REFERENCEScustomers (customer_code) NOT NULL,  product_code varchar(60) REFERENCES products (code) NOT NULL,  qty int
NOTNULL,  price numeric NOT NULL,  row_price numeric
 
);

--
-- END OF FILE

the order_items table is:

business-test-db2=# \d order_items            Tabella "public.order_items"   Colonna         |         Tipo
   | Modificatori
 
-------------------+-------------------------+--------------order_code       | integer                      | not
nullcustomer_code| integer                      | not nullproduct_code   | character varying(60) | not nullqty
       | integer                      | not nullprice                | numeric                    | not nullrow_price
    | numeric                    |
 
Vincoli di integrità referenziale   "$1" FOREIGN KEY (order_code) REFERENCES orders(order_code)   "$2" FOREIGN KEY
(customer_code)REFERENCES customers(customer_code)   "$3" FOREIGN KEY (product_code) REFERENCES products(code)
 

business-test-db2=#



thanks a lot, you all pointed me out of misconceptualized position.

best ragards all,
Gianluca Riccardi

p.s.

Colonna = column
Tipo = type
Modificatori = modifiers
Vincoli di integrita' referenziale = referential integrity constraints