CODEWITHSUNDEEP
sqlforeign-keys
gobnepla
gobnepla

Reputation: 644

Add column not belonging to the table to use FK?

I have a little problem with some tables where I want to have valid data and prevent invalid data from being inserted. My idea was to use FK for this but I don't have the needed column inside the table I would need it. Should I add it, although it's not really belonging there or what should be done in this case?

Tables:

box

----------------------------------------
box_id (PK)   box_type_id (FK->box_type)
          1                            1
          2                            1
----------------------------------------

box_type

---------------------------------
box_type_id (PK)   width   length
               1      40       30
               2      50       40
---------------------------------

box_pos

--------------------------------------------------------------------
box_id (PK, FK->box)   box_pos_id (PK)   item_id (FK->item)   amount
                   1                 1   item1                    10    
                   1                 2   item2                   100
                   1                 3   item3                   500
                   2                 1   item2                    50
--------------------------------------------------------------------

item

------------------------
item_id (PK)  otherstuff
item1           yadayada
item2           yadayada
item3           yadayada
------------------------

item_box_type

-------------------------------------------------------------------------
item_id (PK, FK->item)   box_type_id (PK, FK->box_type)   amount_full_box
item1                                                 1               500
item1                                                 2               600
item2                                                 1               400
-------------------------------------------------------------------------

So I have a box with a certain type. For this box I have box positions (different items with amount inside the box) and I have another table which has some information like how much of this item makes a full box.

What I want is to prevent data being inserted into box_pos which doesn't have an entry inside item_box_type but I don't have the box_type on the positions because it belongs to the box. Is there a better than adding the box_type to the box_pos, which would violate normalization rules as it doesn't depend on the box_id/box_pos_id key but only on the box_id.

Of course I could use triggers, an insert/update trigger on the box_pos to check if a combination of box.box_type_id and box_pos.item_id exists in the table item_box_type and a delete trigger on the item_box_type to check if there is still a row inside the box_pos with that combination but I like to avoid triggers if possible because their performance impact is bigger and they are pretty bound to the rdbms in use.

Upvotes: 1

Views: 40

Answers (1)

Junjie
Junjie

Reputation: 521

What I want is to prevent data being inserted into box_pos which doesn't have an entry inside item_box_type...

If you don't like box_type_id in your box_pos, you can restructure your item_box_type, like so:

--------------------------
item_box_id           (PK)
item_id        (FK) UNIQUE
box_type_id    (FK) UNIQUE
amount_full_box

And restructure your box_pos by replacing item_id with item_box_id referencing the item_box_type table, thereby achieving your needed constraint.

The simplest solution, though, is that box_pos.box_type_id.

Upvotes: 1

Related Questions