0

Take the following data set:

Users
(int)   (varchar)       (varchar)     (varchar)
id    |   username   |   password   | stuff   |
1         john           cat          stuff
2         jack           dog          stuff
3         bill           bird         stuff
.
.
.
n         userN          lol          stuff

and

Colors
(int)          (varchar)
userid     |    color
1               green
2               green
3               yellow
.               .
.               .
.               .
n               purple

Now we obviously can see that the colors and users are linked by the userid. Our proper user deletion script goes and removes all the matching userids from the colors table and then deletes the user in the users table. Let's say we have a lazy admin who manually SSH's into the server and deletes a user from the users table. Let's assume that "john" was deleted.

We then have an issue when our color retrieval script runs (PHP for simplicity):

<?
$query = "SELECT * FROM colors WHERE color = 'green'";
$result = mysql_query($query);

while ($row = mysql_fetch_asoc($result)) {
     //The code bricks here.
     $query2 = "SELECT * FROM users WHERE id = " . $row['id']. " LIMIT 1";
     $result2 = mysql_query($query2);
     $row2 = mysql_fetch_asoc($result2);
     echo $row2['username'] . "<br />"
}

?>

Is there a concept in MySQL itself that will not allow an admin to DELETE the entry in the users table if that userid is in the colors table. Pardon my ignorance, I am not a DBA.

ola_992
  • 3
  • 2
  • On a side note, if you want to pose a questions to a DBA, there's [a Stack Exchange](http://dba.stackexchange.com) for that. – xathien Mar 30 '15 at 20:37

1 Answers1

0

What you want are FOREIGN KEYs with ON DELETE CASCADE. A FOREIGN KEY constraint will require that userid on the Colors table references a value on another table. Read up on them here.

When creating the table, you can specify your key like this:

CREATE TABLE users (
    id BIGINT,
    (other columns etc)
);

CREATE TABLE colors (
    userid BIGINT,
    (other columns etc),
    FOREIGN KEY `userid_fk` (`userid`) REFERENCES users(`id`) ON DELETE CASCADE
 );

What that line indicates: You want a foreign key named userid_fk on the userid column. Any value in the userid column must exist in the users table's id column. With this, even if someone deletes a users row, the delete will CASCADE, meaning it will also delete all relevant rows on the colors table.

xathien
  • 812
  • 7
  • 10