database constraints

[Ben]

If I have have the table:

create table foo
(
a int references bar(id),
b int references baz(id)
)

.... how do I make sure one and only one of the columns a and b are
non-null? Is it even reasonable?
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[David Fetter]

On Wed, Oct 06, 2004 at 08:23:11AM -0700, Ben wrote:

If I have have the table:

create table foo
(
a int references bar(id),
b int references baz(id)
)

... how do I make sure one and only one of the columns a and b are
non-null?
You could write it like this:

CREATE TABLE foo
(
a INT REFERENCES bar(id),
b INT REFERENCES baz(id),
CHECK((a IS NULL AND b IS NOT NULL) OR (a IS NOT NULL AND b IS NULL))
);
Is it even reasonable?

What's "reasonable ?" ;)

BTW, "id" is a terrible name for a column. Better call it foo_id.

Cheers,
D
--
David Fetter da***@fetter.or g http://fetter.org/
phone: [phone] mobile: [phone]

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[sklassen]

* Ben <be***@silentme dia.com> [2004-10-06 08:23:11 -0700]:

If I have have the table:

create table foo
(
a int references bar(id),
b int references baz(id)
)

... how do I make sure one and only one of the columns a and b are
non-null? Is it even reasonable?

ALTER TABLE foo ADD CONSTRAINT must_have_a_or_ b CHECK (a::int IS NULL
AND b::int IS NOT NULL OR a::int IS NOT NULL AND b::int IS NULL);

--
Steven Klassen - Lead Programmer
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Replication & Support Services, [phone]

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Joshua D. Drake]

What's "reasonable ?" ;)

BTW, "id" is a terrible name for a column. Better call it foo_id.
Hello,

I disagree with the idea that "id" is a terrible name for a column. The
only negative to it, is that you will have to be explicit in your
declarations when doing joins and such... ex:

SELECT * FROM foo
JOIN bar on (foo.id = bar.id)

Personally I would rather see, and write that then:

SELECT * FROM foo
JOIN bar on (foo_id = bar_id)
Sincerely,

Joshua D. Drake

Cheers,
D

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
[phone] - jd@commandpromp t.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[David Fetter]

On Wed, Oct 06, 2004 at 09:32:02AM -0700, Joshua D. Drake wrote:

What's "reasonable ?" ;)

BTW, "id" is a terrible name for a column. Better call it foo_id.

Hello,

I disagree with the idea that "id" is a terrible name for a column. The
only negative to it, is that you will have to be explicit in your
declarations when doing joins and such... ex:

SELECT * FROM foo
JOIN bar on (foo.id = bar.id)

Personally I would rather see, and write that then:

SELECT * FROM foo
JOIN bar on (foo_id = bar_id)

With all due respect, Josh, naming your columns with decipherable
names, i.e. *not* having 50 different things called "id" in your db
helps enormously with maintenance, especially when the current
maintainer has never met the designer, a common situation. Also, many
databases have documents that are inadequate, out of date, or both, so
decipherable names, along with as much other self-documentation, is a
big plus.

Cheers,
D

P.S. As a rule, SELECT * doesn't belong in production code.</nit>
--
David Fetter da***@fetter.or g http://fetter.org/
phone: [phone] mobile: [phone]

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Joshua D. Drake]

>>

SELECT * FROM foo
JOIN bar on (foo.id = bar.id)

Personally I would rather see, and write that then:

SELECT * FROM foo
JOIN bar on (foo_id = bar_id)

With all due respect, Josh, naming your columns with decipherable
names, i.e. *not* having 50 different things called "id" in your db
helps enormously with maintenance,

I agree 100%. For us, it makes sense to have id, because if we have
table foo, bar, and baz. We know that the primary key is always, foo.id,
bar.id and baz.id.

especially when the current maintainer has never met the designer, a common situation.
What is what documentation is for.

Also, many
databases have documents that are inadequate, out of date, or both, so
decipherable names, along with as much other self-documentation, is a
big plus.

Agreed. Which is why we make exhaustive use of the comment command :)
Cheers,
D

P.S. As a rule, SELECT * doesn't belong in production code.</nit>

Heh, I agree with this. SELECT * is bad for many, many reasons :). I was
just making an example.

