public inbox for [email protected]
help / color / mirror / Atom feed5.3.5. Foreign Keys (The SQL Language) possible enhance
5+ messages / 3 participants
[nested] [flat]
* 5.3.5. Foreign Keys (The SQL Language) possible enhance
@ 2011-05-07 01:50 Grzegorz Szpetkowski <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Grzegorz Szpetkowski @ 2011-05-07 01:50 UTC (permalink / raw)
To: pgsql-docs
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 5.3.5. Foreign Keys (The SQL Language) possible enhance
@ 2011-05-19 20:17 Robert Haas <[email protected]>
parent: Grzegorz Szpetkowski <[email protected]>
0 siblings, 2 replies; 5+ messages in thread
From: Robert Haas @ 2011-05-19 20:17 UTC (permalink / raw)
To: Grzegorz Szpetkowski <[email protected]>; +Cc: pgsql-docs
On Fri, May 6, 2011 at 9:50 PM, Grzegorz Szpetkowski
<[email protected]> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 5.3.5. Foreign Keys (The SQL Language) possible enhance
@ 2011-09-12 02:09 Bruce Momjian <[email protected]>
parent: Robert Haas <[email protected]>
1 sibling, 1 reply; 5+ messages in thread
From: Bruce Momjian @ 2011-09-12 02:09 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Grzegorz Szpetkowski <[email protected]>; pgsql-docs
Robert Haas wrote:
> On Fri, May 6, 2011 at 9:50 PM, Grzegorz Szpetkowski
> <[email protected]> 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 <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 5.3.5. Foreign Keys (The SQL Language) possible enhance
@ 2011-09-15 11:42 Robert Haas <[email protected]>
parent: Bruce Momjian <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Robert Haas @ 2011-09-15 11:42 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; +Cc: Grzegorz Szpetkowski <[email protected]>; pgsql-docs
On Sun, Sep 11, 2011 at 9:09 PM, Bruce Momjian <[email protected]> wrote:
> Robert Haas wrote:
>> On Fri, May 6, 2011 at 9:50 PM, Grzegorz Szpetkowski
>> <[email protected]> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 5.3.5. Foreign Keys (The SQL Language) possible enhance
@ 2011-10-12 20:59 Bruce Momjian <[email protected]>
parent: Robert Haas <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: Bruce Momjian @ 2011-10-12 20:59 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: Grzegorz Szpetkowski <[email protected]>; pgsql-docs
Robert Haas wrote:
> On Fri, May 6, 2011 at 9:50 PM, Grzegorz Szpetkowski
> <[email protected]> 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 <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Attachments:
[text/x-diff] /rtmp/null (660B, 2-%2Frtmp%2Fnull)
download | inline diff:
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>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-10-12 20:59 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-07 01:50 5.3.5. Foreign Keys (The SQL Language) possible enhance Grzegorz Szpetkowski <[email protected]>
2011-05-19 20:17 ` Robert Haas <[email protected]>
2011-09-12 02:09 ` Bruce Momjian <[email protected]>
2011-09-15 11:42 ` Robert Haas <[email protected]>
2011-10-12 20:59 ` Bruce Momjian <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox