• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

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

0
DrDamnit
Asked:
DrDamnit
1 Solution
 
North2AlaskaCommented:
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.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi DrDamnit,

It's tough to tell from your example, but it sounds like the comments table has a column that contains the attachment ID.  That would be upside down.  The hierarchy should be:

--  Bug Table
--  Comment Table, with the ID of the associated bug.
--  Attachment Table, with the ID of the associated Comment.

That makes both comments and attachments optional, and allows for multiple comments and/or attachments per bug.


Good Luck,
Kent
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now