Sincerely,

Joshua D. Drake

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
[phone] - jd@commandpromp t.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[David Fetter]

On Wed, Oct 06, 2004 at 10:07:43AM -0700, Joshua D. Drake wrote:

SELECT * FROM foo
JOIN bar on (foo.id = bar.id)

Personally I would rather see, and write that then:

SELECT * FROM foo
JOIN bar on (foo_id = bar_id)

With all due respect, Josh, naming your columns with decipherable
names, i.e. *not* having 50 different things called "id" in your db
helps enormously with maintenance,

I agree 100%. For us, it makes sense to have id, because if we have
table foo, bar, and baz. We know that the primary key is always, foo.id,
bar.id and baz.id.

Coding standards are a Good Thing(TM). :)

especially when the current maintainer has never met the designer,
a common situation.

What is what documentation is for.

And in a perfect world... ;)

Also, many databases have documents that are inadequate, out of
date, or both, so decipherable names, along with as much other
self-documentation, is a big plus.

Agreed. Which is why we make exhaustive use of the comment command
:)

This brings up an interesting idea. What if it were possible to set
some kind of rules on DDL at database creation time? For example, I'd
like to be able to throw an error if somebody tries to name an object
any of the SQL keywords.

Other possible rules:

* Every table must have a comment
* noCamelCaps
* downcase all entities
* underbar_separa tors_required
* abbrev_w_beg, nt_by_rmvng_vwl s
* Your favorite algorithmic coding standard here

What do y'all think?

Cheers,
D
--
David Fetter da***@fetter.or g http://fetter.org/
phone: [phone] mobile: [phone]

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Bruno Wolff III]

On Wed, Oct 06, 2004 at 08:35:21 -0700,
sk******@comman dprompt.com wrote:

* Ben <be***@silentme dia.com> [2004-10-06 08:23:11 -0700]:

ALTER TABLE foo ADD CONSTRAINT must_have_a_or_ b CHECK (a::int IS NULL
AND b::int IS NOT NULL OR a::int IS NOT NULL AND b::int IS NULL);

A simpler constraint is:
check (a is null <> b is null);

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Joshua D. Drake]

This brings up an interesting idea. What if it were possible to set
some kind of rules on DDL at database creation time? For example, I'd
like to be able to throw an error if somebody tries to name an object
any of the SQL keywords.

Other possible rules:

* Every table must have a comment
* noCamelCaps
* downcase all entities
* underbar_separa tors_required
* abbrev_w_beg, nt_by_rmvng_vwl s
* Your favorite algorithmic coding standard here
Hmmm.. like a PostgreSQL syntax mode... in the PostgreSQL.conf :

syntax_mode = lazy, standard, strict

Where lazy is the current , standard would throw exceptions if you try
to use a reserved word or use uppercase, and strict would do things like
force a comment.

Ooooh... Bastard Operator From Hell here we come.

Sincerely,

From a managerial perspective I like it... Just like you have to be
explicit when using cvs or subversion to NOT comment.

Sincerely,

Joshua D. Drake


What do y'all think?

Cheers,
D

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
[phone] - jd@commandpromp t.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[David Garamond]

Joshua D. Drake wrote:

This brings up an interesting idea. What if it were possible to set
some kind of rules on DDL at database creation time? For example, I'd
like to be able to throw an error if somebody tries to name an object
any of the SQL keywords.

Other possible rules:

* Every table must have a comment
* noCamelCaps
* downcase all entities
* underbar_separa tors_required
* abbrev_w_beg, nt_by_rmvng_vwl s
* Your favorite algorithmic coding standard here

Hmmm.. like a PostgreSQL syntax mode... in the PostgreSQL.conf :

syntax_mode = lazy, standard, strict

Where lazy is the current , standard would throw exceptions if you try
to use a reserved word or use uppercase, and strict would do things like
force a comment.

I'd much prefer if PostgreSQL had a generalized event or trigger system.
That way, one can install a trigger (or a plugin, or whatever) to trap
CREATE TABLE, CREATE VIEW, etc.

IMO, your proposed solution is too specific. There are too many coding
styles that people might want to enforce in their environment.

--
dave

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match