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 12 1544
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
* 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
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
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
>> 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)
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)
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 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)
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
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)
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Ruby Tuesdays |
last post by:
Perhaps you database guru able to suggest what would be a good choice for
opensource database platform to use to develop projects.
At the moment the project is small/medium, but it will grow in...
|
by: Rajesh Kapur |
last post by:
We use InnoDB tables and foreign key constraints extensively. The mysqldump
backs up the database tables in alphabetical order with foreign key
constraints defined in the create statement of each...
|
by: Mike Nau |
last post by:
We currently have all of our schema and testdata laid out in a large
set of sql scripts.
It currently takes about 15 minutes to run the scripts on a Dual
1.7ghz box with 1gb of ram.
Does...
|
by: serge |
last post by:
I have all the scrips to create a database. I have a few questions:
1- I am creating a batch file that it will call many lines like:
db2 -td@ -f filename.sql -z output.txt
The order i am using...
|
by: Martin Pritchard |
last post by:
Hi,
I'm working on a project that historically contains around 40 enums. In the
database various fields refer to the int values of these enums, but of
course ref integrity is not enofrced and...
|
by: Bill Moran |
last post by:
Hello again,
I'm developing a program based on PostgreSQL. It's consists of tables,
constraints, _many_ stored procedures, 56M of test data, and a client
app written in C.
To help me with...
|
by: sarada7 |
last post by:
How to check if DB Constraints are enabled in a database?
|
by: nyathancha |
last post by:
Hi,
I have a question regarding best practices in database design. In a
relational database, is it wise/necessary to sometimes create tables
that are not related to other tables through a...
|
by: x5Rb |
last post by:
Database: 2 parent tables + 1 child table (child of both parent tables), enforcing foreign key constraints
App: winformsapp, stuff (dataset, datatables, tableadatpers) generated by designer
...
|
by: Neil |
last post by:
Yikes! My database, which had been consistently 1 gig for a long time, went
from being 1 gig to 3 gigs overnight! Looking at the nightly backups, the
database increased on average about 5-15 MB per...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
| |