Sql-server – Foreign key, key split in two tables

foreign keysql server

I have to record a list of events made of action, reason (optional, may be multiple), user.
I have a master table with all the valid action, and another with all the valid action-reason combination.
All tables have a unique identity (not displayed in the example).

master_actions
action
A1
A2
A3

master_action_reasons
action  reason
A1      NULL
A2      R1
A2      R2
A3      R1
A3      R2
A3      R3

events
action  user   id
A1      John   E1
A3      Lisa   E2
A3      Sue    E3

reasons
event reason
E2    R1 
E2    R3
E3    R2

Question: how do I make sure that the combination action-reason is valid, i.e. exists in master_action_reasons? Since the action is in one table, and the reason in another, I cannot use a foreign key.

Best Answer

This is a good case for CHECK constraints using a function. Step one, write a function that does something like (EDIT: Used Event instead of Action sorry!):

CREATE FUNCTION CheckReasonValid(@intReason, @intEvent)
RETURNS bit
AS 
BEGIN
   DECLARE @NumRows int
   SELECT @NumRows = COUNT(*) FROM master_action_reason mar 
       INNER JOIN Events e ON mar.Action = e.Action
       WHERE mar.Reason = @intReason AND e.Event = @Event
   RETURN @NumRows 
END;

Then in your table definition, add the CHECK constraint, something like:

ALTER TABLE Reasons
ADD CONSTRAINT chkActionReasonValid CHECK (dbo.CheckReasonValid(Reason,Event) >= 1 );

Related Solutions

MySQL Foreign Keys – Solving Problems with Foreign Keys on the Same Table

You have identified the cause of the problem. A possible workaround is to create another table for the linked list:

CREATE TABLE calendars
  ( calendar_id INT PRIMARY KEY
  -- other columns
  ) ;

CREATE TABLE calendar_list
  ( calendar_id INT NOT NULL
  , calendar_next INT NOT NULL
  , PRIMARY KEY (calendar_id)
  , UNIQUE (calendar_next)
  , CONSTRAINT calendar_list_1
      FOREIGN KEY (calendar_id) 
      REFERENCES calendars (calendar_id) 
      ON DELETE CASCADE ON UPDATE CASCADE
  , CONSTRAINT calendar_list_2
      FOREIGN KEY (calendar_next) 
      REFERENCES calendars (calendar_id) 
      ON DELETE CASCADE ON UPDATE CASCADE
  ) ;

The following query can then be used to show what you have now:

SELECT c.calendar_id, 
       prev.calendar_id AS calendar_prev, 
       next.calendar_next
FROM calendars AS c
  LEFT JOIN calendar_list AS prev
    ON c.calendar_id = prev.calendar_next
  LEFT JOIN calendar_list AS next
    ON c.calendar_id = next.calendar_id ;

The UPDATE will work fine with this setup. Test at SQL-Fiddle. (Deletes would be a different issue but that wasn't addressed with the previous design either. Setting to null would break the list if the delete was anywhere but the first or last point.)

MySQL Foreign Key – Troubleshooting Cascading Update Failures

It appears as if this is not permitted in innodb which I assume is the engine you are using. From http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html:

If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, is possible, as is a self-referential ON DELETE CASCADE. Cascading operations may not be nested more than 15 levels deep.

Best Answer

Related Solutions

MySQL Foreign Keys – Solving Problems with Foreign Keys on the Same Table

MySQL Foreign Key – Troubleshooting Cascading Update Failures

Related Question