CODEWITHSUNDEEP
mysqldatabase-designforeign-keys
Windle
Windle

Reputation: 1392

Opposite of RESTRICT in MySQL Foreign Key On Delete?

I'm in the process of redesigning some application security log tables (things like when users log in, access different files, etc.) to address some changing requirements. They were originally made with MyISAM, but don't really get accessed that often and switching to InnoDB and adding a bunch of foreign keys for data integrity would really be more beneficial. Since I have to remake the tables anyway, I figure this is as good a time as ever to make the switch.

For the most part, everything is straightforward foreign keys and works as expected. The only part that where I'm trying something weird and hitting problems is with user_ids. Each record in these log tables is associated with a user_id, and I want to make sure the given user_id exists when a record is inserted. Adding a foreign key that references the user table solves that problem - simple stuff. Here are some concise, representative tables:

The User Table

CREATE TABLE tbl_user (
  id INT(10) NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(50),
  PRIMARY KEY(id)
) ENGINE=InnoDB;

Example Log Table

CREATE TABLE tbl_login_time (
  id INT(10) NOT NULL AUTO_INCREMENT,
  user_id INT(10) NOT NULL,
  login_at TIMESTAMP NOT NULL,
  PRIMARY KEY(id),
  CONSTRAINT 'tbl_login_time_fk_1` FOREIGN KEY (user_id) REFERENCES tbl_user
      ON UPDATE CASCADE ON DELETE ???
) ENGINE=InnoDB;

My problem is that I want the foreign key enforced for inserts, updates to be cascaded, but deleting records in tbl_user to not affect tbl_login_time at all. Normally users get marked as inactive, but every once in awhile a user gets deleted entirely yet the logs need to be maintained.

The MySQL docs lists 6 options for ON DELETE, and none of them sound appropriate:

  1. RESTRICT: Would prevent the deletion in tbl_user.
  2. NO ACTION: Gets evaluated just like RESTRICT.
  3. CASCADE: Would delete in tbl_user like I want, but also in tbl_login_time.
  4. SET NULL: Would delete in tbl_user, and leave the row in tbl_login_time but nulls out the data. Close but no cigar.
  5. SET DEFAULT: MySQL recognizes it, but rejects it.
  6. Omit ON DELETE: Equivalent to RESTRICT.

I've never used a foreign key like this before (enforce INSERT and UPDATE but not DELETE), and after reading a lot of other questions it doesn't seem like anyone else does either. That should probably tell me this is the wrong approach, but can it work somehow?

Upvotes: 1

Views: 1200

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95642

My problem is that I want the foreign key enforced for inserts, updates to be cascaded, but deleting records in tbl_user to not affect tbl_login_time at all.

You can't accomplish that with a foreign key constraint.

In some applications, ON DELETE SET NULL makes sense. But your application is essentially a log file stored in a SQL database. You have a significant problem in that you want to delete identifying information (users), but retain their ID numbers in some cases. I frankly don't understand why you're willing to retain the fact that user 23332 logged in at 18:40 today, while not caring whether you can identify who user 23332 is.

You have a few options.

  • Drop the logfile table, and store logfile data in a file in the filesystem, not in the database. If I were in your shoes, I'd consider this first. If we're talking about a database that's somehow accessible over the web, make sure the log file is stored outside the web root. (I'd store it under /var/log with all the other log files.)
  • Use foreign key constraints, and never delete a user.
  • Use foreign key constraints, and live with the effect of ON DELETE SET NULL or ON DELETE SET DEFAULT. In this particular application ON DELETE SET NULL and ON DELETE SET DEFAULT are semantically equivalent. Both replace good data with data that doesn't identify the user. If you can't identify user 23332 anyway, who cares whether you know she logged in at 18:40 today?
  • Drop the foreign key constraints, and use triggers to do whatever you like.

I'm pretty sure we agree that the most obvious option--use foreign keys with ON DELETE CASCADE--is simply wrong for your application.

Upvotes: 2

Related Questions