1

I have two tables loadingDetail, and stowageDetail:

loadingDetail           | stowageDetail
[PK] loadingID (int)    | [PK] StowageID (int)
[PK] transactionID (in) | [PK] room (int)
                        | [UNIQUE] transactionID (int) 
  • transactionID in loadingDetail can be duplicated when the loadingID value is different.
  • transactionID in stowageDetail is UNIQUE.
  • many transactionID values in loadingDetail can have only one transactionID in stowageDetail.
  • transactionID in loadingDetail can be deleted when there is no transactionID in stowageDetail (No Action)

How I can represent that in a database diagram?

Additional information

I'm really looking for a database-diagram possible answer. But I tried to find something about many-to-one relationships in database diagrams and it seems can't find it. The relationship is like many Containers put inside Big Box. The Containers just can removed if the Big Box is already removed. The supported database diagram "I know" is one-to-many which in my condition does not fit because the Box is removed the first, then Containers the second.

0

transactionID in loadingDetail can be duplicated when the loadingID different

This is your requirement.Table composite key already handling this.

transactionID in stowageDetail is UNIQUE

This is your requirement

•many transactionID in loadingDetail just can have 1 transactionID in stowageDetail

This is your requirement.This can Unique index. Or it can be handle via code/trigger. This can never be FK constraint.You cannot define partial FK constraint.

•transactionID in loadingDetail can be deleted when there is no transactionID in stowageDetail (No Action)

Because you cannot define partial FK constraint. You have to handle this via code.**Instead of Trigger** is ideal for this scenario.

I assume you cannot define transactionID and loadingID as FK in stowageDetail table because of your requirement.

Read this

As per your requirement, your design is already ok.Adding any other candidate key is useless. you cannot do it via PK-FK constraint.

  • Thank you, finally I made a trigger for that issue. I added the code to your answer. Feel free to remove/edit. – Jun Rikson May 18 '18 at 14:27
  • Would not a foreign key like: ALTER TABLE loadingDetail FOREIGN KEY (transactionID) REFERENCES stowageDetail (transactionID) ON DELETE RESTRICT satisfy the fourth requirement? I.e. you can only remove a stowageDetail when there are no loadingDetail for that transaction? – Lennart May 18 '18 at 14:33
  • @Lennart : you can only remove loadingDetail when there are no stowageDetail. And many loadingDetail can only have 1 stowageDetail for that transaction. The problem is many-to-one relationship, can't have many as Primary Key and One as Foreign Key. – Jun Rikson May 18 '18 at 15:11
  • @ColourDalnet - I'm rejecting your edit. It's likely more appropriate for you to add your own answer with that code if it helped you answer the question. – Max Vernon May 18 '18 at 17:41

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.