NI TestStand

cancel
Showing results for 
Search instead for 
Did you mean: 

Setting Foreign Key constraints with Database Connectivity Toolkit

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.

 

 

 

0 Kudos
Message 1 of 3
(2,673 Views)

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

 

Still, if anyone has some ideas...

 

Thanks.

0 Kudos
Message 2 of 3
(2,669 Views)

Hi,

 

Take a look at this document:

 

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

 

Regards,

 

Greg H.

Applications Engineer
National Instruments
0 Kudos
Message 3 of 3
(2,655 Views)