Обсуждение: 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 as > > CREATE 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 as > > > > CREATE 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 as >> > >> > CREATE 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 as > > > > CREATE 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. + diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml new file mode 100644 index c624fc2..ea840fb *** a/doc/src/sgml/ddl.sgml --- b/doc/src/sgml/ddl.sgml *************** CREATE TABLE orders ( *** 688,694 **** quantity integer ); </programlisting> ! Now it is impossible to create orders with <structfield>product_no</structfield> entries that do not appear in the products table. </para> --- 688,694 ---- quantity integer ); </programlisting> ! Now it is impossible to create orders with non-NULL <structfield>product_no</structfield> entries that do not appear in the products table. </para>