reflexive refrential integrity - possible in mysql

sfullman

OK, here's a new one for me, and you all probably know the use of a joining table:

table: students

StudentID
FirstName
LastName
..
..
etc.

table: classes

ClassID
Title
Date
..
..
etc.

OK, here's the joining table

table: StudentsClasses

StudentID
ClassID
Notes (optional)

so that third table joins students to classes many-to-many, right?

I already know how to set it up so that the join table keys refer to the "parent" keys in both students and classes, so that if I delete student or class, the join records delete.

BUT..
I want to set it up also so that if I delete the entry in StudentsClasses, both the Student and the Class are removed.

Just FYI, I used students and classes as a simple example.

My thought is you could do this if you set one or both keys to null, but is this possible? is it common? and is the method pretty straightforward and elegant or does it take a hack?

MnyMnyThanks,
Samuel

tomhath

It would only make sense to do that if the relationship was one-to-one. In which case you don't need the third table (and if the relation was reflexive you wouldn't even have a second table per my understanding of reflexive).

But if you really want it this way, create a delete trigger on the third table.

sfullman

here's where I think I dont' understand all that ref integrity has to offer. If you have students and classes in a many-to-many relationship, normally deletion of the student means (duh) you need to delete the student-class entry else @least set it null on the student side (it has no meaning).

Again, Normally ya don't want to delete the student OR the class when you delete the student-class entry - after all they still both exist in that case. But in one of my db aps I want cascade delete to go both ways. That's what I was asking about - and bTW how do you specify a "one-to-one" relationship in mysql? I know you can do it in access but that was years ago.

BTW, I have tried setting referential integrity (on delete|update cascade) in both directions on 3 sample tables in phpMyAdmin, no success on this - it appears to set but then you can't add a name to students because, there's nobody in the join table :-(

Thanks,
Samuel

liquorvicar

I don't think what you originally asked about is possible in SQL. If you delete a row in the join table, there may be other rows in the join table referring to either the student or the class so doing a cascade delete on the student and the class wouldn't make sense. Cascade deletes only make sense when you delete the parent in a one-to-many relationship.
Consider this example:

Students

1
2
3

Classes

1
2
3

Student-classes

1-1
1-2
1-3
2-3
3-3

Let's say we were to delete student-class 1-3. If you were to cascade delete the relevant items, our DB would look like this:

Students

2
3

Classes

1
2

Student-classes

1-1
1-2
2-3
3-3

Now, none of the join rows has any relevance and you have broken the integrity of your data.

It sounds like you need to implement your integrity checks in your logic (PHP code) rather than at the DB level.

tomhath

For one-to-one you just have a foreign key from one table pointing to the primary key of the other. Make the foreign key non-null to ensure it always points to another row. Then you can cascade delete from the primary key table.

A trigger is nothing more than a stored procedure that is executed when the table is modified (e.g. a delete trigger runs when a row is deleted). You can put whatever logic you want in the procedure, including deleting rows from other tables. So you could check if the student or class has any other links before deleting those rows.

The advantage of using a trigger over application code is that it will always enforce the integrity, not just when the programmer remembers. It's also IMHO cleaner and usually faster to keep it in the database rather than scatter database integrity code around the application (but then, I'm a DBA so my opinion is somewhat biased 😃 ).

liquorvicar

Good point tomhath, my DB experience doesn't extend to triggers (...yet) but your point about putting the logic into the DB rather than the app is spot on.

tomhath

Over in the Java world you'll find a lot of support for moving this kind of processing out of the database and into an abstraction layer that sits between the app and the db. Read up on ORM (Object Relational Mapping) and Hibernate. It has some merit, but I think it's a fad that's being oversold.

Roger_Ramjet

Tom, I dunno about this being a 'fad', it's old news in the VB world dating back to VB4 and Rockford Lhotka's book Business Objects @1995. Where you locate the business logic is really a matter of choice depending on the application type. In the db for a transaction processing system, in server-side objects when in a more diverse and unpredictable system.

As a dba you know that often the sql processing logic breaks out in a very different way than it does in code, but is so much faster because of it. I used to see a lot of excellent programmers here being very uncomfortable around sql and dbs; it's getting better lately. I'm sure that some of the Java world support stems from the same discomfort.

sfullman

so my question still remains, how do you do a reflexive referential integrity?

how 'bout I make the question a bit more simple:

you have 2 tables:

table1

id int(11) unsigned primary key auto_inc
data1
data2
etc.

table 2

id int(11) unsigned primary key auto_inc
table1_id int(11) unsigned [indexed]
data1
data2
etc.

any way to make it so that deleting any record in table 2 where table1_id = 5 (for example) will delete the parent record in table 1 AND - AND all other records in table 2 with table1_id = 5 ???

alternately, in mysql can you do this:

table1

id int(11) unsigned primary key auto_inc
data1
data2
etc.

table 2

id int(11) unsigned primary key auto_inc
data1
data2
etc.

can you make it so that both tables have the same id numbers and deleting id=5 in table2 will also delete id=5 in table 2 and vice versa (I call that one-to-one)?

thank you, if I can avoid triggers and the like (have never used) it would be reeeally great..

Samuel

tomhath

Since table1 is the parent table, I'd delete that row first and let a foreign key cascade to table2:

CREATE TABLE table1 (id INT NOT NULL  AUTO_INCREMENT,
                     data1 varchar(10),
                     PRIMARY KEY(id)) ENGINE=INNODB;

CREATE TABLE table2 (id INT NOT NULL AUTO_INCREMENT,
                     table1_id INT NOT NULL,
                     data1 varchar(10),
                     PRIMARY KEY(id),
                     INDEX (table1_id),
                     FOREIGN KEY (table1_id) REFERENCES table1(id)
                     ON DELETE CASCADE) ENGINE=INNODB;

insert into table1 (data1) values ('row1');
insert into table1 (data1) values ('row2'); 

insert into table2 (table1_id, data1) values (1,'row21');
insert into table2 (table1_id, data1) values (2,'row22');
insert into table2 (table1_id, data1) values (2,'row23');

select * from table1;
select * from table2;

delete from table1 where id = 2;

select * from table1;
select * from table2;

drop table table2;
drop table table1;

If you really want to delete the row from table2 first, the only way I can see to do it is to combine the cascading delete from table1 with a trigger on table2:

CREATE TABLE table1 (id INT NOT NULL  AUTO_INCREMENT,
                     data1 varchar(10),
                     PRIMARY KEY(id)) ENGINE=INNODB;

CREATE TABLE table2 (id INT NOT NULL AUTO_INCREMENT,
                     table1_id INT NOT NULL,
                     data1 varchar(10),
                     PRIMARY KEY(id),
                     INDEX (table1_id),
                     FOREIGN KEY (table1_id) REFERENCES table1(id)
                     ON DELETE CASCADE) ENGINE=INNODB;

insert into table1 (data1) values ('row1');
insert into table1 (data1) values ('row2');

insert into table2 (table1_id, data1) values (1,'row21');
insert into table2 (table1_id, data1) values (2,'row22');
insert into table2 (table1_id, data1) values (2,'row23'); 

DELIMITER //
CREATE TRIGGER t1_t2
AFTER DELETE ON table2
FOR EACH ROW BEGIN
 delete from table1 where id = old.table1_id;
END;
//
DELIMITER ;

select * from table1;
select * from table2;

delete from table2 where id=2;

select * from table1;
select * from table2;

drop table table2;
drop table table1;