Problems with on update
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 */;