Link to home
Create AccountLog in
Avatar of DrDamnit
DrDamnitFlag for United States of America

asked on

Optional Foreign Key Constraints?

There are three tables: bugs, comments and attachments.

A comment is always associated with a bug. (bugs have comments).

An attachment will always be associated with a comment (attachments belong to specific comments)

BUT a comment will not always have an attachment. (Like cookies, attachments are a sometimes food).

How do I setup the foreign key constraints here?

Pretty sure this won't work because if I post a comment without an attachment, it will fail...

CONSTRAINT `fk_comments_bugs1`
    FOREIGN KEY (`comments_bug`)
    REFERENCES `bugreport`.`bugs` (`bugs_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_comments_attachments1`
    FOREIGN KEY (`comments_attachment`)
    REFERENCES `bugreport`.`attachments` (`attachments_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)

Open in new window

Avatar of North2Alaska
North2Alaska
Flag of United States of America image

No, the FK is to prevent you from entering an attachment without a comment.  You should have no problem creating a comment without an attachment.
ASKER CERTIFIED SOLUTION
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account