CODEWITHSUNDEEP
mysqlsql
black
black

Reputation: 745

foreign key constraint on update cascade on delete cascade

I have two tables: one and two. I have a primary key (id) in table one.

Table One:

CREATE TABLE `one` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`first_name` varchar(10) NOT NULL,
`last_name` varchar(10) NOT NULL,
`salary` int(100) NOT NULL,
`login_date_time` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1

id    first_name    last_name    salary  login_date_time

Table Two

CREATE TABLE two AS (SELECT id,salary ,CONCAT(first_name, ',', last_name) AS Name from one)

Now adding the foreign key to id as:

ALTER TABLE two
ADD CONSTRAINT `0_38775` FOREIGN KEY (id)
REFERENCES one(id) ON DELETE CASCADE ON UPDATE CASCADE

the table two comes as with the values from table one:

id    salary       name

Now to maintain referential integrity, how to update the values of table two when table one is updated. And also I cannot delete rows from table one and cannot insert rows to table two.

How can I update all the columns of table two when table one is updated/inserted. or insert explicitly into table two

Upvotes: 1

Views: 12140

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30839

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.

Upvotes: 2

Related Questions