MySQL unique constraint across two columns

Question

I have a table defining relationships

Src smallint(5) unsigned NOT NULL,
Dst smallint(5) unsigned NOT NULL,
other fields

I need to add a constraint that says if a given values is present in one of the columns, then.
1) Cannot be duplicated in the same column.
2) Cannot be duplicated in the other column either.

This is invalid

src dst
1 354
666 1

Since the value 1 is present in the first row, it cannot be present in the second row.

How can I define this type of constraint?

I'm doing a lightweight check at application lever. But I want the DB to ensure it.

Update: Currently I have 7 different types of relationships, and one table per relationship type.

Update 2: Originally this was only one table containing all the relationships, now I'm exploding it

# variante
Create TABLE `productsRelationships3` (
 `relSrc` smallint(5) unsigned NOT NULL,
 `relDst` smallint(5) unsigned NOT NULL,
 PRIMARY KEY `src-dst-3` (relSrc, relDst),
 UNIQUE `src-3` (relSrc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# this is the import
INSERT INTO productsRelationships3 SELECT relSrc, relDst FROM productsRelationships WHERE relType=3;
DELETE FROM productsRelationships WHERE relType=3;
#this is the retrieval. The dummy rows are there because I do a UNION
#SELECT relSrc, relDst, 3 as relType, relTypeDesc, 0 as fracQty, 28281 as source FROM productsRelationships3 LEFT JOIN productsRelationshipsDesc on 3=relTypeID WHERE relDst=28281 OR relSrc=28281;
# fraccion
#relType is from the old 1-table schema. It's going to be deleted
Create TABLE `productsRelationships6` (
 `relSrc` smallint(5) unsigned NOT NULL,
 `relType` tinyint(2) unsigned NOT NULL DEFAULT 6,
 `fracQty` int(2) unsigned NOT NULL,
 `relDst` smallint(5) unsigned NOT NULL,
 PRIMARY KEY `src-dst-6` (relSrc, relDst),
 UNIQUE `src-6` (relSrc),
 UNIQUE `dst-6` (relDst),
 CONSTRAINT `fk_type_desc_6` FOREIGN KEY (`relType`) REFERENCES `productsrelationshipsdesc` (`relTypeID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#import
INSERT INTO productsRelationships6 SELECT relSrc, relType, fracQty, relDst FROM productsRelationships WHERE relType=6;

The other tables are basically the same as productsRelationships3

• ypercubeᵀᴹ: Are these (relSrc and relDst) foreign keys to some other tables? Please include that info, too. And if yes, are the foreign keys from productsRelationships3 and from productsRelationships6 (and productsRelationshipsX) all pointing to (referencing) the same tables? Or different ones?
• The Disintegrator: They should be foreign keys to a products table. They all are referencing to the same products table (products.prodID). A given product can participate in more than one relationship.

Answer 1

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

• ypercubeᵀᴹ: This is far better answer than mine. And if the OP wants to allow same source and destination in the same Route, then the PointType table and columns are not needed, making the solution much simpler.

Answer 2

I don't think it's possible to have this constraint with the current design. If you can change that and assuming the table is now:

CREATE TABLE Route
( RouteID int unsigned NOT NULL,
 Src smallint(5) unsigned NOT NULL,
 Dst smallint(5) unsigned NOT NULL,
 -- other fields
 PRIMARY KEY (RouteID)
) ;

you can split into 2 tables and move the two columns into the new table, combining them into one column (SrcDst). A small reference table (PointType) with just 2 rows will help enforce the requirements that you have a Src and a Dst and no more:

CREATE TABLE Route
( RouteID int unsigned NOT NULL,
 --- other fields
 PRIMARY KEY (RouteID)
) ;
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 RoutePoint
( RouteID int unsigned NOT NULL,
 PointTypeID tinyint unsigned NOT NULL,
 SrcDst smallint(5) unsigned NOT NULL,
 PRIMARY KEY (RouteID, PointTypeID),
 UNIQUE (SrcDst), -- and this is what all the fuss is about
 FOREIGN KEY (RouteID)
 REFERENCES Route (RouteID)
 ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY (PointTypeID)
 REFERENCES PointType (PointTypeID)
) ;

This means that any row on the old Route table will now be 1 row in the new Route and 2 rows in the RoutePoint table.

This means that now you can't simply INSERT into the Route table. You have to use a transaction that ensures that if a row is inserted at Route table, 2 rows are also inserted at the RoutePoint table. Otherwise you may have rows in Route without Src or Dst.

Similar changes have to be made to the UPDATE and DELETE statements of the two tables, so no row is accidentally changed or deleted from the RoutePoint table for example, leaving a row in Route without a related Src or Dst data.

• A-K: The original design guarantees that both source and destination are not null. IMO with your design one or both can be missing.
• ypercubeᵀᴹ: @AlexKuznetsov: That is correct. INSERT procedures have to take care of that. (and DELETE/UPDATE as well). Is there any other way to implement this constraint?

Answer 3

This can be accomplished with a trigger.

See this SQLFiddle for a working model. Change the 2nd INSERT to a duplicate value and you'll see the insert fail with an error message. Bailing from a BEFORE INSERT trigger with a fatal SIGNAL prevents the insert from actually happening.

CREATE TRIGGER Route_bi BEFORE INSERT ON Route FOR EACH ROW
 BEGIN
 DECLARE err_msg VARCHAR(128) DEFAULT NULL; 
 IF EXISTS(SELECT x.Dst FROM Route x WHERE x.Dst = NEW.Src) THEN
 SET err_msg = CONCAT_WS('','cannot insert Src value ',NEW.Src,'; already exists as a Dst');
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = err_msg;
 END IF;
 IF EXISTS(SELECT x.Src FROM Route x WHERE x.Src = NEW.Dst) THEN
 SET err_msg = CONCAT_WS('','cannot insert Dst value ',NEW.Dst,'; already exists as a Src');
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = err_msg;
 END IF;
 END;

The Src and Dst columns each need a UNIQUE KEY, which will prevent duplicate values in the same column.

You'll also have to handle the condition in a similar BEFORE UPDATE trigger to avoid changing values to duplicates later.