3

In a nutshell, we have two tables:

Document 1 (strong entity)
Document 2 (weak entity)

Document 1 has many Document 2 and Document 2 has only one Document 1.

That being said, sometimes we need to insert a Document 2 into the database but a Document 1 doesn't always exist and that's causing a foreign key error.

Is it OK if I remove the mandatory foreign key constraint, add a Document 2 and associate them later, when I finally insert the corresponding Document 1?

Will the association still formally be present?
Also, can that cause any trouble in the future, like a stored procedure or a view stop working?

2
  • 1
    you could add a "dummy" document in Document1, and use that id when inserting document2 , then update document2 with respective document1 when it exists
    – Bob Klimes
    Oct 19 at 16:17
  • @BobKlimes yeah I'll try this approach first, thanks for the comment.
    – attoslins
    Oct 19 at 16:43
3

Is it OK if I remove the mandatory foreign key constraint, add a Document 2 and associate them later, when I finally insert the corresponding Document 1?

Technically, this is doable, but obviously it breaks the data integrity between two tables Document 1 and Document 2, that your foreign key helps to enforce

You either have to:

A) add all possible records/values into Document 1 table beforehand, so by the time you insert a new row into Document 2, value already exists at Document 1, and foreign key is not violated

or

B) Don't insert values into Document 2, which don't exist at Document 1 yet

If you can't achieve any of above (A or B) - then what's the point of having foreign key at all ?

5
  • Well, you can also disable enforcement of the constraint temporarily, can't you
    – mustaccio
    Oct 19 at 14:44
  • @mustaccio right, under "remove the mandatory foreign key constraint ... and associate them later" you can think "disable enforcement of the constraint temporarily" Oct 19 at 15:18
  • Dear @AlekseyVitsko I can't upvote because I need more reputation but I would if I could. Thanks for the answer.
    – attoslins
    Oct 19 at 16:31
  • I'm simply responding to the "either - or" statement by pointing out another alternative. Besides, even disabled constraints are useful as they demonstrate the intent.
    – mustaccio
    Oct 19 at 20:22
  • A disabled constraint may be exactly what OP is looking for... "Will the association still formally be present?" seems to be of concern, and they seem open to not actually having the foreign key constraint in place. Maybe not of course. One tidbit on that - if you're going this route, it could be worth making the doc 2 id field unique in doc 1 table for some level of constraint, since you're forgoing the key relationship
    – TCooper
    Oct 19 at 22:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.