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