Sql, on Delete Cascade and on Update Cascade

When to use ON UPDATE CASCADE

It's true that if your primary key is just an identity value auto incremented, you would have no real use for ON UPDATE CASCADE.

However, let's say that your primary key is a 10 digit UPC bar code and because of expansion, you need to change it to a 13-digit UPC bar code. In that case, ON UPDATE CASCADE would allow you to change the primary key value and any tables that have foreign key references to the value will be changed accordingly.

In reference to #4, if you change the child ID to something that doesn't exist in the parent table (and you have referential integrity), you should get a foreign key error.

Difference between On Delete Cascade & On Update Cascade in mysql

on delete cascade

It will delete all the child records when parent record is deleted, so
that there will be no child record when parent is deleted.

on update cascade

It will update the child records parent_id when parent record id that
change. In rare case we use on update cascade

eg:- suppose your parent id is 4 digit and due to expansion later on
you need change it to 10 digit. In that case, ON UPDATE CASCADE would
allow you to change the primary key value and any tables that have
foreign key references to the value will be changed accordingly.

SQL, On delete cascade and on update cascade

There is no "on update cascade" in Oracle as far as I know (even in current versions):

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5773459616034

Syntax Error for ON UPDATE CASCADE ON DELETE CASCADE

Oracle does not support cascades on updates, only deletes. So this works fine:

create table Doctor(
    SSN int not Null,
    Doc_id int not Null,
    Dept_id int not Null,
    HireDate Date not Null,
    foreign key(SSN) references Person(SSN) on delete cascade
);

See here.

This is generally not a big limitation because you shouldn't be changing primary key values anyway.

Also, don't store social security numbers as integers. They are really strings that happen to be digits. In the US, at least, they can have leading zeros.

foreign key constraint on update cascade on delete cascade

Well, for the case of delete, when you delete the row from table 1, all the rows corresponding to that id will be deleted from table 2.

In case of update, if you want first name and last name to be updated in table 2 when they are altered in table 1 then you will have to write an update trigger for that.

SQL Learner -- MS Access 2013 ON UPDATE CASCADE and ON DELETE CASCADE

The ON UPDATE CASCADE and ON DELETE CASCADE statements are not supported by Access (see https://msdn.microsoft.com/en-us/library/office/ff836971.aspx).

Regarding the functionality, you should not have a need to use ON UPDATE CASCADE. This constraint means that if you do change a primary key in the master table, the changes propagate to every child table referencing the master table. Changing a primary key is considered a no-go in the SQL world. Is you really must change primary keys (because of some kind of disaster struck), this will be done with a script, lots of backup and extreme care. A primary key is this: a unique (ideally globally), immutable identifier of a row in a table.

The ON DELETE CASCADE means that if you remove a row in the master table, any rows referencing this key in child tables will also be deleted. While this sounds like a lazy shortcut, I would recommend against it and doing this within application logic (there might be a use case where you want to retain records or log them or do something with them instead of blindly erasing them from the database.

Foreign Key constraint one to many tables while on delete ,on update cascade rule

Please try the following:

Table 1

CREATE TABLE emp(  
       eID INT NOT NULL,  
       eName VARCHAR(45) NULL,  
       PRIMARY KEY(eID)  
);

Table 2

CREATE TABLE empLocation(  
       eLocateID INT NOT NULL,
       eArea VARCHAR(45) NULL,    
       eCity VARCHAR(45) NULL,  
       eZipcode VARCHAR(45) NULL,
       eID INT NULL,  
       PRIMARY KEY(eLocateID),
       CONSTRAINT eID
           FOREIGN KEY (eID)
           REFERENCES emp(eID)
              ON DELETE CASCADE
              ON UPDATE CASCADE
);

Table 3

CREATE TABLE empLogin(  
       eLoginID INT NOT NULL,
       eTimeIn TIMESTAMP NULL,    
       eTimeOut TIMESTAMP NULL,  
       eID INT NULL,  
       PRIMARY KEY(eLoginID)
);

ALTER TABLE empLogin ADD FOREIGN KEY (eID) REFERENCES emp(eID) 
            ON DELETE CASCADE 
            ON UPDATE CASCADE;

Then,

INSERT INTO emp VALUES(1, 'John'), (2, 'Paul');
INSERT INTO empLogin VALUES
    (1, '2017-01-27 09:00:00', '2017-01-27 18:00:00', 1),
    (2, '2017-01-27 09:30:00', '2017-01-27 18:30:00', 2);

Seems to work fine this way.

---

Related Topics