MySQL - Conditional Foreign Key Constraints

MySQL - Multiple foreign keys, that are only valid on individual check constraints

It sounds like you're trying to implement polymorphic-associations, where a given column can be an id referencing any of several parent table.

A foreign key constraint references one parent table. You can't make these conditional. This is why polymorphic associations are fundamentally not compatible with relational constraints.

If you need to reference one of several parent tables, one way to do it is to create multiple columns, each of which are nullable. Then you can use a check constraint or a trigger to make sure that exactly one of them is not null.

CREATE TABLE items (
  Itemid INT NOT NULL PRIMARY KEY,
  Itemtype ENUM ('Action', 'Risk') NOT NULL,
  Actionid INT NULL,
  Riskid INT NULL,
  FOREIGN KEY (Actionid) REFERENCES actions (Actionid),
  FOREIGN KEY (Riskid) REFERENCES risks (riskid),
  CHECK (CASE Itemtype
         WHEN 'Action' THEN Actionid IS NOT NULL AND Riskid IS NULL
         WHEN 'Risk' THEN Riskid IS NOT NULL AND Actionid IS NULL
         END)
);

Conditional Foreign Key to multiple tables

If you really want to do this, you can have two nullable columns one for CompanyId and one for EmployeeId that act as foreign keys.

But I would rather you to try and review the database schema design.

Constrain with conditional value check in MySQL

According to the documentation,

A UNIQUE index permits multiple NULL values for columns that can contain NULL.

So removing the not null-constraint from Status and adding a unique index on (ContactId,PhoneId,Status) will work as you want it to, if you use null instead of 0 for inactive records.

If you don't want to or cannot use null for your Status column, want to make sure both Status=0 and Status=null behave identically, or e.g. want to treat Status=2 as active (and enforcing uniqueness) too, you can add a dummy column that will be calculated from Status.

If you are using MySQL 5.7+, you can do this with a generated column:

CREATE TABLE IF NOT EXISTS `ContactPhone` (
  `ContactPhoneId` int(10) unsigned NOT NULL auto_increment primary key,
  `ContactId` int(11) NOT NULL,
  `PhoneId` smallint(5) unsigned NOT NULL,
  `Status` tinyint(1) NOT NULL DEFAULT '1',
  `StatusUnq` tinyint(1) as (if(Status <> 0, 1, null)) stored null,
  constraint unique (ContactId, PhoneId, StatusUnq)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

insert into ContactPhone (ContactPhoneId, ContactId, PhoneId, Status)
values (1, 1, 1, 1);
insert into ContactPhone (ContactPhoneId, ContactId, PhoneId, Status)
values (2, 1, 1, 1);
-- Duplicate key error 
insert into ContactPhone (ContactPhoneId, ContactId, PhoneId, Status)
values (3, 1, 1, 0);
insert into ContactPhone (ContactPhoneId, ContactId, PhoneId, Status)
values (4, 1, 1, 0);
update ContactPhone set Status = 1 where ContactPhoneId = 4;
-- Duplicate key error

Otherwise, you can use a normal column and use triggers to calculate the value of the column, e.g.:

create trigger trbi_contactPhoneUnique before insert on ContactPhone 
for each row
  set new.StatusUnq = if(new.Status <> 0, 1, null);

create trigger trbu_contactPhoneUnique before update on ContactPhone 
for each row
  set new.StatusUnq = if(new.Status <> 0, 1, null);

You can of course switch the formula to e.g. if(new.Status <> 0, new.Status, null); if you want to allow different values of Status too.

How to insert data into table conditionally based on foreign key value?

Your second query seems the correct way to proceed, however looks like you can benefit from adding the institution_id in the teacher_has_student table, and define the foreign key with this column as well:

CREATE TABLE teacher (
    teacher_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    teacher_name VARCHAR(50) NOT NULL,
    institution_id INT(10) UNSIGNED,
    PRIMARY KEY (teacher_id),
    UNIQUE KEY teacher_institution (teacher_id, institution_id)
);
CREATE TABLE student (
    student_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    student_name VARCHAR(50) NOT NULL,
    institution_id INT(10) UNSIGNED,
    PRIMARY KEY (student_id),
    UNIQUE KEY student_institution (student_id, institution_id)
);
CREATE TABLE teacher_has_student (
    teacher_id INT(10) UNSIGNED NOT NULL,
    student_id INT(10) UNSIGNED NOT NULL,
    institution_id INT(10) UNSIGNED NOT NULL,
    UNIQUE KEY (teacher_id, student_id, institution_id),
    CONSTRAINT teacher_istitution FOREIGN KEY (teacher_id, institution_id) REFERENCES teacher (teacher_id, institution_id),
    CONSTRAINT student_istitution FOREIGN KEY (student_id, institution_id) REFERENCES student (student_id, institution_id)
);
INSERT INTO teacher (teacher_name, institution_id)
VALUES ("Stark", 101), ("Haydn", 102);
INSERT INTO student (student_name, institution_id)
VALUES ("Parker", 101), ("Beethoven", 102);

/* THIS ONE WORKS for both student 2 and teacher 2 have institution_id 102 */
INSERT INTO teacher_has_student (teacher_id, student_id, institution_id)
VALUES (2, 2, 102);

/* foreign key constraint fails: for none of theacher and student have institution_id 101 */
INSERT INTO teacher_has_student (teacher_id, student_id, institution_id)
VALUES (2, 2, 101);

/* foreign key constraint fails: for none of theacher have no institution_id 101 */
INSERT INTO teacher_has_student (teacher_id, student_id, institution_id)
VALUES (2, 1, 101);

/* foreign key constraint fails: for none of student have no institution_id 101 */
INSERT INTO teacher_has_student (teacher_id, student_id, institution_id)
VALUES (1, 2, 101);

How to implement a conditional foreign key restriction?

If I get what you want you need to have a primary key on A in table Y and a unique constraint on A,B in table Y.

Try this:

create table Y
(
  A int not null,
  B int not null,
  primary key (A)
);

create unique index IX_Y_AB on Y(A, B);

create table X 
(
    A int not null,
    B int not null,
    C int null,
    primary key (A, B),
    foreign key (A) references Y(A),
    foreign key (A, C) references Y(A, B)
);

Test:

insert into Y values (1, 2)

insert into X values (1, 1, null) -- OK
insert into X values (1, 2, 2)    -- OK
insert into X values (1, 3, 3)    -- Fail