CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
scriptybusiness
scriptybusiness

Reputation: 25

How to choose what FOREIGN KEY to create on what attribute?

Assume I have 2 tables with following attributes.

table 1 (customers): id (PRIMARY), customer (UNIQUE), totalspent

table 2 (receipts): id, cost

What indexes do I need to create on customers or receipts to make it so when a customer is deleted from the table all the receipts tied to him via id are deleted as well?

I set receipts.id to primary key, but when deleting entries from customers, they would not be deleted from receipts. Should I make customers.id a foreign key that references receipts.id? Can a primary key be a foreign key?

Upvotes: 0

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You are looking for a cascading foreign key constraint, rather than an index.

alter table receipts add constraint fk_receipts_cust
    foreign key (cust) reference customers(id)
        on delete cascade;

In MySQL, a foreign key also happens to create an index. This is a "special feature" of MySQL. The functionality you are looking for is the cascading part. You can read more about this in the documentation.

Upvotes: 2

Related Questions