5.3.5. Foreign Keys (The SQL Language) possible enhance
I have some remark about
"Now it is impossible to create orders with product_no entries that do
not appear in the products table."
http://www.postgresql.org/docs/9.0/static/ddl-constraints.html#DDL-CONSTRAINTS-FK
Let' see:
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
CREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products (product_no),
quantity integer
);
INSERT INTO products VALUES (1, 'Bosch vacuum cleaner', 10);
INSERT INTO orders VALUES (1, 1, 1);
INSERT INTO orders VALUES (2, NULL, 5);
There is still possibility to add product_no (exactly NULL) value,
which does not appear (cannot because of primary key nature) in
products table. To get "full solution" you need create orders table as
CREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products (product_no) NOT NULL,
quantity integer
);
Regards,
Grzegorz Szpetkowski
On Fri, May 6, 2011 at 9:50 PM, Grzegorz Szpetkowski
<gszpetkowski@gmail.com> wrote:
I have some remark about
"Now it is impossible to create orders with product_no entries that do
not appear in the products table."http://www.postgresql.org/docs/9.0/static/ddl-constraints.html#DDL-CONSTRAINTS-FK
[...]
There is still possibility to add product_no (exactly NULL) value,
which does not appear (cannot because of primary key nature) in
products table. To get "full solution" you need create orders table asCREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products (product_no) NOT NULL,
quantity integer
);
I don't think we should change the example, but we could probably
clarify the wording.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas wrote:
On Fri, May 6, 2011 at 9:50 PM, Grzegorz Szpetkowski
<gszpetkowski@gmail.com> wrote:I have some remark about
"Now it is impossible to create orders with product_no entries that do
not appear in the products table."http://www.postgresql.org/docs/9.0/static/ddl-constraints.html#DDL-CONSTRAINTS-FK
[...]
There is still possibility to add product_no (exactly NULL) value,
which does not appear (cannot because of primary key nature) in
products table. To get "full solution" you need create orders table asCREATE TABLE orders (
? ?order_id integer PRIMARY KEY,
? ?product_no integer REFERENCES products (product_no) NOT NULL,
? ?quantity integer
);I don't think we should change the example, but we could probably
clarify the wording.
Any ideas on how to clarify the wording?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On Sun, Sep 11, 2011 at 9:09 PM, Bruce Momjian <bruce@momjian.us> wrote:
Robert Haas wrote:
On Fri, May 6, 2011 at 9:50 PM, Grzegorz Szpetkowski
<gszpetkowski@gmail.com> wrote:I have some remark about
"Now it is impossible to create orders with product_no entries that do
not appear in the products table."http://www.postgresql.org/docs/9.0/static/ddl-constraints.html#DDL-CONSTRAINTS-FK
[...]
There is still possibility to add product_no (exactly NULL) value,
which does not appear (cannot because of primary key nature) in
products table. To get "full solution" you need create orders table asCREATE TABLE orders (
? ?order_id integer PRIMARY KEY,
? ?product_no integer REFERENCES products (product_no) NOT NULL,
? ?quantity integer
);I don't think we should change the example, but we could probably
clarify the wording.Any ideas on how to clarify the wording?
Maybe something like this:
Now every product_no that appears in the orders table must also appear
in the products table. Foreign key constraints are not checked for
NULL values, so product_no may also be NULL; we could prohibit this by
declaring the column NOT NULL.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas wrote:
On Fri, May 6, 2011 at 9:50 PM, Grzegorz Szpetkowski
<gszpetkowski@gmail.com> wrote:I have some remark about
"Now it is impossible to create orders with product_no entries that do
not appear in the products table."http://www.postgresql.org/docs/9.0/static/ddl-constraints.html#DDL-CONSTRAINTS-FK
[...]
There is still possibility to add product_no (exactly NULL) value,
which does not appear (cannot because of primary key nature) in
products table. To get "full solution" you need create orders table asCREATE TABLE orders (
? ?order_id integer PRIMARY KEY,
? ?product_no integer REFERENCES products (product_no) NOT NULL,
? ?quantity integer
);I don't think we should change the example, but we could probably
clarify the wording.
Done with the attached patch.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +