Search code examples
sqlt-sqlspecializationgeneralization

How to add a foreign key constraint references to the IS-A-to-Two-Tables relationship?

First in my ER-Model I have an account entity which has a IS-A relationship with two disjoint subentities saving-account and checking-account. However I have a customer entity have a depositor relationship with the account entity, such the use case like the ER diagram below.

enter image description here

Therefore I translate the ER into the table like this:

customer(cid, cname)

depositor(cid, account-number)

saving-account(account-number, balance, interest-rate)

checking-account(account-number, balance, overdraft-amount)

So finally, I take only two tables saving-account and checking-account from the IS-A relationship.

Then the problem occurs, to create the depositor table, I take a T-SQL:

CREATE TABLE depositor(
    customer_id         int             not null,
    account_number      int             not null,
    access_date         Date            DEFAULT GETDATE(),
    PRIMARY KEY(customer_id, account_number),
    FOREIGN KEY(customer_id)    REFERENCES customer(customer_id),
    FOREIGN KEY(account_number) REFERENCES account(account_number)
)

At the final line, the foreign key account_number should reference to the account table, but what I have are saving-account and checking-account tables. In this situation, how do I add a constraint in T-SQL? Is it possible to add a constraint if I only take two tables from the IS-A relationship?

Solution

  • I suggest you have an extra table called account. Then saving-account and checking-account should be separate tables pointing to that.

    Also, you should have an account_id field as a PK instead of account_number. As a best practice rule, PK should not have any business meaning, it should be an abstract concept.

    In summary, I suggest following tables:

    account(aid, account-number, balance, plus any other common field for account)
saving-account([optional pk], aid_ref, interest-rate, plus fields specific to saving-account)
checking-account([optional pk], aid_ref, overdraft-amount, plus fields specific to saving-account)
customer(cid, cname)
depositor(cid, aid)