MySQL constraint to prevent combinations of columns

constraintMySQLmysql-8.0

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 <> );

I was wondering if the same could be done with MySQL?

Best Answer

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 = "file has another directory";
    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<>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.

Related Solutions

MySQL unique constraint across two columns

I have modified ypercube's solution a little bit, so that both source and destination are not null, as is guaranteed by the original design. My CHECK constraints are commented out, as they apparently will not work in MySQL. I kept them as comments because they document my intent, and they will work on other RDBMS.

CREATE TABLE PointType
( PointTypeID tinyint unsigned NOT NULL,
  TypeDescription CHAR(20) NOT NULL,
  PRIMARY KEY (PointTypeID),
  UNIQUE (TypeDescription)
) ;

INSERT INTO PointType 
  (PointTypeID, TypeDescription)
VALUES
  (1, 'Source'), 
  (2, 'Destination') ;

CREATE TABLE PointUsageQuota
( RouteID int unsigned NOT NULL,
  PointTypeID tinyint unsigned NOT NULL,
  PointID smallint(5) unsigned NOT NULL,
  PRIMARY KEY (PointID),     -- and this is what all the fuss is about
  UNIQUE (RouteID , PointID, PointTypeID), -- target for the foreign keys
  FOREIGN KEY (PointTypeID)
    REFERENCES PointType (PointTypeID)
) ;

CREATE TABLE Route
( RouteID int unsigned NOT NULL,
  SourcePointID smallint(5) unsigned NOT NULL,
  SourceTypeID tinyint unsigned NOT NULL, 
      -- CHECK(SourceTypeID = 1),
  FOREIGN KEY (RouteID , SourcePointID, SourceTypeID) 
    REFERENCES PointUsageQuota (RouteID , PointID, PointTypeID) ,
  DestinationPointID smallint(5) unsigned NOT NULL,
  DestinationTypeID tinyint unsigned NOT NULL, 
      -- CHECK(DestinationTypeID = 2),
  FOREIGN KEY (RouteID , DestinationPointID, DestinationTypeID) 
    REFERENCES PointUsageQuota (RouteID , PointID, PointTypeID) ,
  --  other fields
  PRIMARY KEY (RouteID)
) ;

Testing:

insert into PointUsageQuota
values(1,1,666),(1,2,354);

INSERT INTO Route VALUES (1, 666, 1, 354, 2);
-- this fails:
INSERT INTO Route VALUES (2, 666, 1, 354, 2);
-- this fails too:
INSERT INTO Route VALUES (2, 354, 1, 666, 2);

Mysql – How to forcibly create/modify MySQL user grants, without restarting the database service

You could copy just the the "mysql" database away to another location and start another daemon on it. Get the SHA1 or DES hash stored in the user table for a user with SUPER privs (usually root, but sometimes renamed for security through obscurity).

Then connect to the mysql using a modified version of the client library that makes mysql_real_connect() support using a pre-hashed password instead of having it take the password plaintext. This should be trivial.

You won't ever know the actual password, but with the hash and a modified client you'll be able to log in anyway.

You can then make any modifications to permissions, create necessary schema and tables and flush privileges.

I'll leave the security implications of such practices up to you.

Best Answer

Related Solutions

MySQL unique constraint across two columns

Mysql – How to forcibly create/modify MySQL user grants, without restarting the database service

Related Question