I want to automatically delete rows when the table (shown below) gets a new insert, if certain conditions are met.
When:
- There are rows referring to the same 'field' with the same 'user_id'
- Their 'field', 'display' and 'search' columns are the same
Simply, when the rows would become duplicates (except the 'group_id' column) the non null 'group_id' should be deleted, otherwise a row should be updated or inserted.
Is there a way to set this up in mysql (in spirit of "ON DUPLICATE do stuff" combined with unique keys etc.), or do I have to explicitly check for it in php (with multiple queries)?
Additional info: There should always be a row with NULL 'group_id' for every possible 'field' (there's a limited set, defined elsewhere). On the other hand there might not be one with a non null 'group_id'.
CREATE TABLE `Views` (
`user_id` SMALLINT(5) UNSIGNED NOT NULL,
`db` ENUM('db_a','db_b') NOT NULL COLLATE 'utf8_swedish_ci',
`field` VARCHAR(40) NOT NULL COLLATE 'utf8_swedish_ci',
`display` TINYINT(1) UNSIGNED NOT NULL,
`search` TINYINT(1) UNSIGNED NOT NULL,
`group_id` SMALLINT(6) UNSIGNED NULL DEFAULT NULL,
UNIQUE INDEX `user_id` (`field`, `db`, `user_id`),
INDEX `Views_ibfk_1` (`user_id`),
INDEX `group_id` (`group_id`),
CONSTRAINT `Views_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `User` (`id`) ON
UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_swedish_ci'
ENGINE=InnoDB;