SQL confused on foreign key/references/constraints, newb question.

[voidflux]

Hello everyone,

I'm confused on this part of the SQL code of the connects table below:

CONSTRAINT `connects_entity_fk1` FOREIGN KEY (`aEndEntityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE, 
  CONSTRAINT `connects_entity_fk2` FOREIGN KEY (`zEndEntityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE

DROP TABLE IF EXISTS `connects`; 
CREATE TABLE `connects` ( 
  `connectionId` int(11) NOT NULL auto_increment, 
  `aEndEntityId` int(11) NOT NULL, 
  `zEndEntityId` int(11) NOT NULL, 
  `unidirectional` int(11) NOT NULL, 
  PRIMARY KEY  (`connectionId`), 
  UNIQUE KEY `connects_uk` (`aEndEntityId`,`zEndEntityId`,`unidirectional`), 
  KEY `connEntityA_idx` (`aEndEntityId`,`zEndEntityId`,`connectionId`), 
  KEY `connEntityZ_idx` (`zEndEntityId`,`aEndEntityId`,`connectionId`), 
  CONSTRAINT `connects_entity_fk1` FOREIGN KEY (`aEndEntityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE, 
  CONSTRAINT `connects_entity_fk2` FOREIGN KEY (`zEndEntityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Here is the 'entity' table:

CREATE TABLE `entity` ( 
  `entityId` int(11) NOT NULL, 
  `mainNodeEntityId` int(11) default NULL, 
  `entityName` varchar(255) collate utf8_bin NOT NULL, 
  `domainMgrId` int(11) NOT NULL, 
  `entityType` int(11) NOT NULL, 
  `createTime` datetime NOT NULL, 
  `changeTime` datetime NOT NULL, 
  `displayLabel` varchar(255) collate utf8_bin NOT NULL, 
  `description` varchar(512) collate utf8_bin default NULL, 
  `alias` varchar(255) collate utf8_bin default NULL, 
  PRIMARY KEY  (`entityId`), 
  UNIQUE KEY `entity_uk` (`domainMgrId`,`entityName`), 
  KEY `entityMainNode_idx` (`mainNodeEntityId`,`entityId`), 
  KEY `entityEntityType_idx` (`entityType`), 
  KEY `entityName_idx` (`entityName`), 
  KEY `entityDomain_idx` (`domainMgrId`), 
  CONSTRAINT `entity_domainMgr_fk` FOREIGN KEY (`domainMgrId`) REFERENCES `domainMgr` (`domainMgrId`) ON DELETE CASCADE, 
  CONSTRAINT `entity_entityType_fk` FOREIGN KEY (`entityType`) REFERENCES `entityType` (`entityType`) ON DELETE CASCADE, 
  CONSTRAINT `entity_entity_fk` FOREIGN KEY (`mainNodeEntityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE, 
  CONSTRAINT `entity_entiytNameCache_fk` FOREIGN KEY (`entityId`) REFERENCES `entityNameCache` (`entityId`) ON DELETE CASCADE 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

When you insert a value into the entity table,
entityID is the primary key, but what does it mean when connects table uses:
CONSTRAINT `connects_entity_fk1` FOREIGN KEY (`aEndEntityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE,
CONSTRAINT `connects_entity_fk2` FOREIGN KEY (`zEndEntityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE

I searched the connects table and NO entity ID is in the table that corresponds to the entity ID in the entity table;

For instance, if there is a value in the entity table, entityId = 95. If I search the connects table there is no "95" value anywhere. But it says, aEndEntityId and zEndEntityId are references to entityId, so who is referencing the value 95?


Thanks any help would be great!!

PS: I used mysql work bench to see the relationship, and it says connects table is connected some how to the entity table, but I don't see why when I search for the values I see no relationship of entityId and what is referncing it in the connects table.

[Culperat]

From the looks of the code this is an oppossite relation to what you're thinking.

Entity MUST contain ALL entityID's

However,
Connect doesn't have to have all of the entityID's. This is stated in Connect's code:

  CONSTRAINT `connects_entity_fk1` FOREIGN KEY (`aEndEntityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE, 
  CONSTRAINT `connects_entity_fk2` FOREIGN KEY (`zEndEntityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE

The way I'm reading this is that Connect points to the Entity table. This doesn't neccessarily mean that it would be forced to hold all entityID's. Even if entityID were a PK, you would not necessarily have all the entityID's in Connect. I guess what comes up as my question to you is, are you looking to make a relationship where all of Entity is involved in Connect?


So it is possible to have a 95 value in Entity that doesn't show up in Connect. You'd have to have entered the entity value into a tuple of Connect in order for it to show up. Its redundant information but its how you'll develop the relationships in the actual data.

[voidflux]

Thanks for the responce! Now i'm lost on how I would get data from 4 of the tables.


I'm trying to get all the data that link these tables together. They all have forgien keys in common so I would like to retireve all that data and print it out to the screen.

Here's the code used to create the tables as well as the statement I used to query the databases.

CREATE TABLE `entity` (
`entityId` int(11) NOT NULL,
`mainNodeEntityId` int(11) default NULL,
`entityName` varchar(255) collate utf8_bin NOT NULL,
`domainMgrId` int(11) NOT NULL,
`entityType` int(11) NOT NULL,
`createTime` datetime NOT NULL,
`changeTime` datetime NOT NULL,
`displayLabel` varchar(255) collate utf8_bin NOT NULL,
`description` varchar(512) collate utf8_bin default NULL,
`alias` varchar(255) collate utf8_bin default NULL,
PRIMARY KEY (`entityId`),
UNIQUE KEY `entity_uk` (`domainMgrId`,`entityName`),
KEY `entityMainNode_idx` (`mainNodeEntityId`,`entityId`),
KEY `entityEntityType_idx` (`entityType`),
KEY `entityName_idx` (`entityName`),
KEY `entityDomain_idx` (`domainMgrId`),
CONSTRAINT `entity_domainMgr_fk` FOREIGN KEY (`domainMgrId`) REFERENCES `domainMgr` (`domainMgrId`) ON DELETE CASCADE,
CONSTRAINT `entity_entityType_fk` FOREIGN KEY (`entityType`) REFERENCES `entityType` (`entityType`) ON DELETE CASCADE,
CONSTRAINT `entity_entity_fk` FOREIGN KEY (`mainNodeEntityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE,
CONSTRAINT `entity_entiytNameCache_fk` FOREIGN KEY (`entityId`) REFERENCES `entityNameCache` (`entityId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

CREATE TABLE `connects` (
`connectionId` int(11) NOT NULL auto_increment,
`aEndEntityId` int(11) NOT NULL,
`zEndEntityId` int(11) NOT NULL,
`unidirectional` int(11) NOT NULL,
PRIMARY KEY (`connectionId`),
UNIQUE KEY `connects_uk` (`aEndEntityId`,`zEndEntityId`,`unidirectional`),
KEY `connEntityA_idx` (`aEndEntityId`,`zEndEntityId`,`connectionId`),
KEY `connEntityZ_idx` (`zEndEntityId`,`aEndEntityId`,`connectionId`),
CONSTRAINT `connects_entity_fk1` FOREIGN KEY (`aEndEntityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE,
CONSTRAINT `connects_entity_fk2` FOREIGN KEY (`zEndEntityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

CREATE TABLE `topologyLinks` (
`entityId` int(11) NOT NULL,
`connectionId` int(11) NOT NULL,
PRIMARY KEY (`entityId`,`connectionId`),
KEY `topoLinkConnId_idx` (`connectionId`,`entityId`),
CONSTRAINT `topologyLinks_connects_fk` FOREIGN KEY (`connectionId`) REFERENCES `connects` (`connectionId`) ON DELETE CASCADE,
CONSTRAINT `topologyLinks_entity_fk` FOREIGN KEY (`entityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

CREATE TABLE `networkPipe` (
`entityId` int(11) NOT NULL,
`connectionId` int(11) NOT NULL,
`aggregationType` int(11) NOT NULL,
PRIMARY KEY (`entityId`,`connectionId`),
KEY `netPipeConnId_idx` (`connectionId`,`entityId`),
CONSTRAINT `networkPipe_connects_fk` FOREIGN KEY (`connectionId`) REFERENCES `connects` (`connectionId`) ON DELETE CASCADE,
CONSTRAINT `networkPipe_entity_fk` FOREIGN KEY (`entityId`) REFERENCES `entity` (`entityId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

I'm trying to retrieve all the data that map to entity; so I need to link
all the forgien keys and all that table data and spit it out to the screen.

Here is what I tired:
select * from entity, connects, networkPipe, topologyLinks where
entity.entityId = connects.aEndEntityId or entity.entityId = connects.zEndEntityId
and entity.entityId = networkPipe.entityId and entity.entityId = topologyLinks.entityId;

But I get 0 results.
I posted my code here as well: [rafb.net]

I got this to work:
select * from entity, connects where entity.entityId = connects.aEndEntityId or entity.entityId = connects.zEndEntityId;


that will return 560 results. Now I need to also add it to join the toplogy table, and networkpipe table thats where i'm having issues.

Thanks

[Culperat]

I guess the problem I'm seeing is, what exactly are you trying to limit the search to? To me what you're trying to query, is to find all entitys that exist in all tables. So this means an entity has to exist in all the tables or it won't show. Perhaps that's what you want.

Here's an idea of what could work. I'm not sure as I don't have the database built and full of information to test it out, but its an idea:

SELECT *
FROM entity AS e,
          networkPipe AS n,
          topologyLinks AS t
WHERE e.entityId IN (
         SELECT e.entityId
         FROM entity AS e,
                   connects AS c,
         WHERE e.entityId = c.aEndEntityId OR
                   e.entityId = c.zEndEntityId) AND
         e.entityId = n.entityId AND
         e.entityId = t.entityId;

As I'm not the most fluent with nested statements in sql, so I am not absolutely sure that will work. My basic idea behind the query above is to check to see if the entityId exists in connects first and then see if it exists in networkPipe and topologyLinks. Try that, if it doesn't work syntactically, try looking into how to get a nested statement like that to work.