Thread: database constraints
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?
* 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
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!
Use a post trigger function, ON UPDATE, INSERT which essentially has
this in it:
if ( ISNULL(new.a) AND ISNULL(new.b) ){ RAISE NOTICE "blah blah"; }
I work with PHP a lot, just a little plpgsql, so, the grammar may be
wrong above.
Ben <bench@silentmedia.com> wrote:
<quote --------------------------------------------------------------->
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?
</quote -------------------------------------------------------------->
>
>
> 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
Attachment
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!
>> >>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
Attachment
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!
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);
> 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
Attachment
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
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
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
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
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!
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
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