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]

Re: database constraints

On Wed, Oct 06, 2004 at 08:23:11AM -0700, Ben wrote:[color=blue]
> 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?[/color]

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))
);
[color=blue]
> Is it even reasonable?[/color]

What's "reasonable ?" ;)

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

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

Remember to vote!

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

[sklassen@commandprompt.com]

Re: database constraints

* Ben <bench@silentme dia.com> [2004-10-06 08:23:11 -0700]:
[color=blue]
> 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?[/color]

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

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

[Joshua D. Drake]

Re: database constraints

[color=blue]
>
>
> What's "reasonable ?" ;)
>
> BTW, "id" is a terrible name for a column. Better call it foo_id.[/color]

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


[color=blue]
>
> Cheers,
> D[/color]


--
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@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]

Re: database constraints

On Wed, Oct 06, 2004 at 09:32:02AM -0700, Joshua D. Drake wrote:
[color=blue][color=green]
> >What's "reasonable ?" ;)
> >
> >BTW, "id" is a terrible name for a column. Better call it foo_id.[/color]
>
> 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)[/color]

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.or g http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

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

[Joshua D. Drake]

Re: database constraints

>>[color=blue][color=green]
>>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)[/color]
>
>
> 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,[/color]

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[color=blue]
> maintainer has never met the designer, a common situation.[/color]

What is what documentation is for.

[color=blue]
> 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.[/color]


Agreed. Which is why we make exhaustive use of the comment command :)
[color=blue]
> Cheers,
> D
>
> P.S. As a rule, SELECT * doesn't belong in production code.</nit>[/color]

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@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 majordomo@postg resql.org)

[David Fetter]

Re: database constraints

On Wed, Oct 06, 2004 at 10:07:43AM -0700, Joshua D. Drake wrote:[color=blue][color=green][color=darkred]
> >>
> >>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)[/color]
> >
> >
> >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,[/color]
>
> 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.[/color]

Coding standards are a Good Thing(TM). :)
[color=blue][color=green]
> >especially when the current maintainer has never met the designer,
> >a common situation.[/color]
>
> What is what documentation is for.[/color]

And in a perfect world... ;)
[color=blue][color=green]
> >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.[/color]
>
> Agreed. Which is why we make exhaustive use of the comment command
> :)[/color]

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 david@fetter.or g http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

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 majordomo@postg resql.org)

[Bruno Wolff III]

Re: database constraints

On Wed, Oct 06, 2004 at 08:35:21 -0700,
sklassen@comman dprompt.com wrote:[color=blue]
> * Ben <bench@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);[/color]

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]

Re: database constraints

[color=blue]
> 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[/color]

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



[color=blue]
>
> What do y'all think?
>
> Cheers,
> D[/color]


--
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@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 majordomo@postg resql.org)

[David Garamond]

Re: database constraints

Joshua D. Drake wrote:[color=blue][color=green]
>> 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[/color]
>
> 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.[/color]

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

[David Garamond]

Re: database constraints

David Fetter wrote:[color=blue][color=green][color=darkred]
>>>BTW, "id" is a terrible name for a column. Better call it foo_id.[/color]
>>
>>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
>>declaration s 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)[/color]
>
> 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.[/color]

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>_na me" 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_i d ..., 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

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

[Greg Stark]

Re: database constraints


David Garamond <lists@zara.6.i sreserved.com> writes:
[color=blue]
> 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.[/color]

Sure. But let me put another big vote in favour against the "id" naming scheme
and for the "table_id" naming scheme.
[color=blue]
> 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".[/color]

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.
[color=blue]
> Personally, I like column names to be short and to the point. Instead of:
> CREATE TABLE person(person_i d ..., 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.[/color]

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


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

http://archives.postgresql.org

[Marco Colombo]

Re: database constraints

On Thu, 7 Oct 2004, David Garamond wrote:
[color=blue]
> 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;[/color]

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.
[color=blue]
> - renaming tables does not need to a PITA (to be consistent with the above
> "<table>_na me" scheme you would have to rename all the column names too).[/color]

(1), see below.
[color=blue]
>
> - it's shorter;[/color]

Agreed. But is shorter "better"? How about writing a program using
"a", "b", .. "aa", "ab", .. "xyz" as variable names? Isn't it shorter?
[color=blue]
> - etc.[/color]

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

[color=blue]
> 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_i d ..., 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.[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html