On 6 Dec 2004 07:19:30 -0800,
g1**@hotmail.com (Chris) wrote:
Hi, sorry to post OT but i cant find the MySQL newsgroup, however i am
hoping to pick up on some expert advice from php/mysql gurus here. I'm
having some trouble performing a delete across two tables.
The tables i have are:
questionnaires (id, name);
questionnaire_questions (questionnaires_id, id, name, qf_type)
The questionnaire_questions table contains a list of questions for a
specific questionnaire (indicated by the questonnaires_id). I would
like to remove a questionnaire, and when doing so all corresponding
questionnaire_questions who have matching id's (questionnaires.id =
questionnaire_questions.questionnaires_id).
I have this statement, however it only works if a questionnaire has
questions... however i would like it to delete even if no questions
exist for that specific questionnaire. The SQL i am using is:
delete questionnaires, questionnaire_questions FROM questionnaires,
questionnaire_questions WHERE questionnaires.id =
questionnaire_questions.questionnaires_id AND questionnaires.id =
THE_QUESTIONAIRE_TO_DELETE_ID
Add a foreign key constraint from questionnaire_questions to questionnaires
(you should have this anyway) and declare it as 'on delete cascade'.
http://dev.mysql.com/doc/mysql/en/In...nstraints.html
mysql> create table questionnaires (
-> id int not null auto_increment primary key,
-> name varchar(16) not null
-> )
-> TYPE=INNODB;
Query OK, 0 rows affected (0.35 sec)
mysql> create table questionnaire_questions (
-> questionnaires_id int not null,
-> id int not null auto_increment primary key,
-> name varchar(16) not null,
-> index (questionnaires_id),
-> foreign key (questionnaires_id)
-> references questionnaires (id)
-> on delete cascade
-> )
-> TYPE=INNODB;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into questionnaires values (null, 'Quest.1');
Query OK, 1 row affected (0.05 sec)
mysql> insert into questionnaires values (null, 'Quest.2');
Query OK, 1 row affected (0.00 sec)
mysql> insert into questionnaire_questions values (1, null, 'Q1');
Query OK, 1 row affected (0.02 sec)
mysql> select * from questionnaires;
+----+---------+
| id | name |
+----+---------+
| 1 | Quest.1 |
| 2 | Quest.2 |
+----+---------+
2 rows in set (0.03 sec)
mysql> select * from questionnaire_questions;
+-------------------+----+------+
| questionnaires_id | id | name |
+-------------------+----+------+
| 1 | 1 | Q1 |
+-------------------+----+------+
1 row in set (0.01 sec)
mysql> delete from questionnaires;
Query OK, 2 rows affected (0.08 sec)
mysql> select * from questionnaire_questions;
Empty set (0.00 sec)
--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
