MySQL constraint to prevent combinations of columns I have a table that represents files in directories.
dir   | file
----------------
dir_A | file_0
dir_A | file_1
dir_A | file_1
dir_B | file_2
dir_C | file_3
...   | ...

I want to put a constraint on that table to make sure a file can never be in 2 directories. But the same file can be in the same directory multiple times.
Example of permitted condition:
dir_A | file_1
dir_A | file_1
dir_B | file_2

Example of failed condition:
dir_A | file_1
dir_B | file_1
-- should be prevented, because `dir_A file_1` exists
-- same file, but different directory

The question has been asked for PostgreSQL with solution:
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE table_name
    ADD CONSTRAINT only_one_dir_per_file
        EXCLUDE USING gist ( file WITH =, dir WITH &lt;&gt; );

I was wondering if the same could be done with MySQL?
you can use Triggers for that.


CREATE TABLE files_dir (
  `dir` VARCHAR(5),
  `file` VARCHAR(6)
);

INSERT INTO files_dir
  (`dir`, `file`)
VALUES
  ('dir_A', 'file_0'),
  ('dir_A', 'file_1'),
  ('dir_A', 'file_1'),
  ('dir_B', 'file_2'),
  ('dir_C', 'file_3');
  




CREATE TRIGGER before_file_insert 
    BEFORE INSERT ON files_dir
    FOR EACH ROW 
 BEGIN
 IF EXISTS(SELECT 1 FROM files_dir WHERE `file`= NEW.`file` AND `dir` = NEW.`dir`) then
  BEGIN
  END;
 ELSE
    IF EXISTS(SELECT 1 FROM files_dir WHERE `file`= NEW.`file`) THEN
          SIGNAL SQLSTATE '45000' 
      SET MESSAGE_TEXT = &quot;file has another directory&quot;;
    END IF;
   END IF;
 END;




 INSERT INTO files_dir
  (`dir`, `file`)
VALUES
('dir_A', 'file_1');


✓




 INSERT INTO files_dir
  (`dir`, `file`)
VALUES
('dir_B', 'file_1');


file has another directory




SELECT * FROM files_dir;


dir   | file  
:---- | :-----
dir_A | file_0
dir_A | file_1
dir_A | file_1
dir_B | file_2
dir_C | file_3
dir_A | file_1


db&lt;&gt;fiddle here
For an BEFORE UPDATE TRIGGER you can do something eual.
Of course you can always change your insert statement with a join like ypercubeᵀᴹ already said.