0

I'm planning to make a notification system that sends out different types of messages to users (private messages among users, message about their posts being published etc) What I'm looking for is some sort of conditional foreign key on the column this_id in notification table so that it can find a specific row from either table comments or pm, and I have found this thread that's exactly what I want. So I create a table "supertable" with just one column (SERIAL PRIMARY KEY) to be referenced by comments and PM. And to make sure a new row is insert into the supertable first to generate a key before any new row inserts into either comments and PM, I set up two BEFORE INSERT INTO triggers 

CREATE TRIGGER `before_insert_comments` 
    BEFORE INSERT ON `comments`
    FOR EACH ROW BEGIN
    INSERT INTO supertable (this_id) VALUES ('')
END;

CREATE TRIGGER `before_insert_pm` 
    BEFORE INSERT ON `PM`
    FOR EACH ROW BEGIN
    INSERT INTO supertable (this_id) VALUES ('')
END;

But when inserting a record into table comments or PM, I'm still getting the error Cannot add or update a child row: a foreign key constraint fails ( CONSTRAINT comments FOREIGN KEY (this_id) REFERENCES supertable (this_id) ON DELETE CASCADE ON UPDATE CASCADE). Anyone know what's the problem with the triggers?

Table schema 

CREATE TABLE notification (
  id INT NOT NULL AUTO_INCREMENT,
  this_id SERIAL PRIMARY KEY,
  user_id INT,
  is_read TINYINT,
  FOREIGN KEY (this_id) REFERENCES supertable(this_id)
  ON UPDATE CASCADE
  ON DELETE CASCADE
);

CREATE TABLE supertable (
  this_id SERIAL PRIMARY KEY
);

CREATE TABLE comments (
  this_id SERIAL PRIMARY KEY,
  user_id INT ,
  post    TEXT,
  is_approved TINYINT,
  ...
  FOREIGN KEY (this_id) REFERENCES supertable(this_id)
  ON UPDATE CASCADE
  ON DELETE CASCADE
);

CREATE TABLE PM (
  this_id SERIAL PRIMARY KEY,
  sender_id INT,
  recipient_id INT ,
  msg TEXT,
  ...
  FOREIGN KEY (this_id) REFERENCES supertable(this_id)
  ON UPDATE CASCADE
  ON DELETE CASCADE
);
Community
  • 1
  • 1
RedGiant
  • 4,444
  • 11
  • 59
  • 146

1 Answers1

0

Check again your column mapping. Few key to point here.

  1. your notification table primary key should set to id instead of this_foreign_id(I rename it to avoid confuse).
  2. datatype for this_foreign_id should be BIGINT UNSIGNED to map with SERIAL, since 1 table only can have 1 auto increment column and it should be primary key.
  3. on your trigger, insert null instead of '' cause SERIAL column is meant for BIGINT UNSIGNED

Let me know if it work.

CREATE TABLE supertable (
  this_id SERIAL PRIMARY KEY
);

CREATE TABLE notification (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  this_foreign_id BIGINT UNSIGNED,
  user_id INT,
  is_read TINYINT,
  FOREIGN KEY (this_foreign_id) REFERENCES supertable(this_id)
  ON UPDATE CASCADE
  ON DELETE CASCADE
);

CREATE TABLE comments (
  this_id SERIAL PRIMARY KEY,
  user_id INT ,
  post    TEXT,
  is_approved TINYINT,
  FOREIGN KEY (this_id) REFERENCES supertable(this_id)
  ON UPDATE CASCADE
  ON DELETE CASCADE
);

CREATE TABLE PM (
  this_id SERIAL PRIMARY KEY,
  sender_id INT,
  recipient_id INT ,
  msg TEXT,
  FOREIGN KEY (this_id) REFERENCES supertable(this_id)
  ON UPDATE CASCADE
  ON DELETE CASCADE
);

CREATE TRIGGER before_insert_comments
BEGIN
    BEFORE INSERT ON comments
    FOR EACH ROW BEGIN
    INSERT INTO supertable (this_id) VALUES (NULL);
END;



CREATE TRIGGER before_insert_pm
BEGIN
    BEFORE INSERT ON PM
    FOR EACH ROW BEGIN
    INSERT INTO supertable (this_id) VALUES (NULL);
END;
ah_hau
  • 768
  • 4
  • 11