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);
Best Answer
Looks like you want something like: