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?


Wed, 6 Oct 2004 08:23:11 -0700
Ben <bench@silentmedia.com>
database constraints
* Ben <bench@silentmedia.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, (503) 667-4564

Wed, 6 Oct 2004 08:35:21 -0700
sklassen@commandprompt.com
Re: database constraints
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 david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

Wed, 6 Oct 2004 08:38:49 -0700
David Fetter <david@fetter.org>
Re: database constraints
> 
> 
> 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.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
Wed, 06 Oct 2004 09:32:02 -0700
"Joshua D. Drake" <jd@commandprompt.com>
Re: database constraints
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 david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

Wed, 6 Oct 2004 09:43:56 -0700
David Fetter <david@fetter.org>
Re: database constraints
>>
>>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.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
Wed, 06 Oct 2004 10:07:43 -0700
"Joshua D. Drake" <jd@commandprompt.com>
Re: database constraints
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_separators_required
* abbrev_w_beg, nt_by_rmvng_vwls
* Your favorite algorithmic coding standard here

What do y'all think?

Cheers,
D
-- 
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

Wed, 6 Oct 2004 10:35:43 -0700
David Fetter <david@fetter.org>
Re: database constraints
On Wed, Oct 06, 2004 at 08:35:21 -0700,
  sklassen@commandprompt.com wrote:
> * Ben <bench@silentmedia.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);

Wed, 6 Oct 2004 12:48:16 -0500
Bruno Wolff III <bruno@wolff.to>
Re: database constraints
> 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_separators_required
> * abbrev_w_beg, nt_by_rmvng_vwls
> * 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.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
Wed, 06 Oct 2004 11:23:48 -0700
"Joshua D. Drake" <jd@commandprompt.com>
Re: database constraints
David Fetter wrote:
>>>BTW, "id" is a terrible name for a column.  Better call it foo_id.
>>
>>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.

With all due respect, David, everybody is entitled to his own opinion 
and yours is not the absolute truth. Column and table naming is not 
exact science. Naming every single-column PK as "id" has advantages over 
"<tablename>_id":

- you instantly know that "id" is PK;

- renaming tables does not need to a PITA (to be consistent with the 
above "<table>_name" scheme you would have to rename all the column 
names too).

- it's shorter;

- etc.

And besides, what is exactly the advantage of having unique/prefixed 
column names across all tables (aside from being able to use JOIN USING 
and NATURAL JOIN syntax)? Every column name can be fully qualified with 
their table names (and their schema name) anyway. And it's unlikely that 
someone who is familiar with computing could misunderstand "id".

Personally, I like column names to be short and to the point. Instead 
of: CREATE TABLE person(person_id ..., person_name ..., person_dob ...) 
I prefer CREATE TABLE person(id ..., name ..., dob ...). It matches the 
way I name my Perl/Python/Ruby/etc. classes' attributes.

Of course, everyone can choose his or her own style.

-- 
dave

Thu, 07 Oct 2004 11:43:56 +0700
David Garamond <lists@zara.6.isreserved.com>
Re: database constraints
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_separators_required
>> * abbrev_w_beg, nt_by_rmvng_vwls
>> * 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

Thu, 07 Oct 2004 11:47:55 +0700
David Garamond <lists@zara.6.isreserved.com>
Re: database constraints

David Garamond <lists@zara.6.isreserved.com> writes:

> With all due respect, David, everybody is entitled to his own opinion and
> yours is not the absolute truth. Column and table naming is not exact science.

Sure. But let me put another big vote in favour against the "id" naming scheme
and for the "table_id" naming scheme.

> And besides, what is exactly the advantage of having unique/prefixed column
> names across all tables (aside from being able to use JOIN USING and NATURAL
> JOIN syntax)? Every column name can be fully qualified with their table names
> (and their schema name) anyway. And it's unlikely that someone who is familiar
> with computing could misunderstand "id".

Sure when you're just comparing simple queries like:

  select * from foo,bar where foo.bar_id = bar.bar_id
  select * from foo,bar where foo.bar_id = bar.id

there doesn't seem to be much difference. Though the ability to use USING instead
of ON sure does clean things up quite a bit:

 select * from foo join bar USING (bar_id)

However, when you have more complex queries things are not so clear. Once you
have a couple levels of subqueries with joins in them it's no longer so clear
what "subquery1.id" is any more. For that matter there could be two or three
"id" columns in the subquery that you would like to refer to.

I've found that in the long run I saved a whole lot of time and energy by
religiously going around enforcing a "one name" policy. My database column
names for the same data type always match, the application variables match the
database column names, and the other layers (html forms) always match the
database column names and application variables. The only exceptions are when
I have to distinguish which of several relationships the other foreign key
bears.

> Personally, I like column names to be short and to the point. Instead of:
> CREATE TABLE person(person_id ..., person_name ..., person_dob ...) I prefer
> CREATE TABLE person(id ..., name ..., dob ...). It matches the way I name my
> Perl/Python/Ruby/etc. classes' attributes.

