CODEWITHSUNDEEP
mysqldatabase-design
boh
boh

Reputation: 1547

MySQL: 1 Column refers to 2 different tables

If I have 2 accounts table: saving account and overdraft account, and then I have the table transaction in which column "from_account" can only have value equal to either saving account ID or overdraft account ID, how should I set the foreign key constraint? Is there any better schema design as the saving&overdraft account ID can collide. Im using mysql btw :) Thanx

Upvotes: 1

Views: 233

Answers (2)

magarciaschopohl
magarciaschopohl

Reputation: 345

You can define an additional column ACCOUNT_TYPE and set a UNIQUE CONSTRAINT combining the FROM_ACCOUNT and the ACCOUNT_TYPE columns. You can also add a CHECK CONSTRAINT to the ACCOUNT_TYPE to limit its values to i.e 1,2.

Or you define one column for each referenced table plus a CHECK CONSTRAINT to ensure you never fill both at the same time.

And finally, consider merging both account tables, if there is no much difference between them.

Upvotes: 0

mavroprovato
mavroprovato

Reputation: 8352

A better schema design would be to have two distinct columns in the transaction table: One referencing the saving account table and the other referencing overdraft account table. Of course, these columns should be nullable and only one of them will point to an actual row in the other tables. But with this design you can enforce referential integrity constraints.

Another option would be to merge the two account tables into one, especially if they have many similar columns.

Upvotes: 1

Related Questions