CODEWITHSUNDEEP
sqloracle-databasedatabase-administration
redCodeAlert
redCodeAlert

Reputation: 663

Multiple foreign keys and multiple constrains

Let's assume I have a table called boxes with the box_id attribute as the PK.

There are two other tables. The first one is red_boxes and the second blue_boxes.

I have added a constraint to the red_boxes table

ALTER TABLE red_boxes
ADD CONSTRAINT fk_box_id
FOREIGN KEY (box_id)
REFERENCES boxes (box_id);

Now, I would like to add a constraint to the blue_boxes table. The SQL structure would look like the following, if I did not add the constraint already to the the red_boxes. The obvious way to fix this is to name a new constraint differently e.g. fk_box_id2, but is this is a good way? Am I supposed to somehow re-use the previous constraint, or this is not possible, why?

ALTER TABLE blue_boxes
ADD CONSTRAINT fk_box_id
FOREIGN KEY (box_id)
REFERENCES boxes (box_id)

Upvotes: 0

Views: 25

Answers (1)

lc.
lc.

Reputation: 116538

Each constraint is separate and requires a unique name. My recommendation is to use the source and destination table names, for example fk_red_boxes_boxes and fk_blue_boxes_boxes. This way you can easily identify where they come from and where they go to.

If you have underscores in your table names, you might want to come up with a modified convention that you can easily understand at a glance. For example, a double underscore: fk__blue_boxes__boxes and fk__red_boxes__boxes.

Upvotes: 3

Related Questions