CODEWITHSUNDEEP
mysqlsql
Plup
Plup

Reputation: 1242

sql primary key of commutative columns

Here is what I attempt to acheive :

I have one table fabric containing fabrics to build a product. When the administrator record a new fabric he can define if this fabric can be selected with another one (for compatibility of materials).

This exclusion relationship is commutative. If X doesn't go with Y, it means that Y can't go with X.

To implement that I created a table fabric_exclusion with two fields (id_fabric1 and id_fabric2) forming a primary key.

But how can I have CRUD operations working with this underlaying logic (id_fabric1=X|id_fabric2=Y) = (id_fabric1=Y|id_fabric2=X) ?

UPDATE: Here is what I already tried : 

CREATE TABLE IF NOT EXISTS `PREFIX_fabric_exclusion` (
    `id_fabric1` INT(10) NOT NULL,
    `id_fabric2` INT(10) NOT NULL,
    CONSTRAINT `ids` UNIQUE (`id_fabric1`, `id_fabric2`),
    PRIMARY KEY `ids`
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Upvotes: 0

Views: 171

Answers (2)

Ronnie
Ronnie

Reputation: 542

I know I am probably late, And as I scratched my head through this, I found one viable solution to enforce this.

Usually, when you have such a commutative pair, the order doesn't really matter.

I started adding a BEFORE INSERT TRIGGER.

CREATE TRIGGER `tbl_BEFORE_INSERT` BEFORE INSERT ON `thread` FOR EACH ROW
BEGIN
SET @l = least(new.id1, new.id2);
SET @l2 = greatest(new.id1, new.id2);
SET new.id1 = @l;
SET new.id2 = @m;
END;

With this, you always insert in order and never have to violate it. Because, it essentially assigns the smaller one to one field, and the larger one to another. Since they're guaranteed to call in each row, you're almost as sure as an integrity constraint, but with some overhead. But, another interesting way I found is by having a pairing function, again, in the same way, but for commutativeness, I would say, I would use an unordered pairing function, but they're not as space efficient as these.

Upvotes: 1

Stephan Lechner
Stephan Lechner

Reputation: 35154

Probably a little overhead, but you could use a calculated field based on id_fabric1 combined with id_fabric2 and a unique constraint (or even a primary key) on the calculated field. Thereby, it is ensured that, for example, a tuple (4,3) cannot be inserted if a tuple (3,4) exists and vice versa: 

CREATE TABLE `PREFIX_fabric_exclusion` (
    `id_fabric1` INT(10) NOT NULL,
    `id_fabric2` INT(10) NOT NULL,
    combinedKey int(21) as (if (id_fabric1 < id_fabric2, id_fabric1*100000000+ id_fabric2, id_fabric2*100000000+id_fabric1)) stored primary key
)

insert into PREFIX_fabric_exclusion(id_fabric1, id_fabric2) values (3, 4);

insert into PREFIX_fabric_exclusion(id_fabric1, id_fabric2) values (4, 3);  # Error: Duplicate entry '300000004' for key 'PRIMARY'

Upvotes: 1

Related Questions