delete multiple records from multiple tables

[kredd]

hi,
i have delete page set up where a 'store owner' can select multiple customers(with checkboxes) to be deleted from a 'shoppers' table. works great. what i'm wondering is if i can add the 'product' table to the delete command so that when it fires it will delete the selected customerS in the 'shoppers' table and their products associated with them in the 'cart' table. both the shopper and their products share a common identifier.

i'm using MS ACCESS...

thanks,
kelly

[kredd]

not a big deal as once thot....

just added another delete statement that looked for all cart items that matched the same value used to delete the shopper.

if anyone knows a more efficient way - as in doing it with one DELETE statement, etc., please post...

thanks!
kelly

[phpnovice]

All you have to do is have referrential integrity turned on for the relationship between the shopper's table and the product's table. Then, when you DELETE a row from the shopper's table, the associated rows from the product's table are automatically deleted.

[kredd]

sweet. this rocks! i had no idea this could be done...but...

neither of the two related fields(userID, prodID) are a primary key or autonumbered...b/c of this ACCESS says it can't guarantee accuracy of a cascaded DELETE and the relationship is "indeterminate". but i know the userIDs in my primary table are unique b/c i check the db to make sure it doesn't already exist before inserting it.

so would this override the "indeterminate" relationship that ACCESS sees and give me an accurate DELETE?

'preciate the help,
kelly

[phpnovice]

So just create a unique index over the userid in the shopper's table.
I would think you would want that anyway.

[kredd]

turns out that ACCESS won't allow me to create the relationship b/c neither of the fields are unique. when i posted i hadn't got that far.

but just for clarification, when you say "unique" do you mean the auto-numbered primary key set up by ACCESS? if so, i've got the ID column set to that and its being used by other pages so i can't change it.


in the future i will def keep this in mind...

[phpnovice]

No, you can create a unique index over a non-autonumber column. If you already have an auto-number column, why aren't you using that as the secondary key to your products table? I take it you're not a trained database analyst? ;)

[kredd]

"trained database analyst"?? no, not by a long shot!! ;) i'm figuring this out as i go!

i've been doing this for about 5 years, but basically i just use ACCESS to store data and then do all the work outside of the DB. never thot about what work can be done by ACCESS itself, like the cascading deletes and what-not.

but you've def sparked my interest here, i guess a secondary key is the 'second line of defense' after the primary key has been queried?

should i just do a 'help' search for 'unique index' or something? sometimes the help files aren't very helpful :D

[phpnovice]

No, a secondary key is what you use to create a relationship between two tables (also called a foreign key). The primary key of one table is used as the secondary key of another table. That second table has its own primary key, too. In your case, I would have created the shopper's table with an autonumber primary key and a customer number alternate key. The alternate key would have a unique index built over it -- this is so that queries against the shopper's table by customer number are satisfied by indexed access rather than sequential access. Then, the products table would have an autonumber primary key and the shopper's primary key as the products secondary key. The products table would have a compound unique index built over the combination of the secondary key and the primary key -- so that, again, queries against the products table by secondary key can still be satisfied by indexed access rather than sequential access. Note that the products table could also have a compound unique index built over a combination of the product number and the other two keys.

Which version of Access do you have for your database design tool (in other words, on your local machine)?

[kredd]

i'm using ACCESS 2002

ok, its monday and my brain hurts a little after reading that...but, i understand the primary/foriegn key deal, i just misunderstood you before.

so where and when are these unique keys created? ACCESS requires a primary key(*) be set when the table is created, but what about the secondary key and unique index? are they also set when you create the tables?

(*)i've always just let ACCESS create an autonumbered ID field and allowed that field to be set as the primary key just by default...then used SQL to do all the work outside ACCESS

[phpnovice]

What you create at design-time can be utilized via SQL at execution-time. Yes, you should create the additional indicies at design-time so that your on-line access is faster at execution-time. You create alternate and secondary key indices in the View / Indexes dialog box. Depending upon the indexed column, you may also wish to define that column as "required".

Index Name Field Name Sort Order
------------ ------------ ------------
PrimaryKey id Ascending
SecondaryKey key Ascending
id Ascending

The above example demonstrates a compound key setup that can be used to make the secondary key unique if needed. If the "key" field, in this case, is already unique, then this compound key setup is not required.