MySQL Forums
Forum List  »  General

On update doesn't work
Posted by: Jose Maria Cañas
Date: September 07, 2021 01:23PM

Hello, I wish that there are any thread about this issue,



Well, I'm creating a database of a group and I've almost finished it but I've a problem. I've some foreign referred to a primary and I've select on update cascade and on delete Set null.

The on delete works perfect but the on update works as if were on update restrict. I cannot change this PK when it referred through a foreign key. I Will post a short code (I've this problem in all tables). As you could seen prueba field is referred to id_grupo field, but if I change the id_grupo field it doesn't work (if I deleted it yes)

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;


CREATE TABLE `grupos` (
`id_grupo` int(4) NOT NULL,
`grupo` varchar(255) NOT NULL,
`prueba` int(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO `grupos` (`id_grupo`, `grupo`, `prueba`) VALUES
(1, 'Aaaaaa', 113, NULL),
(2, 'bbbb', 25, NULL),
(3, 'cccc', NULL, NULL),
(4, 'dddd', NULL, 3),

ALTER TABLE `grupos`
ADD PRIMARY KEY (`id_grupo`),
ADD KEY `fk-prueba` (`prueba`);



ALTER TABLE `grupos`
MODIFY `id_grupo` int(4) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;


ALTER TABLE `grupos`
ADD CONSTRAINT `fk-prueba` FOREIGN KEY (`prueba`) REFERENCES `grupos` (`id_grupo`) ON DELETE SET NULL ON UPDATE CASCADE,
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Options: ReplyQuote


Subject
Written By
Posted
On update doesn't work
September 07, 2021 01:23PM
September 07, 2021 02:49PM
September 08, 2021 03:18AM
September 08, 2021 03:29AM
September 08, 2021 08:46AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.