I do agree about avoiding things like person_name, person_dob etc. Though
sometimes it's handy just for avoiding keywords like "desc", "user", etc.

-- 
greg


07 Oct 2004 05:30:25 -0400
Greg Stark <gsstark@mit.edu>
Re: database constraints
On Thu, 7 Oct 2004, David Garamond wrote:

> With all due respect, David, everybody is entitled to his own opinion and 
> yours is not the absolute truth. Column and table naming is not exact 
> science. Naming every single-column PK as "id" has advantages over 
> "<tablename>_id":
>
> - you instantly know that "id" is PK;

You mean if you're looking at table "X" it takes time to you to 
identify "X_id" as the PK? I don't get why just "id" is better in
this respect.

> - renaming tables does not need to a PITA (to be consistent with the above 
> "<table>_name" scheme you would have to rename all the column names too).

(1), see below.

>
> - it's shorter;

Agreed. But is shorter "better"? How about writing a program using
"a", "b", .. "aa", "ab", .. "xyz" as variable names? Isn't it shorter?

> - etc.

I think I can list this one among the advantages of "<tablename>_id"
as well. :-)


> And besides, what is exactly the advantage of having unique/prefixed column 
> names across all tables (aside from being able to use JOIN USING and NATURAL 
> JOIN syntax)? Every column name can be fully qualified with their table names 
> (and their schema name) anyway. And it's unlikely that someone who is 
> familiar with computing could misunderstand "id".
>
> Personally, I like column names to be short and to the point. Instead of: 
> CREATE TABLE person(person_id ..., person_name ..., person_dob ...) I prefer 
> CREATE TABLE person(id ..., name ..., dob ...). It matches the way I name my 
> Perl/Python/Ruby/etc. classes' attributes.
>
> Of course, everyone can choose his or her own style.

The advantage is that you can use the same name for both the PK column
and any FK columns that reference to it. It's useful for JOINs but not
only. If you're using any FK, your (1) doesn't hold for "id" as well:
you'll have to rename FKs anyway.

Since you have to somehow qualify FKs, there's no reason not to use the
same name for PKs in their own table.

Using the same name for the same object (and different names for
different objects) comes NATURAL. :-)

NATURAL JOINs are only part of the problem. When joining two tables,
you may want to know which column comes from which table, in the resulting
one. Sure, you can rename them as in:

SELECT a.comment as a_comment, b.comment as b_comment FROM a JOIN b;

so that in the resulting table you can tell which is which.
But, doesn't the need to _rename_ things hint about a naming problem? :-)

Of course, I'm not stating the "<tablename>_id" is your best, your last
and your only naming convention. It's just one. A decent one. Having
_no_ naming convention is a little disaster.

.TM.
-- 
       ____/  ____/   /
      /      /       /			Marco Colombo
     ___/  ___  /   /		      Technical Manager
    /          /   /			 ESI s.r.l.
  _____/ _____/  _/		       Colombo@ESI.it

Thu, 7 Oct 2004 13:53:17 +0200 (CEST)
Marco Colombo <pgsql@esiway.net>
Re: database constraints
On Thu, Oct 07, 2004 at 11:47:55AM +0700, David Garamond wrote:
> 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_separators_required
> >>* abbrev_w_beg, nt_by_rmvng_vwls
> >>* 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.

Forwarding to -hackers...

Folks,

How big a project would it be to have generalized DDL triggers?  Apart
from resource allocation, what are some downsides of providing such a
facility?

Cheers,
D
-- 
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

Thu, 7 Oct 2004 12:40:38 -0700
David Fetter <david@fetter.org>
DDL triggers [was Re: database constraints]
David Fetter <david@fetter.org> writes:
> How big a project would it be to have generalized DDL triggers?  Apart
> from resource allocation, what are some downsides of providing such a
> facility?

BEFORE triggers on the system catalogs are Right Out.  In principle we
could support AFTER triggers, which would only fire after the catalog
operation is fully complete (but before it's committed of course).  So
you could have a veto, but not really affect the system behavior
on-the-fly.  See past discussions in the pghackers archives.

			regards, tom lane

Thu, 07 Oct 2004 16:43:53 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: DDL triggers [was Re: database constraints] 
On Thu, 7 Oct 2004, David Fetter wrote:

> On Thu, Oct 07, 2004 at 11:47:55AM +0700, David Garamond wrote:
> > 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_separators_required
> > >>* abbrev_w_beg, nt_by_rmvng_vwls
> > >>* 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.
>
> Forwarding to -hackers...
>
> Folks,
>
> How big a project would it be to have generalized DDL triggers?  Apart
> from resource allocation, what are some downsides of providing such a
> facility?

I discussed this earlier in the year on hackers (say, Jan or Feb). I did
not have any time to do this for 8.0 but there is discussion and I believe
Bruce added a TODO item.

>
> Cheers,
> D

Gavin

Fri, 8 Oct 2004 09:48:29 +1000 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: [HACKERS] DDL triggers [was Re: database constraints]