SQLTeam.com | Weblogs | Forums

Deleting a record from Database


#1

Hello
I am a SQL beginner.

I have a question please.

I got a Database of restaurant management.

It includes several tables, and of course some tables have a common record.

For example, both the Payment table and the Reservation table include the name of the person.

The Reservation table details which table the person booked and how much he paid.

The Payment table details how the person paid for that booking - Credit Card, Cash, etc.

If I delete the person name from the Reservation table for a certain booking (e.g. 1/1/2016, Table 3), should the person be automatically deleted from the Payment table as well for that specific booking (1/1/2016, Table 3)?

Thank you very much.


#2

depends on how you set it up. if there is a foreign key between the two tables and the reservation table specifies on delete cascade, then yes. otherwise no.


#3

I'd say no. You might need the payment history far longer than you'd need the reservation history. I wouldn't "hard-link" the two in duration / retention.


#4

You need to set the foreign key option as on delete cascade. In this way while you delete the records in Payment table, all related records in Reservation table will be deleted automatically.