By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,811 Members | 1,978 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,811 IT Pros & Developers. It's quick & easy.

database constraints

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
12 Replies


P: n/a
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.org 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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #2

P: n/a
* Ben <be***@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

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

Nov 23 '05 #3

P: n/a


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
---------------------------(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

Nov 23 '05 #4

P: n/a
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.org 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

Nov 23 '05 #5

P: n/a
>>
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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #6

P: n/a
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 da***@fetter.org 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 YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #7

P: n/a
On Wed, Oct 06, 2004 at 08:35:21 -0700,
sk******@commandprompt.com wrote:
* Ben <be***@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);

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

http://archives.postgresql.org

Nov 23 '05 #8

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #9

P: n/a
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

---------------------------(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

Nov 23 '05 #10

P: n/a
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

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

Nov 23 '05 #11

P: n/a

David Garamond <li***@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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #12

P: n/a
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.
_____/ _____/ _/ Co*****@ESI.it

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

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

Nov 23 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.