SQL delete request with foreign key

Question

I know this question refers to the earliest stages of database theory, but I have not encountered such a problem since several months. If someone has a database with some tables linked together as a “chain” with foreign keys, and they want to delete a record from a table with some “dependent” tables, what are the obstacles? In particular, associations exist in the database with tables: Person, Profile, Preference, Filter, because Person.id is a foreign key in Profile and Profile.id is a foreign key in Preference and Filter.id is a foreign key in Preference , so that’s all the enter code here associations are equal to OneToMany . Is it possible to remove Person with a simple query:

 Delete from Person p where p.id= 34; 

If not, what should the request look like to successfully complete the deletion? If the database in the application is hibernated, what annotations should be applied to the associated fields of each object in order to be able to perform deletion with a simple simple query?

Answer 1

FOR SQL VERSION

Take a look at the screenshot. You can use "Paste Updates". since it has deletion and update rules. You can set any of these values.

Foreign key constraints can be created by referencing a primary or unique key. Foreign key constraints provide relational data integrity in linked tables. The foreign key value can be NULL and indicates that the particular record does not have a parent record. But if the value exists, then it must have a related value in the parent table. When applying update or delete operations in parent tables, there may be different requirements for influencing related values ​​in child tables. Four options are available in SQL Server 2005 and 2008:

 No Action Cascade SET NULL SET Default 

Use this article for Refrence.

http://www.mssqltips.com/sqlservertip/2365/sql-server-foreign-key-update-and-delete-rules/

VERSION ORACLE

You can use one of the below.

modify table sample1 add foreign key (col1) Recommendations sample (col2) on no no action;

modify table sample1 add foreign key (col1) Recommendations sample (col2) to limit deletion;

modify table sample1 add foreign key (col1) Sample link (col2) when deleting the cascade;

for advertising.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm

Answer 2

the answer is no, if there is a foreign key constraint, then you need to delete the node table data first

which is first removed from the Preference table

then from the Profile and Filter table

then delete the entry from Person table

This is a general concept that you apply anywhere.