Deleting both records in a 1 to 1 relationship

开发者_JAVA技巧

Hi I have 2 tables A and B. There is a 1 to 1 relationship between A and B. Now say I have a records in A and B (A1 and B1) which are related through this 1 to 1 relationship. How would I delete both records. If I delete A1 1st then I get a conflict error same goes for B1.

I'm just curious

Thank You

---

Model the relationship between A and B in a table C. C will reference (FK) both A and B. To remove the relationship, delete the corresponding row in C. Whether the entities A and/or B should also be deleted is then a separate matter.

---

What database do you use, MSSQL ORACLE, MySql? Some databases support cascade delete (first two I mentioned support it for sure). Or there is another way, use trigger. Hook it on delete of one which will trigger the seccond, but firstly turn off consistency on those two tables and in the end turn it on.

Edit: If you are using MSSQL then you can set up cascade delete like this. (if your schema is like Table A has Primary key which is the primary/foreighn key in table B )

ALTER TABLE [dbo].[YourTable]  WITH CHECK ADD  CONSTRAINT     
[FK_YourTable1_YourTable2] FOREIGN KEY([YourForeignKey])
REFERENCES [dbo].[YourTable2] ([Id])
ON DELETE CASCADE
GO