Re: 5.3.5. Foreign Keys (The SQL Language) possible enhance
Re: 5.3.5. Foreign Keys (The SQL Language) possible enhance
От:
Bruce Momjian <bruce@momjian.us>
Дата:
Robert Haas wrote: > On Fri, May 6, 2011 at 9:50 PM, Grzegorz Szpetkowski > 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 http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Re: 5.3.5. Foreign Keys (The SQL Language) possible enhance
От:
Bruce Momjian <bruce@momjian.us>
Дата:
Robert Haas wrote:
> On Fri, May 6, 2011 at 9:50 PM, Grzegorz Szpetkowski
> 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 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
);
! Now it is impossible to create orders with
product_no entries that do not appear in the
products table.
--- 688,694 ----
quantity integer
);
! Now it is impossible to create orders with non-NULL
product_no entries that do not appear in the
products table.
5.3.5. Foreign Keys (The SQL Language) possible enhance
От:
Grzegorz Szpetkowski <gszpetkowski@gmail.com>
Дата:
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
Re: 5.3.5. Foreign Keys (The SQL Language) possible enhance
От:
Robert Haas <robertmhaas@gmail.com>
Дата:
On Fri, May 6, 2011 at 9:50 PM, Grzegorz Szpetkowski 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
Re: 5.3.5. Foreign Keys (The SQL Language) possible enhance
От:
Robert Haas <robertmhaas@gmail.com>
Дата:
On Sun, Sep 11, 2011 at 9:09 PM, Bruce Momjian wrote: > Robert Haas wrote: >> On Fri, May 6, 2011 at 9:50 PM, Grzegorz Szpetkowski >> 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
