Return to 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.

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

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.

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.