Foreign key constraints from DB at runtime

[Shelah]

I'm trying to figure out how to discover foreign key constraints at
runtime by asking the database.

All the code samples I see in .NET have you manually create the foreign
key constraints. I'm trying to detect them from the database's schema.
I'm working with MySQL4.1.

When I query the database, I get my UniqueConstraints but I don't get
my ForeignKeyConstraints, and wondered if anyone knew how to do it.

In my program, the business objects are associated in the database with
a main table, and collections within this object are associated by
foreign key constraints to other tables.

So say I have an object, User. It's associated with the table "user",
which is identified by a unique integer key, ObjectId. It contains no
foreign key constraints.

A User can belong to any number of Groups, and a Group can contain any
number of Users, so I have a "groups" table keyed by its own integer
ObjectId. It also contains no foreign key constraints.

Connecting the two is a "usergroups" table, also keyed by integer
ObjectId, and all it contains is two foreign keys. "UserId" refers to
"user.ObjectId" and "GroupId" refers to "groups.ObjectId".

My code looks like this:

StringBuilder sb = new StringBuilder();
sb.Append( "SELECT * FROM " );
sb.Append( strTableName );
using ( OdbcCommand cmd = new OdbcCommand( sb.ToString(),
connection ) )
{
using ( OdbcDataAdapter da = new OdbcDataAdapter() )
{
da.SelectCommand = cmd;
// Get the schema for this table and add
// the schema for any foreign key tables to the
// dataset
DataTable table = new DataTable( strTableName );
da.FillSchema( table, SchemaType.Source );
ds.Tables.Add( table );
foreach ( Constraint constraint in
table.Constraints )
{
if ( constraint is ForeignKeyConstraint )
{
... do stuff...
}
}
}
}

When you run this code on the "usergroups" table, which has two foreign
key constraints, no ForeignKeyConstraints exist. I have my
UniqueConstraint on UserId but that's it. If I look in the debugger,
table.Constraints.Non-Public members.fLoadForeignKeyConstraint is
false, which looks like the culpret. Do I need to set this flag to
give me the foreign key constraint? If I do, how do I do it?

I see there's the DataTable.ParentRelations and ChildRelations
properties but I haven't tried using them because I think they just
access the ForeignKeyConstraints. I figured if there are no
ForeignKeyConstraints in the collection, I'm going to get the same
results if I rip out my code and rewrite it to use DataRelations.

Does anyone know how to do this?

Thanking you for in advance for your time and thoughts.

Shelah

 

[Francois Bonin [C# MVP]]

Given that you are working with a MySQL db, you can connect using an
OleDbConnection object, and then use the GetOleDbSchemaTable() method to get
your schema information


OleDbConnection conn;
//
conn.Open()
Dim schemaTable As DataTable =
conn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, New Object()
{Nothing, Nothing, Nothing, Nothing, Nothing})
conn.Close()


This will get you all the foreign key definitions inside the schemaTable
DataTable.
You can get more info by looking at the documentation for
OleDbConnection.GetOleDbSchemaTable()

HTH
Cois