SQL, relacja na kilka tabeli

Question

Witam, mam problem z tym kodem:

CREATE table logs (
 id BIGINT UNSIGNED PRIMARY KEY,
  log_date DATETIME NOT NULL,
  log_action VARCHAR(255) NOT NULL,
  log_user_id BIGINT UNSIGNED NOT NULL,
  object_id BIGINT UNSIGNED NOT NULL,
  object_type ENUM('users', 'usergroups', 'permissions') NOT NULL,
  FOREIGN KEY (log_user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (object_id) REFERENCES  
    CASE
      WHEN object_type = 'users' THEN users(id)
      WHEN object_type = 'usergroups' THEN usergroups(id)
      WHEN object_type = 'permissions' THEN permissions(id)
    END
);

 

PMA wyrzuca problemy z nawiasami
 

2 errors were found during analysis.

 

1. An opening bracket was expected. (near ")" at position 568)
2. A closing bracket was expected. (near ")" at position 568)

Answer 1

W SQL nie możesz użyć CASE expression kiedy używasz do określania kluczy z słówkiem kluczowym REFERENCES dla tworzenia  FOREIGN KEY.
Poprawnie zapisać możesz to tylko tak. 
 

CREATE TABLE logs (
 id BIGINT UNSIGNED PRIMARY KEY,
 log_date DATETIME NOT NULL,
 log_action VARCHAR(255) NOT NULL,
 log_user_id BIGINT UNSIGNED NOT NULL,
 object_id BIGINT UNSIGNED NOT NULL,
 object_type ENUM('users', 'usergroups', 'permissions') NOT NULL,
 FOREIGN KEY (log_user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY (object_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY (object_id) REFERENCES usergroups(id) ON DELETE CASCADE,
 FOREIGN KEY (object_id) REFERENCES permissions(id) ON DELETE CASCADE
);

Ty tworzysz tabele, a tworzenie definicji tabeli jest jedno - więc twój ENUM jest workiem na dane, ale twój foreign key nie może dotyczyć się rekordu który zostanie wpisany, bo na tym poziomie (definicji), jest to niemożliwe. 

Możesz jedynie to zrobić dynamicznie z poziomu kodu, np  skorzystać z połączenia dynamicznego SQL i prepared statements.

DELIMITER //

CREATE PROCEDURE add_object_fk(IN type ENUM('users', 'usergroups', 'permissions'))
BEGIN
    SET @sql = CONCAT('ALTER TABLE logs ADD FOREIGN KEY (object_id) REFERENCES ', type, '(id) ON DELETE CASCADE');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END//

DELIMITER ;

i uruchomić to z poziomu jakiegoś skryptu 

CALL add_object_fk('users');

Jeśli nie potrafisz korzystać z procedur składowych MSQL to możesz ostatecznie dodać skrypt z alterami po definicji tabeli

ALTER TABLE logs ADD CONSTRAINT fk_logs_users FOREIGN KEY (object_id) REFERENCES users(id) ON DELETE CASCADE WHERE object_type = 'users';
ALTER TABLE logs ADD CONSTRAINT fk_logs_usergroups FOREIGN KEY (object_id) REFERENCES usergroups(id) ON DELETE CASCADE WHERE object_type = 'usergroups';
ALTER TABLE logs ADD CONSTRAINT fk_logs_permissions FOREIGN KEY (object_id) REFERENCES permissions(id) ON DELETE CASCADE WHERE object_type = 'permissions';

Chociaż isntrukcji CALL możesz wykonać z poziomu SQL.