MySQL Forums
Forum List  »  Spanish

Problems with on update
Posted by: Jose Maria Cañas
Date: September 07, 2021 12:44PM

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
Views
Written By
Posted
Problems with on update
324
September 07, 2021 12:44PM
188
September 07, 2021 01:05PM
175
September 08, 2021 03:22AM
152
September 08, 2021 12:50PM


Sorry, you can't reply to this topic. It has been closed.

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.