We help IT Professionals succeed at work.

ALTER TABLE syntax to drop a relationship

forester
forester asked
on
Anyone know the ALTER TABLE   CONSTRAINT syntax for droping a relationship ?

What modification is needed for something like this?

ALTER TABLE Projects
DROP CONSTRAINT (Projects.RelateFieldName) REFERENCES (Table2.RelateFieldName);

???????????
Comment
Watch Question

Commented:
I did this in a module
Replace T1 with your Table Name
Replace C1 with the Constraint Name (Relationship)

Public Function RemoveCon()
Set dbs = CurrentDb
dbs.Execute "ALTER TABLE T1 DROP CONSTRAINT C1;"
dbs.Close
End Function

Hope this clears things up.

Author

Commented:
How can you identify the name of the relationship (i.e., "constraint name")?

Commented:
The following code will list your indexes, including relationships, in the intermediate window.
I'm not sure how to determine if an index is also a relationship...

Sub IndexProperty()

    Dim dbs As Database
    Dim tdf As TableDef
   
    Dim idxLoop As Index

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("T1")
    Set tdf = dbs.TableDefs!T1

    With rst

        ' Enumerate Indexes collection of table.
        For Each idxLoop In tdf.Indexes
            .Index = idxLoop.Name
            Debug.Print "Index = " & .Index
            .MoveFirst
        Next idxLoop
        .Close
    End With

    dbs.Close

End Sub

Author

Commented:
Thank you CSimpson.  

An index IS separate from a relationship. (I tried this earlier.) (There is a button on the Toolbar that lists the indices.)  However, when I drop an index, it does not drop the relationship. That's why I guessed that a relationship must have a different name.  If I use a data definition query to create a relationship, I must give it a name in the query syntax. So, I can successfully drop any that I've made via a data definition query because I know the name of the relationship. However, I can't seem to drop any relationships that I made the old-fashioned way (i.e., via the Relationships Window when I first designed the database).

I'm beginning to suspect that it is not possible - that is, Microsoft has no SQL data definition syntax for accomplishing this task.
Commented:
Found what you are looking for.

The main thing to learn here is that the relationship name is simply the 2 tables name joined together.

I tested this by creating a relationship manually.
Listing it with the following code and then removing it with the previous code.

This is straight copy from help file so edit what you need to.

' Enumerate the Relations collection of the Northwind
    ' database to report on the property values of
    ' the Relation objects and their Field objects.
    For Each relLoop In dbsNorthwind.Relations
        With relLoop
            Debug.Print
            Debug.Print .Name & " Relation"
            Debug.Print "        Table - Field"
            Debug.Print "  Primary (One) ";
            Debug.Print .Table & " - " & .Fields(0).Name
            Debug.Print "  Foreign (Many)  ";
            Debug.Print .ForeignTable & " - " & _
                .Fields(0).ForeignName
        End With
    Next relLoop

Author

Commented:
Wonderful!  Thank you very much!

Author

Commented:
Ah, a piece of further information.  If you display the System Objects in an Access database, you will find a table called MySysRelationships.  This gives the name of each relationship.  (Answered my own question - but only with a tip from CSimpson.) The names of the relationships indeed are a concatentation of the two table names linked by the relationship.

Author

Commented:
Make that "MSysRelationship"

Explore More ContentExplore courses, solutions, and other research materials related to this topic.