Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009
    Posts
    56

    Unanswered: FOREIGN KEYs for starters

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    56
    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.
    Last edited by vivoices; 04-23-10 at 20:43.

  4. #4
    Join Date
    Jul 2009
    Posts
    56
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vivoices View Post
    Can you recommend a good, perhaps even free IDE for conveniently developing and maintaining a fair amount of triggers in MySQL?
    HeidiSQL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2009
    Posts
    56
    Thanks,
    installing and checking.

    Happy coding

  7. #7
    Join Date
    Jul 2009
    Posts
    56
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •