Start a new topic

Foreign key metadata

 Retrieving the VistaDBConnection.SchemaConstants.SCHEMA_FOREIGNKEYS datatable I can't find any info about the rule types CASCADE, SET NULL, SET DEFAULT

Does SQL Server include that information in its version of that API?  If so we would want to try to match what SQL Server includes in it.

However, there are a few other ways of querying the FOREIGN KEY schema info.  There are built-in table-valued functions which you can use in a simple query:

VistadbEF6FKConstraints() returns just the information about the UpdateRule and the DeleteRule for each FK relationship.

VistadbEF6ForeignKeys() returns just the table and key information for each FK relationship.

VistadbEF6Constraints() returns basic information for each PRIMARY KEY, UNIQUE, and FOREIGN KEY constraint over all tables.

sp_ForeignKeys() returns the key information and the UPDATE_RULE and DELETE_RULE (as an Int16 corresponding to the VistaDB.DDA.VistaDBReferentialIntegrity enum which have the same values as the System.Data.Rule enum) for each FK relationship.

You can also obtain the FK schema information through the DDA API if you're using that.  But if you aren't already using the DDA API you'll probably want to stick with the others which can be used through a normal SQL connection.

Thank you for the info. I don't know if SQL Server does include this information, never used it. But Firebird does. In my humble opinion every database should make ALL metadata available. I use the metadata to compare the database with my data-dictonary (database consistency check)

Login to post a comment