Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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? –  ypercube Nov 27 '12 at 13:33
    
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. –  The Disintegrator Nov 28 '12 at 0:57
add comment

4 Answers

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.

share|improve this answer
    
I see your point. The problem is that I have 7 tables (7 different kinds of relationships with different rules) and 6 of the 7 need this constraint. Omg too many tables! –  The Disintegrator Nov 27 '12 at 7:55
    
Post your table structure (edit the question). It may be possible to add only 2 tables (and not 7x2). –  ypercube Nov 27 '12 at 9:41
    
It's nothing fancy, one of the tables has a qty row, And other has a startDate row –  The Disintegrator Nov 27 '12 at 10:27
    
The original design guarantees that both source and destination are not null. IMO with your design one or both can be missing. –  AlexKuznetsov Nov 27 '12 at 17:50
    
@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? –  ypercube Nov 27 '12 at 17:53
show 1 more comment

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 docuemnt 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);
share|improve this answer
    
Nice. It wouldn't work as it is in MySQL (no CHECK constraints) but it's very clever. –  ypercube Nov 27 '12 at 21:09
    
@ypercube Thanks! Instead of a CHECK, we can use a computed column, CAST(1 AS tinyint) AS SourceTypeID. What do you think? Anyway, this CHECK is redundant, needed for self-documentation. –  AlexKuznetsov Nov 27 '12 at 21:15
    
Computed columns are implemented only in MariaDB variant :) The CHECKs could be implemented with FKs to a 1-row table though. –  ypercube Nov 27 '12 at 21:26
    
On the other matter, if you remove the CHECKs, and the PointTypeID columns, how can you avoid having a Route with identical Source and Destination? –  ypercube Nov 27 '12 at 21:28
    
@ypercube I concur - SourceTypeID and DestinationTypeID must be kept. Thanks! –  AlexKuznetsov Nov 27 '12 at 21:35
show 1 more comment

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.

share|improve this answer
add comment

Thinking out of the box... Add two column:

smaller SMALLINT NOT NULL,
larger SMALLINT NOT NULL,
UNIQUE(smaller), UNIQUE(larger)

Then do this when inserting (src, dst):

INSERT ... (src, dst, smaller, larger)
    VALUES ($src, $dst, LEAST($src, $dst), GREATEST($src, $dst)

I think that gives you the dedupping you mention in your first paragraph.

share|improve this answer
1  
This will not prevent from inserting (source, destination) pairs (1, 3) and (3, 5) - in the first row 3 will be larger, in the second it will be smaller. So, it does not work. –  AlexKuznetsov Nov 29 '12 at 2:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.