How do i delete a foreign key constraint programmatically in Microsoft Access

Question

How do i delete a foreign key constraint programmatically in Microsoft Access, preferable using SQL. For starters i don't know how to find the name of the foreign key.

I connect to Access from a Java application using the JDBC-ODBC bridge. I want to execute the SQL from my Java application.

I can see the relationship in Access, in the RelationShip view, but there seems to be no way of finding out the name. If i could find out the name i expect i could drop it with an ALTER TABLE statement.

Answer 1

I've tried accessing the foreign key name via JDBC's DataBaseMetadata object, but the JDBC-ODBC bridge does not implement the required functions. So i've resorted to droping and recreating the entire table with the foreign key.

Answer 2

Determine the relationship using

SELECT szRelationship FROM Msysrelationships WHERE szObject = 'childtablename' and szReferencedObject = 'parenttablename'

THEN

Use the ALTER TABLE command. Something along the line of this

ALTER TABLE Table2 DROP CONSTRAINT Relation1