Use of ON_DELETE in Constraints [closed]

Question

Use of ON_DELETE in Constraints [closed]

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

I am a new computer graduate and I am motivated by all this programming; I am making an administrative system of human resources for my institution (Police) I am using the yiiframework 1.1 I think this framework is quite practical; Now create 5 tables: officials, families, studies, transfers and casualties; Of course the last 4 tables relate to the table "officials"; I have 2 problems is the following: FIRST: I want that when deleting 1 record of the table "Officials" I delete the records of that ID of the Study, Families and Transfers tables. SECOND: in the case of the "Low" table, I want the bitagora or permanent records to be maintained; they told me that using ON_DELETE calling with Constraints, I really do not know how to do it; they could help me within their possibilities ... !!!

phpmyadmin

asked by Yvan Oliveros 21.05.2018 в 15:34

source

2 answers

How to search an arraylist with iterator and modify an object how to define multidimensional array with urls?

score 2 · Answer 1

For the first answer, the "on delete cascade" is so that when the record of the main table is deleted, the daughters tables that have the "on delete cascade" are deleted.

CREATE TABLAPRINCIPAL
(
    ID_TABLA NUMBER(3);
)
ALTER TABLE TABLAPRINCIPAL ADD CONSTRAINT TABLE1_PK PRIMARY KEY 
(ID_TABLA )  ENABLE;
CREATE TABLE TABLE1 
(
  ID_DE_TABLAPRINCIPAL NUMBER(3) 
);
**ALTER TABLE TABLE1 ADD CONSTRAINT TABLE1_FK1 FOREIGN KEY
(  ID_DE_TABLAPRINCIPAL )
REFERENCES TABLAPRINCIPAL
(  ID_TABLA ) ON DELETE CASCADE ENABLE;**

The main point is when you create the foreign key to relate to your main table, put on delete cascade enable.

For the second point, what you have to do is make a logical deletion, and that you only control it with a field in your table, you can put "status" where each one you consult, you will consult the status of the record.

score 0 · Answer 2

RULES OF REFERENTIAL INTEGRITY

ON DELETE CASCADE ON UPDATE CASCADE. It is used to indicate that when the record of the parent table is deleted or deleted, it is also deleted   or modify the record of the daughter table

ON DELETE SET NULL ON UPDATE SET NULL. It is used to give the instruction that when the record of the table is deleted or modified   father, that record in the child table is converted to NULL

ON DELETE NO ACTION ON UPDATE NO ACTION. It is similar to restrict, that is, MySQL will prevent both the action of ON DELETE and ON   UPDATE take place

ON DELETE RESTRICT ON UPDATE RESTRICT They help prevent update or deletion actions between linked tables from being carried out in order to maintain and ensure referential integrity

EXAMPLE

Imagine that I have a users table and a posts table, each post that is recorded is saved with the user's id in the form of a foreign key; then I would stay like this

CREATE TABLE users](
  id INT PRIAMRY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  password VARCHAR(100) NOT NULL
);

CREATE TABLE posts(
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(100) NOT NULL,
  user_id INT NOT NULL /*ESTE campo me servirá como llave foránea*/
  CONSTRAINT fk_posts_users FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);