LINUX.ORG.RU
Форум — Development  

[SQL] Как сделать автоматическое удаление записей на которые нет ссылок?

 


0

0

Есть две таблицы one и two. В первой есть внешний ключ two_id. Во второй соответственно первичный ключ id. Связь один ко многим - одно поле второй таблицы может соответствовать многим полям первой. Кроме этого, полю второй таблицы обязательно должно соответствовать хотя бы одно поле первой.

Есть ли стандартные способы такое организовать?

Хочется чтобы поле из второй таблицы само удалялось если на него никто из первой таблицы не ссылается. Можно завести счётчик ссылок: 

 Table one:    Table two:
 +--------+    +------+-------------+
 | two_id |    | id   | ref_counter |
 +--------+    +------+-------------+
 |      1 |    |    1 |           3 |
 |      1 |    |    2 |           1 |
 |      1 |    +------+-------------+
 |      2 |
 +--------+
И самому его изменить/проверять и удалять строку когда ref_counter станет равным нулю. Но это выглядит не Ъ. Что делать?

Ответ на: комментарий от Pi

Точно, можно проверки и изменения в триггер засунуть. Я об этом думал, но забыл написать.

Но хочется что-нибудь более стандартное чтобы без поля ref_counter обойтись.

LinuxUser ★★★
() автор топика
Ответ на: комментарий от ef37

> ON DELETE CASCADE ?

Нет, это в обратную сторону работает.

const86 ★★★★★
()

вторая таблица должна быть создана с foreign key. а потом использовать каскадное удаление.

Deleted
()

короче, зависит от базы. myisam в принципе не поддерживает ссылочную ценность. А у innodb и postgres это в крови. Т.е. при попытке руками испортить базу оно будет ругаться.

true_admin ★★★★★
()

У mysql это innodb с foreign key. Всё можно наглядно в phpMyAdmin сделать.

В других СУБД - аналогично.

KRoN73 ★★★★★
()
Ответ на: комментарий от KRoN73

Не работает: 

mysql> CREATE TABLE one (id INT PRIMARY KEY) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE two (one_id INT REFERENCES one (id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO one VALUES (1);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO two VALUES (1);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM one, two;
+----+--------+
| id | one_id |
+----+--------+
|  1 |      1 | 
+----+--------+
1 row in set (0.01 sec)
Теперь удаляю запись из первой таблицы. Во второй запись остаётся, не смотря на то, что она теперь ссылается на удалённую запись первой таблицы: 
mysql> DELETE FROM one WHERE id = 1;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM one;
Empty set (0.01 sec)

mysql> SELECT * FROM two;
+--------+
| one_id |
+--------+
|      1 | 
+--------+
1 row in set (0.00 sec)

mysql>

LinuxUser ★★★
() автор топика
Ответ на: комментарий от LinuxUser 
mysql> CREATE TABLE IF NOT EXISTS `two` (
    ->   `one_id` int(11) default NULL,
    ->   KEY `one_id` (`one_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE `two`
    ->   ADD CONSTRAINT `two_ibfk_1` FOREIGN KEY (`one_id`) REFERENCES `one` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO one VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO two VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM one, two;
+----+--------+
| id | one_id |
+----+--------+
|  1 |      1 | 
+----+--------+
1 row in set (0.00 sec)

mysql> DELETE FROM one WHERE id = 1;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM one;
Empty set (0.00 sec)

mysql> SELECT * FROM two;
Empty set (0.00 sec)

Я же говорю - смотри в phpMyAdmin :) Там у innodb таблиц есть раздел «Связи» (соответствующий код создания таблицы и ключа взят оттуда).

KRoN73 ★★★★★
()
Ответ на: комментарий от KRoN73

Спасибо, работает. Можно без ALTER TABLE, сразу сделать: 

CREATE TABLE two
(
  one_id INT,
    FOREIGN KEY (one_id)
    REFERENCES one (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB;

LinuxUser ★★★
() автор топика
Ответ на: комментарий от LinuxUser

> Спасибо, работает.

хм, у Вас в первом посте написано что two_id в первой таблице не уникален. интересно, как Вы сделаете внешний ключ ссылающийся на колонку с _не уникальными_ значениями ? это типа "ссылается на какую-то строку вот из этих пяти штук" ? :)

Eshkin_kot ★★
()
Ответ на: комментарий от Eshkin_kot

Убивается запись с первичным ключом во второй таблице - и убиваются все записи со ссылкой на этот ключ из первой.

KRoN73 ★★★★★
()
Ответ на: комментарий от KRoN73

KRoN73> Убивается запись с первичным ключом во второй таблице - и убиваются все записи со ссылкой на этот ключ из первой.

а автору топика в первом его сообщении нужно было наоборот, при удалении из первой таблицы:

LinuxUser> Хочется чтобы поле из второй таблицы само удалялось если на него никто из первой таблицы не ссылается

Eshkin_kot ★★
()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.
Development