Setting Foreign Key constraints with Database Connectivity Toolkit

BetterVIEW — Thu, 08 Mar 2012 01:44:54 GMT

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.

Re: Setting Foreign Key constraints with Database Connectivity Toolkit

BetterVIEW — Thu, 08 Mar 2012 01:53:55 GMT

Oops, it looks like I posted this to the wrong board. -- Sorry about that.

Still, if anyone has some ideas...

Thanks.

Re: Setting Foreign Key constraints with Database Connectivity Toolkit

Greg-H — Thu, 08 Mar 2012 22:48:33 GMT

Hi,

Take a look at this document:

http://digital.ni.com/public.nsf/allkb/9212B594E5F9A7A786256C100061D516?OpenDocument

Regards,

Greg H.

topic Re: Setting Foreign Key constraints with Database Connectivity Toolkit in NI TestStand

Setting Foreign Key constraints with Database Connectivity Toolkit

Re: Setting Foreign Key constraints with Database Connectivity Toolkit

Re: Setting Foreign Key constraints with Database Connectivity Toolkit