My Cableroutings table models a many-to-many relationship. However, I am trying to constrain this relationship to limit the number of occurrences of any given Cable to a maximum of 2 occurrences. (Basically a cable can only be routed to two enclosures).
There is a problem. My 'CHECK_ONLY_2_ROUTES_FOR_C
ABLE' doesn't work as I've defined it. It only allows me to create two records in the entire table. This isn't what I want.
Does anyone know how I am reformulate my check constraint to achieve the desired functionality?
--Table In question
CREATE TABLE Cableroutings (
areacode TEXT( 4 ) NOT NULL,
enc_class TEXT( 3 ) NOT NULL,
enc_nbr INTEGER NOT NULL,
prefix INTEGER NOT NULL,
cabletype TEXT( 2 ) NOT NULL,
suffix INTEGER NOT NULL,
FOREIGN KEY ( areacode, enc_class, enc_nbr )
REFERENCES Enclosures( areacode, enc_class, enc_nbr )
ON UPDATE CASCADE,
FOREIGN KEY ( prefix, cabletype, suffix )
REFERENCES Cables( prefix, cabletype, suffix )
ON UPDATE CASCADE );
--Access doesn't like CHECK constraints declared in Table Definition
--But declaring them with ALTER TABLE ADD ... works just fine.
ALTER TABLE Cableroutings
ADD CONSTRAINT CHECK_ONLY_2_ROUTES_FOR_CABLE
CHECK(( SELECT COUNT( * )
FROM Cableroutings AS CR
WHERE CR.prefix = prefix
AND CR.cabletype = cabletype
AND CR.suffix = suffix ) BETWEEN 0 AND 2 );