Thread: 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