[SQL] Delete with join -- deleting related table entries?
When I delete a record from a certain table, I need to delete a (possibly) attached note as well. How can I do this with postgres? The tables are like this: reservation reservation_id stuff... isuse issue_id reservation_id stuff.. note issue_id text comments... A select that pulls out what I want to delete is: SELECT reservation_id,issue_id,note_id,eg_note.comments FROM eg_reservation LEFT JOIN eg_issue USING (reservation_id) LEFT JOIN eg_note USING (issue_id) WHERE reservation_id > condition; Can anyone help me turn this into a DELETE statement? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Delete with join -- deleting related table entries?
I'm not sure if this is true for you as I can't see your complete table definitions, but I'd usually do this by using issue_id INTEGER REFERENCES issue ON DELETE CASCADE in my column definition. See [1] for more information. [1]http://www.postgresql.org/docs/current/interactive/ddl-constraints.html#DDL-CONSTRAINTS-FK ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] (NONE)
Your question is not clear at all. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Delete with join -- deleting related table entries?
Bryce Nesbitt wrote: > When I delete a record from a certain table, I need to delete a > (possibly) attached note as well. How can I do this with > postgres? The > tables are like this: > > reservation > reservation_id > stuff... > > isuse > issue_id > reservation_id references reservation (reservation_id) -- ADD > stuff.. > > note > issue_id references isuse (issue_id) -- ADD (kept typo in example) > text comments... > > A select that pulls out what I want to delete is: > > SELECT reservation_id,issue_id,note_id,eg_note.comments FROM > eg_reservation >LEFT JOIN eg_issue USING (reservation_id) >LEFT JOIN eg_note USING (issue_id) >WHERE reservation_id > condition; > > Can anyone help me turn this into a DELETE statement? 1. Add foreign key references between the tables to ensure that there are only notes and issues (isuses? :) for existing issues and reservations respectively. You can make those references 'ON DELETE CASCADE' so that a delete of the original reservation cascades down to related entries in the issue table, which in turn cascade down to the related entries in the note table. 2. Or... BEGIN; DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse WHERE reservation_id = reservation_to_delete); DELETE FROM isuse WHERE reservation_id = reservation_to_delete; DELETE FROM reservations WHERE reservation_id = reservation_to_delete; END; with an appropriate value or expression substituted into reservation_to_delete. This would be the "hard way", but (as it's in a single transaction) will still protect other clients from seeing a partial delete. Get yourself a good, non-MySQL-specific database book, which should explain how referential integrity is handled in databases. -Owen ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Delete with join -- deleting related table entries?
BigSmoke wrote: > ...I'd usually do this by using > issue_id INTEGER REFERENCES issue ON DELETE CASCADE > Good, and valuable, thanks! But at the moment I can't change the schema. So is there a way to do a cascaded or joined delete in a sql schema that did not anticipate it? Again, this is deleting rows from three related tables, as a single atomic operation. -Bryce ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Delete with join -- deleting related table entries?
Owen Jacobson wrote: > BEGIN; > DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse > WHERE reservation_id = reservation_to_delete); > DELETE FROM isuse WHERE reservation_id = reservation_to_delete; > DELETE FROM reservations WHERE reservation_id = > reservation_to_delete; > END; That should be COMMIT;, not END;. Been writing too much pl/pgsql. -Owen ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] regarding debugging?
Checking how your PgSQL statements are executed, can be done using EXPLAIN [1]. EXPLAIN ANALYZE will also execute (but not dry-run!) your statement. I work with a seperate development and production database. Once the changes to the schema in the development DB are done, I commit them to the production DB using ActiveRecord migrations. You could do the same with an advanced schema diff tool such as pgdiff [2] or zongle [3]. - Rowan [1] http://www.postgresql.org/docs/current/interactive/sql-explain.html [2] http://pgdiff.sourceforge.net/ [3] http://zongle.sourceforge.net/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Delete with join -- deleting related table entries?
Owen Jacobson wrote: > BEGIN; > DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse > WHERE reservation_id = reservation_to_delete); > DELETE FROM isuse WHERE reservation_id = reservation_to_delete; > DELETE FROM reservations WHERE reservation_id = reservation_to_delete; > COMMIT; > > With an appropriate value or expression substituted into > reservation_to_delete. This would be the "hard way", but (as it's in a > single transaction) will still protect other clients from seeing a partial > delete. Yup, that's exactly how I delete reservations one a time. But here I need to select a few thousand reservations, and I don't think this will work: BEGIN; DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse WHERE reservation_id IN (select reservation_id from reservations where date > magic); DELETE FROM isuse WHERE reservation_id IN (select reservation_id from reservations where date > magic) DELETE FROM reservations WHERE reservation_id IN (select reservation_id from reservations where date > magic) COMMIT; I suppose I can do the subselect as a perl wrapper, but I was thinking that maybe SQL could do it all for me -Bryce ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Delete with join -- deleting related table entries?
Hi, Bryce, Bryce Nesbitt wrote: > BEGIN; > DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse > WHERE reservation_id IN > (select reservation_id from reservations where date > magic); > DELETE FROM isuse WHERE reservation_id IN > (select reservation_id from reservations where date > magic) > DELETE FROM reservations WHERE reservation_id IN > (select reservation_id from reservations where date > magic) > COMMIT; > > I suppose I can do the subselect as a perl wrapper, but I was thinking > that maybe SQL could do it all for me Why do you think this won't work? (provided you add the missing ) and ; :-) Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Delete with join -- deleting related table entries?
Bryce Nesbitt wrote: > Owen Jacobson wrote: > > > BEGIN; > > DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse > > WHERE reservation_id = reservation_to_delete); > > DELETE FROM isuse WHERE reservation_id = reservation_to_delete; > > DELETE FROM reservations WHERE reservation_id = > reservation_to_delete; > > COMMIT; > > > > With an appropriate value or expression substituted into > > reservation_to_delete. This would be the "hard way", but (as > > it's in a single transaction) will still protect other > > clients from seeing a partial delete. > > Yup, that's exactly how I delete reservations one a time. But here I > need to select a few thousand reservations, and I don't think > this will > work: > BEGIN; > DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse > WHERE reservation_id IN > (select reservation_id from reservations where date > magic); > DELETE FROM isuse WHERE reservation_id IN > (select reservation_id from reservations where date > magic) > DELETE FROM reservations WHERE reservation_id IN > (select reservation_id from reservations where date > magic) > COMMIT; > > I suppose I can do the subselect as a perl wrapper, but I was thinking > that maybe SQL could do it all for me Further thinking produced the following functional example. CREATE TABLE reservation ( reservation_id INTEGER NOT NULL, date DATE NOT NULL ); CREATE TABLE issue ( issue_id INTEGER NOT NULL, reservation_id INTEGER NOT NULL ); CREATE TABLE note ( issue_id INTEGER NOT NULL ); INSERT INTO reservation VALUES (1, '2006-01-01'); INSERT INTO reservation VALUES (2, '2006-01-15'); INSERT INTO reservation VALUES (3, '2006-02-01'); INSERT INTO issue VALUES (1, 1); INSERT INTO issue VALUES (2, 1); INSERT INTO issue VALUES (3, 2); INSERT INTO issue VALUES (4, 2); INSERT INTO issue VALUES (5, 3); INSERT INTO issue VALUES (6, 3); INSERT INTO note VALUES (1); INSERT INTO note VALUES (2); INSERT INTO note VALUES (3); INSERT INTO note VALUES (4); INSERT INTO note VALUES (5); INSERT INTO note VALUES (6); -- PostgreSQL 8.0 and prior BEGIN; DELETE FROM note WHERE note.issue_id = issue.issue_id AND issue.reservation_id = reservation.reservation_id AND reservation.date > '2006-01-16'; DELETE FROM issue WHERE issue.reservation_id = reservation.reservation_id AND reservation.date > '2006-01-16'; DELETE FROM reservation WHERE date > '2006-01-16'; COMMIT; -- PostgreSQL 8.1 and later BEGIN; DELETE FROM note USING issue, reservation WHERE note.issue_id = issue.issue_id AND issue.reservation_id = reservation.reservation_id AND reservation.date > '2006-01-16'; DELETE FROM issue USING reservation WHERE issue.reservation_id = reservation.reservation_id AND reservation.date > '2006-01-16'; DELETE FROM reservation WHERE date > '2006-01-16'; COMMIT; The version using subselects works fine, too. -Owen ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Delete with join -- deleting related table entries?
Markus Schaber wrote: > Hi, Bryce, > > Bryce Nesbitt wrote: > > >> BEGIN; >> DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse >> WHERE reservation_id IN >> (select reservation_id from reservations where date > magic) >> ); >> DELETE FROM isuse WHERE reservation_id IN >> (select reservation_id from reservations where date > magic); >> DELETE FROM reservations WHERE reservation_id IN >> (select reservation_id from reservations where date > magic); >> COMMIT; >> >> I suppose I can do the subselect as a perl wrapper, but I was thinking >> that maybe SQL could do it all for me >> > > Why do you think this won't work? (provided you add the missing ) and ; :-) > Wow. It worked. Cool. I guess the reservations don't get deleted until they are not needed any more... Not the fastest thing in the world. But it worked. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Delete with join -- deleting related table entries?
Bryce Nesbitt wrote: > Markus Schaber wrote: > > > Bryce Nesbitt wrote: > > > > > >> BEGIN; > >> DELETE FROM note WHERE issue_id IN (SELECT issue_id FROM isuse > >> WHERE reservation_id IN > >> (select reservation_id from reservations where date > magic) > >> ); > >> DELETE FROM isuse WHERE reservation_id IN > >> (select reservation_id from reservations where date > magic); > >> DELETE FROM reservations WHERE reservation_id IN > >> (select reservation_id from reservations where date > magic); > >> COMMIT; > >> > >> I suppose I can do the subselect as a perl wrapper, but I > >> was thinking that maybe SQL could do it all for me > > > > Why do you think this won't work? (provided you add the > missing ) and ; :-) > > Wow. It worked. Cool. I guess the reservations don't get deleted > until they are not needed any more... > > Not the fastest thing in the world. But it worked. EXPLAIN works with DELETE too. Joins in general on unindexed fields can be pretty slow; if you see a lot of Seq Scan entries in the EXPLAIN output, you might consider having indexes added on appropriate fields. Then again, if you were going to do that, you might as well just fix the schema to use REFERENCES...ON DELETE CASCADE and be done with it. :) -Owen ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Non Matching Records in Two Tables
I need some help with a bit of SQL. I have two tables. I want to find records in one table that don't match records in another table based on a common column in the two tables. Both tables have a column named 'key100'. I was trying something like: SELECT count(*) FROM table1, table2 WHERE (table1.key100 != table2.key100); But the query is very slow and I finally just cancel it. Any help is very much appreciated. -Ken
Re: [SQL] Non Matching Records in Two Tables
At 04:10 PM 2/8/06, Ken Hill wrote: I need some help with a bit of SQL. I have two tables. I want to find records in one table that don't match records in another table based on a common column in the two tables. Both tables have a column named 'key100'. I was trying something like: SELECT count(*) FROM table1, table2 WHERE (table1.key100 != table2.key100); But the query is very slow and I finally just cancel it. Any help is very much appreciated. vacuum analyse table1; vacuum analyse table2; select count(*) from table1 full outer join table2 on table1.key100=table2.key100 where table1.key100 is null or table2.key100 is null; If this is also slow, post output of "EXPLAIN ANALYSE SELECT " ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Non Matching Records in Two Tables
Hi, Ken, Ken Hill schrieb: > I need some help with a bit of SQL. I have two tables. I want to find > records in one table that don't match records in another table based on > a common column in the two tables. Both tables have a column named > 'key100'. I was trying something like: > > SELECT count(*) > FROM table1, table2 > WHERE (table1.key100 != table2.key100); > > But the query is very slow and I finally just cancel it. Any help is > very much appreciated. Do you have indices on the key100 columns? Is autovacuum running, or do you do analyze manually? Can you send us the output from "EXPLAIN ANALYZE [your query]"? Btw, I don't think this query will do what you wanted, it basically creates a cross product, that means if your tables look like: schabitest=# select * from table1; key100 | valuea | valueb ++ 1 | foo| bar 2 | blah | blubb 3 | manga | mungo schabitest=# select * from table2; key100 | valuec | valued ++ 1 | monday | euro 2 | sunday | dollar 4 | friday | pounds Then your query will produce something like: schabitest=# select * from table1, table2 WHERE (table1.key100 != table2.key100); key100 | valuea | valueb | key100 | valuec | valued +++++ 1 | foo| bar| 2 | sunday | dollar 1 | foo| bar| 4 | friday | pounds 2 | blah | blubb | 1 | monday | euro 2 | blah | blubb | 4 | friday | pounds 3 | manga | mungo | 1 | monday | euro 3 | manga | mungo | 2 | sunday | dollar 3 | manga | mungo | 4 | friday | pounds I suggest you would like to have all records from table1 that don't have a corresponding record in table2: schabitest=# select * from table1 where table1.key100 not in (select key100 from table2); key100 | valuea | valueb ++ 3 | manga | mungo HTH, Markus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Non Matching Records in Two Tables
Ken Hill wrote: > I need some help with a bit of SQL. I have two tables. I want > to find records in one table that don't match records in another > table based on a common column in the two tables. Both tables > have a column named 'key100'. I was trying something like: > > SELECT count(*) > FROM table1, table2 > WHERE (table1.key100 != table2.key100); > > But the query is very slow and I finally just cancel it. Any help > is very much appreciated. That's a cartesian join, there, and it'll be huge (on the order of N*M rows, where N and M are the number of rows in the first and second tables respectively). It sounds like, from your description, you want to find rows in table1 that don't have a corresponding row in table2. This should work: SELECT count(*) FROM table1 LEFT JOIN table2 ON table1.key100 = table2.key100 WHERE table2.key100 IS NULL; This will still be fairly slow unless there are indexes on table1.key100 and table2.key100, but nowhere near as slow as the original query. Frank Bax's solution will work if what you want is a count of rows in table1 that don't have a corresponding row in table2 or in table2 that don't have a corresponding row in table1; for that specific requirement you may actually be better off doing two queries (one for each table) and adding the results together. -Owen ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Non Matching Records in Two Tables
On Wed, 2006-02-08 at 16:27 -0500, Frank Bax wrote:
At 04:10 PM 2/8/06, Ken Hill wrote:
>I need some help with a bit of SQL. I have two tables. I want to find
>records in one table that don't match records in another table based on a
>common column in the two tables. Both tables have a column named 'key100'.
>I was trying something like:
>
>SELECT count(*)
>FROM table1, table2
>WHERE (table1.key100 != table2.key100);
>
>But the query is very slow and I finally just cancel it. Any help is very
>much appreciated.
vacuum analyse table1;
vacuum analyse table2;
select count(*) from table1 full outer join table2 on
table1.key100=table2.key100 where table1.key100 is null or table2.key100 is
null;
If this is also slow, post output of "EXPLAIN ANALYSE SELECT "
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
OK. I added indexes on the two columns in the two tables:
CREATE INDEX key100_idex
ON ncccr9 (key100);
CREATE INDEX key100_ncccr10_idex
ON ncccr10 (key100);
Here is the analysis of the query:
csalgorithm=# EXPLAIN ANALYSE SELECT count(*)
csalgorithm-# FROM ncccr9 LEFT JOIN ncccr10 ON ncccr9.key100 = ncccr10.key100
csalgorithm-# WHERE ncccr10.key100 IS NULL;
QUERY PLAN
Aggregate (cost=208337.59..208337.59 rows=1 width=0) (actual time=255723.212.. 255723.214 rows=1 loops=1)
-> Hash Left Join (cost=99523.55..207101.41 rows=494471 width=0) (actual ti me=92326.635..255538.447 rows=38144 loops=1)
Hash Cond: ("outer".key100 = "inner".key100)
Filter: ("inner".key100 IS NULL)
-> Seq Scan on ncccr9 (cost=0.00..59360.71 rows=494471 width=104) (ac tual time=171.778..75099.734 rows=494471 loops=1)
-> Hash (cost=88438.64..88438.64 rows=611564 width=104) (actual time= 91962.956..91962.956 rows=0 loops=1)
-> Seq Scan on ncccr10 (cost=0.00..88438.64 rows=611564 width=1 04) (actual time=11.704..76519.323 rows=611564 loops=1)
Total runtime: 255724.219 ms
(8 rows)
The result of 38,144 non-matching records seems too much:
csalgorithm=# SELECT count(*)
csalgorithm-# FROM ncccr9 LEFT JOIN ncccr10 ON ncccr9.key100 = ncccr10.key100
csalgorithm-# WHERE ncccr10.key100 IS NULL;
count
---
38144
(1 row)
Maybe I need to do a RIGHT JOIN to return the count of records in table 'ncccr10' that don't match records in 'ncccr9'?
Thanks for your help. JOINS are fairly new to me.
[SQL] Syntax for "IF" clause in SELECT
Greetings, the following is an MySQL statement that I would like to translate to PostgreSQL: Could someone point me to a documentation of a coresponding Systax for an "IF" clause in the a SELECT, or is the some other way to do this select if(spektrum is null,' ','J'), if(s19 is null,' ','J'), if(OhneGrenze is null,' ','J'), from namen; Do I need to create my own function to allow this behaviour! my best regards, Stefan -- email: [EMAIL PROTECTED] tel : +49 (0)6232-497631 http://www.yukonho.de ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Syntax for "IF" clause in SELECT
[EMAIL PROTECTED] wrote: Greetings, the following is an MySQL statement that I would like to translate to PostgreSQL: Could someone point me to a documentation of a coresponding Systax for an "IF" clause in the a SELECT, or is the some other way to do this select if(spektrum is null,' ','J'), if(s19 is null,' ','J'), if(OhneGrenze is null,' ','J'), from namen; Do I need to create my own function to allow this behaviour! my best regards, Stefan use CASE Since I'm not a user of MySQL, and if I'm reading your query correctly: try select (CASE when spektrum is null then 'J' else spektrum end), ... or if you are just trying to replace nulls, then try COALESCE ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Column Index vs Record Insert Trade-off?
Is there a performance trade-off between column indexes and record inserts? I know that in MS Access there is such a trade-off. This being indexes make SQL queries perform faster at the cost of record insert speed. Put another way, the more column indexes in a table, the slower a record insert in that table performs. Is there a similar trade-off in PostgreSQL?
[SQL] unique constraint instead of primary key? what disadvantage(ODBC usage)?
[PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1] I'm designing a completely new schema for my database. A major criterion is that it facilitate ad-hoc queries via MS-access, excel and OpenOffice, presumably with ODBC. My question regards the use of UNIQUE constraints instead of PRIMARY KEY's on some tables. Both result in an index on the specified tuple of fields, so I presume query performance shouldn't be much different. Using UNIQUE constraints seems to let me better match the natural structure of my data. A 'run' contains a sequence of 'opsets'. Each opset contains a sequence of (a few) 'step's. run-foo opset-1 step-1 step-2 opset-2 step-1 So the 'steps' table is logically indexed by (run, opset_num, step_num). But some opsets are not in runs, and some steps are not in opsets, so I would have step.run be null in some cases, likewise step.opset_num. Null values mean I can't use these fields in a primary key, so I propose to use UNIQUE constraints instead. What am I losing by not using PRIMARY KEYS? Will ODBC clients have difficulty dealing nicely with the database? Will the planner produce lousy query plans? Will Edgar Codd haunt my dreams? -- George Young -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Syntax for "IF" clause in SELECT
This has been something I've been trying do so that I can do some column comparisons as part of "data-cleaning" work. I'll let you know if this helps me accomplish my task! On Wed, 2006-02-08 at 15:20 -0800, Bricklen Anderson wrote: [EMAIL PROTECTED] wrote: > Greetings, > > the following is an MySQL statement that I would like to > translate to PostgreSQL: > > Could someone point me to a documentation of a coresponding > Systax for an "IF" clause in the a SELECT, > or is the some other way to do this > > select > if(spektrum is null,' ','J'), > if(s19 is null,' ','J'), > if(OhneGrenze is null,' ','J'), > from namen; > > > Do I need to create my own function to allow this behaviour! > > > my best regards, > > Stefan use CASE Since I'm not a user of MySQL, and if I'm reading your query correctly: try select (CASE when spektrum is null then 'J' else spektrum end), ... or if you are just trying to replace nulls, then try COALESCE ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] unique constraint instead of primary key? what
On Wed, 2006-02-08 at 21:04 -0500, george young wrote: [PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1] I'm designing a completely new schema for my database. A major criterion is that it facilitate ad-hoc queries via MS-access, excel and OpenOffice, presumably with ODBC. My question regards the use of UNIQUE constraints instead of PRIMARY KEY's on some tables. Both result in an index on the specified tuple of fields, so I presume query performance shouldn't be much different. Using UNIQUE constraints seems to let me better match the natural structure of my data. A 'run' contains a sequence of 'opsets'. Each opset contains a sequence of (a few) 'step's. run-foo opset-1 step-1 step-2 opset-2 step-1 So the 'steps' table is logically indexed by (run, opset_num, step_num). But some opsets are not in runs, and some steps are not in opsets, so I would have step.run be null in some cases, likewise step.opset_num. Null values mean I can't use these fields in a primary key, so I propose to use UNIQUE constraints instead. What am I losing by not using PRIMARY KEYS? Will ODBC clients have difficulty dealing nicely with the database? Will the planner produce lousy query plans? Will Edgar Codd haunt my dreams? -- George Young I think I can give you some insights about MS Access to help you. In MS Access, you can specify a column as a "primary key"; which basically means the column is indexed and must contain unique values (also, nulls are not allowed). I have run into problems depending on columns being "primary key" in MS Access in db apps that receive data in batch file uploads from other sources (e.g., uploading 1,000+ records into a table). Is sounds like your requirement to use MS Access for ad-hoc queries means that you will have some users that want to access the database with MS Access as a "front-end" client tool. If that is the situation, then you don't need to worry about the structure of the table as MS Access relies on ODBC for this. You may also want to communicate to the end users that MS Access is not a client-server tool; in other words, all of the records are transferred from the server to the client's box and then the query is executed. I hope that helps.
Re: [SQL] unique constraint instead of primary key? what
On Wed, 08 Feb 2006 18:34:22 -0800 Ken Hill <[EMAIL PROTECTED]> threw this fish to the penguins: > On Wed, 2006-02-08 at 21:04 -0500, george young wrote: > > > [PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1] > > I'm designing a completely new schema for my database. A major > > criterion is that it facilitate ad-hoc queries via MS-access, excel and > > OpenOffice, presumably with ODBC. > > > > My question regards the use of UNIQUE constraints instead of PRIMARY > > KEY's on some tables. Both result in an index on the specified tuple > > of fields, so I presume query performance shouldn't be much different. > > > > Using UNIQUE constraints seems to let me better match the natural > > structure of my data. A 'run' contains a sequence of 'opsets'. > > Each opset contains a sequence of (a few) 'step's. > > > >run-foo > > opset-1 > > step-1 > > step-2 > > opset-2 > > step-1 > > > > So the 'steps' table is logically indexed by (run, opset_num, step_num). > > But some opsets are not in runs, and some steps are not in opsets, so > > I would have step.run be null in some cases, likewise step.opset_num. > > > > Null values mean I can't use these fields in a primary key, so I > > propose to use UNIQUE constraints instead. > > > > What am I losing by not using PRIMARY KEYS? Will ODBC clients have > > difficulty dealing nicely with the database? Will the planner produce > > lousy query plans? Will Edgar Codd haunt my dreams? > > > > -- George Young > > > > > > I think I can give you some insights about MS Access to help you. In MS > Access, you can specify a column as a "primary key"; which basically > means the column is indexed and must contain unique values (also, nulls > are not allowed). I have run into problems depending on columns being > "primary key" in MS Access in db apps that receive data in batch file > uploads from other sources (e.g., uploading 1,000+ records into a > table). > > Is sounds like your requirement to use MS Access for ad-hoc queries > means that you will have some users that want to access the database > with MS Access as a "front-end" client tool. If that is the situation, > then you don't need to worry about the structure of the table as MS > Access relies on ODBC for this. You may also want to communicate to the > end users that MS Access is not a client-server tool; in other words, > all of the records are transferred from the server to the client's box > and then the query is executed. Ouch! A good portion of queries will access my 4M row parameter table in joins with other tables. It sounds like MS access is not workable. Thanks for the info. > > I hope that helps. > -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
