Greetings Wire-Folk,
I'm getting into an area with the Database Connectivity Toolkit that lies outside of my usual LV comfort zone. We need to build DB tables dynamically in LabVIEW, and due to the complexity of the normalized DB design, several of the tables require Foreign Key refererences.
The normalized schema has been constructed using the (open source) MySQL Workbench. However, the target DB must run under MS SQL Server, and some of the tables will need to be constructed and "dropped" at run-time.
To illustrate the format, here is an example of one of the smaller tables we must create:
CREATE TABLE IF NOT EXISTS `BFSH`.`Operator_Event` (
`OperatorEvent_ID` INT NOT NULL AUTO_INCREMENT ,
`Operator_ID` INT NOT NULL ,
`EventType_ID` INT NOT NULL ,
`EventTime` DATETIME NOT NULL ,
`EventComment` VARCHAR(100) NOT NULL ,
PRIMARY KEY (`OperatorEvent_ID`) ,
INDEX `EventType_ID` (`EventType_ID` ASC) ,
UNIQUE INDEX `OperatorEvent_ID_UNIQUE` (`OperatorEvent_ID` ASC) ,
CONSTRAINT `Operator_ID` FOREIGN KEY (`Operator_ID` )
REFERENCES `BFSH`.`Operator` (`Operator_ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `EventType_ID` FOREIGN KEY (`EventType_ID` )
REFERENCES `BFSH`.`Operator_Event_Type` (`EventType_ID` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
The "DB Tools Create Table" VI has been effective for dynamically creating the tables, but it doesn't appear to have a mechnism for adding Foreign Key constraints at build time. Am I missing something? Is there a way to use this VI that I'm missing? Perhaps another VI must be used to add constraints to the tables after construction? (I have looked in the DBCT "advanced palette", and have seen nothing that appears to be related to constraints, but I must admit that I'm only part-way up the learnig curve on this one.)
Any suggestions or pointers would be appreciated!
Thanks.
