Problém s cizím klíčem při mazání

V databázi máš omezení proti odstranění záznamu referencovaného cizím klíčem. Jestli chceš smazat i související záznamy, nastav u toho cizího klíče ON DELETE CASCADE

No ale je divné že mám na tabulce

ON DELETE CASCADE

ale i tak to nefunguje…

David Matějka napsal(a):

ukaz dump databaze

Tady:

-- Adminer 4.6.2 MySQL dump

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `window_category`;
CREATE TABLE `window_category` (
  `window_join_category_id` int(11) NOT NULL AUTO_INCREMENT,
  `window_id` int(11) NOT NULL,
  `window_category_id` int(11) NOT NULL,
  PRIMARY KEY (`window_join_category_id`),
  KEY `window_category_id` (`window_category_id`),
  KEY `window_id` (`window_id`),
  CONSTRAINT `window_category_ibfk_1` FOREIGN KEY (`window_id`) REFERENCES `window` (`window_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `window_category_ibfk_2` FOREIGN KEY (`window_category_id`) REFERENCES `category` (`window_category_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;


-- 2018-06-12 12:29:17

David Matějka napsal(a):

to je dump uplne jine tabulky, si precti chybovou hlasku – tam ti to hlasi chybu u constraint window_color_ibfk_1

Ajo, no ale jde o to že tabulka color je napojená na window_category pomocí window_join_category_id… se kterýma pracuji… a bylo by dobrý kdyby se smazal jen ten záznam ve window_category a zbytek zůstal..

Editoval jAkErCZ (12. 6. 2018 15:04)

David Matějka napsal(a):

ukaz prosim dump vsech dotcenych tabulek (color, category, window_color, window_category, window)

Dobře tady:

-- Adminer 4.6.2 MySQL dump

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `category`;
CREATE TABLE `category` (
  `window_category_id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(255) COLLATE utf8_czech_ci NOT NULL,
  `title` varchar(255) COLLATE utf8_czech_ci NOT NULL,
  `text` text COLLATE utf8_czech_ci NOT NULL,
  `order_no` int(11) NOT NULL,
  `hidden` tinyint(4) NOT NULL,
  `parent_category_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`window_category_id`),
  KEY `parent_category_id` (`parent_category_id`),
  CONSTRAINT `category_ibfk_1` FOREIGN KEY (`parent_category_id`) REFERENCES `category` (`window_category_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;


DROP TABLE IF EXISTS `window`;
CREATE TABLE `window` (
  `window_id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(255) COLLATE utf8_czech_ci NOT NULL,
  `url` varchar(255) COLLATE utf8_czech_ci NOT NULL,
  `title` varchar(255) COLLATE utf8_czech_ci NOT NULL,
  `short_description` varchar(255) COLLATE utf8_czech_ci NOT NULL,
  `description` text COLLATE utf8_czech_ci NOT NULL,
  `class` varchar(255) COLLATE utf8_czech_ci NOT NULL,
  `uw` varchar(255) COLLATE utf8_czech_ci NOT NULL,
  `hex` varchar(10) COLLATE utf8_czech_ci NOT NULL,
  `images_count` int(11) NOT NULL,
  `hidden` tinyint(4) NOT NULL,
  PRIMARY KEY (`window_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;


DROP TABLE IF EXISTS `window_category`;
CREATE TABLE `window_category` (
  `window_join_category_id` int(11) NOT NULL AUTO_INCREMENT,
  `window_id` int(11) NOT NULL,
  `window_category_id` int(11) NOT NULL,
  PRIMARY KEY (`window_join_category_id`),
  KEY `window_category_id` (`window_category_id`),
  KEY `window_id` (`window_id`),
  CONSTRAINT `window_category_ibfk_1` FOREIGN KEY (`window_id`) REFERENCES `window` (`window_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `window_category_ibfk_2` FOREIGN KEY (`window_category_id`) REFERENCES `category` (`window_category_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;


DROP TABLE IF EXISTS `window_color`;
CREATE TABLE `window_color` (
  `color_id` int(11) NOT NULL AUTO_INCREMENT,
  `window_join_category_id` int(11) DEFAULT NULL,
  `title` varchar(20) DEFAULT NULL,
  `hex` varchar(10) NOT NULL,
  `images_count` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`color_id`),
  KEY `window_join_category_id` (`window_join_category_id`),
  CONSTRAINT `window_color_ibfk_3` FOREIGN KEY (`window_join_category_id`) REFERENCES `window_category` (`window_join_category_id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `window_detail`;
CREATE TABLE `window_detail` (
  `detail_id` int(11) NOT NULL AUTO_INCREMENT,
  `window_join_category_id` int(11) DEFAULT NULL,
  `title` varchar(20) NOT NULL,
  `description` text NOT NULL,
  `images_count` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`detail_id`),
  KEY `window_join_category_id` (`window_join_category_id`),
  CONSTRAINT `window_detail_ibfk_1` FOREIGN KEY (`window_join_category_id`) REFERENCES `window_category` (`window_join_category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- 2018-06-12 13:08:32

David Matějka napsal(a):

predpokladam, ze si ty klice znovu vytvarel a jde tedy o window_color_ibfk₃. ted by se to pri smazani ve window_category take melo smazat, pokud chces, aby ti ve window_color ten radek zustal, tak tam dej „on delete set null“ a hodnota ve window_join_category_id se nastavi na null. a pak tam mas jeste window_detail_ibfk₁, kde zadne „on delete“ pravidlo nemas

Jenže ta funkce to má aktualizovat :D

public function updateWindowCategories($windowId, $categories)
{
    $this->database->table(WindowsRepository::TABLE_NAME . '_' . self::TABLE_NAME)
        ->where(WindowsRepository::COLUMN_ID, $windowId)->delete();
    $rows = array();
    foreach ($categories as $category) {
        $rows[] = array(
            WindowsRepository::COLUMN_ID => $windowId,
            self::COLUMN_ID => $category,
        );
    }
    if (!empty($rows)) {
        $this->database->table(WindowsRepository::TABLE_NAME . '_' . self::TABLE_NAME)->insert($rows);
    }
}

David Matějka napsal(a):

myslim, ze to mas spatne navrzene… nemelo by spise to category id u color a detail odkazovat na tabulku „category“ nez na tu window_category?

No ale přece mám tabulky color,detail,window,category a ta tabulka window_category všechny spojuje. Tudíž myslím že navrhnuté to mám dobře :)

David Matějka napsal(a):

window_category spojuje window a category, ne? proc by na to melo mit vazbu window_color? kdybys to mel navrzeny jak rikam, tak neresis problemy, ktery ted resis

Ano spojuje, no ale jde o to že ten color a detail mám napojený na window…

David Matějka napsal(a):

ale ted to mas napojeny na tu spojovaci tabulku window_category a ne na window. preci barva a detail je nezavisle na kategorii, ne?

Máš pravdu přehodím strukturu :) díky