Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Posts
    25

    Unanswered: Constraint Problems

    I have a table RESERVATIONS, which has these Constraints:

    CONSTRAINT `reservations_customerID` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`),

    CONSTRAINT `reservations_eventID` FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`),

    CONSTRAINT `reservations_hotelID` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`hotel_id`)

    For some reason, this INSERT SUCCEEDS:
    INSERT INTO reservations (hotel_id, customer_id) VALUES (111,222)

    While this INSERT FAILS:
    INSERT INTO reservations (hotel_id, event_id, customer_id) VALUES (111,222,333)

    With the following error message:
    "Cannot add or update a child row: a foreign key constraint fails ('turon/reservations', CONSTRAINT 'reservations_eventID' FOREIGN KEY ('event_id') REFERENCES 'events' ('event_id'))

    As far as I can tell all constraints are similar.

    Any suggestions?

    Thanks,
    Michael

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the error message is quite clearly telling you that you do not have a 222 row in the events table

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2006
    Posts
    25
    Oh! So a Foreign Key Constraint also prevents you from entering a non-existent FK value?

    Shoot! I thought FK Constraints we only to prevent deleting a related record...

    Is that correct?

    Thanks, Michael

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mvreade
    Oh! So a Foreign Key Constraint also prevents you from entering a non-existent FK value?
    that is correct
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2006
    Posts
    25
    Living and learning. Thanks for the help, r937.

    All the best,

    Michael

Posting Permissions

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