FOREIGN KEYs for starters

vivoices · #1 (**permalink**) 04-23-10, 17:56

I have a "sizes" and a "finishes" table each of which has an "available" boolean field.

A "products" table uses the "sizes" id and "finishes" id, which are primary key fields, to build products.

I would like to maintain an "available" boolean field in the "products" table as well.

Would FOREIGN KEYs be able to update the "products"."available" field as in:

Code:

UPDATE
	products
	INNER JOIN
		sizes
	ON
		sizes.sizeId = products.sizeId

	INNER JOIN
		finishes
	ON
		finishes.finishId = products.finishId
SET
	products.available = ( sizes.available AND finishes.available )

and if yes, how to set the FKs up?

Thanks for any help,
David

r937 · #2 (**permalink**) 04-23-10, 19:25

alas, foreign keys won't accomplish that

you could write triggers, if you were a masochist

i would expect that you'll be happy running the update query manually as necessary, i.e. as often as you add new sizes and finishes

vivoices · #3 (**permalink**) 04-23-10, 19:40

Thanks for saving me some time.

It will be no problem updating manually.
New sizes and finishes are less of a problem than switching their availability on and off.
Just that the DB I am designing for the project has a lot of updating need like in the example for my question.
I will just "bundle" an UPDATE statement to every relevant point in the application.

It would have been nice to have a database that could keep data-integrity by itself.

vivoices · #4 (**permalink**) 04-24-10, 03:17

I looked into triggers, now that you mentioned them.
I learned to set them up in no time and they are exactly what I need. They are a very simple form of an event system which is the "spine" for OOP, great!

Only working in the dark would indeed be masochistic, if that is what you meant.

Can you recommend a good, perhaps even free IDE for conveniently developing and maintaining a fair amount of triggers in MySQL?
I found phpMyIDE but have not idea if it is any good.

Do you think there are other reasons to feel masochistic for working with triggers?

Thanks a lot for your input,
David

r937 · #5 (**permalink**) 04-24-10, 03:22

Quote:

Originally Posted by vivoices

Can you recommend a good, perhaps even free IDE for conveniently developing and maintaining a fair amount of triggers in MySQL?

HeidiSQL

vivoices · #6 (**permalink**) 04-24-10, 03:46

Thanks,
installing and checking.

Happy coding

vivoices · #7 (**permalink**) 04-29-10, 05:41

HeidiSQL is great!

Just too bad that a lot of hosting providers do not allow to connect to databases remotely in shared hosting environments.
I am still working with both phpMyAdmin and Heidi and phpMyAdmin does not include the SQL for the triggers when exporting a database to a SQL file.

Thanks again,
David

Internet Services

Web Development

Digital Marketing

Consumer Tech