none
How to find foreign keys for a specific database table. RRS feed

  • Question

  • Hi Everyone:

    I am making some modifications to an application I wrote about three years ago. The application is designed to take either a Microsoft Access or SQL Server database. List the tables contained therein, and dynamically create a data entry form for the selected table.

    The modification I wish to add is a pull down box to make entering data like a country and/or state name easier. This is easily done if you have a form already designed for data entry. Not so much when you are creating a form dynamically at runtime.

    My question is: How do I find and return the Foreign Key constraints for a table?

    Let me give an example. I have the following relationships between four tables.

    The customer's data entry form is like this:

    As you can see the foreign keys for the Customer Data Entry form are; CountryID and StateID respectively. Normally, I would query the MSys tables  to return the foreign key restraints for tblCustomers. However, the structured manner of the .NET framework prevents this, but the .NET framework has methods available that will allow the designer to accomplish this.

    So can someone point me in the direction on how I can enter a query something like: SELECT * FROM [tblConstraints] WHERE [strTbl].

    And it returns something like:

    For tblCustomers the foreign keys are: tblCountries.CountryID and tblStates.StateID.

    Thanks,


    MRM256

    Monday, October 8, 2018 8:46 PM

All replies

  • For SQL-Server you have two methods, using T-SQL or SMO.

    For SMO checkout my GitHub repo for getting started with SMO. In the class ColumnDetails you will see

    <Category("Items"), Description("ForeignKeys DataTable")>
    Public Property ForeignKeys() As DataTable
    <Category("Items"), Description("ForeignKeys break down")>
    Public Property ForeignKeysList() As List(Of ForeignKeysDetails)
    Public Overrides Function ToString() As String
        Return Name
    End Function

    Which is hydrated in TableInformation class e.g.

    T-SQL

    DECLARE @TableName AS NVARCHAR(50) = 'Report'
    SELECT 
       OBJECT_NAME(f.parent_object_id) TableName,
       COL_NAME(fc.parent_object_id,fc.parent_column_id) ColumnName
    FROM 
       sys.foreign_keys AS f
    INNER JOIN 
       sys.foreign_key_columns AS fc 
          ON f.OBJECT_ID = fc.constraint_object_id
    INNER JOIN 
       sys.tables t 
          ON t.OBJECT_ID = fc.referenced_object_id
    WHERE 
       OBJECT_NAME (f.referenced_object_id) = @TableName

    For MS-Access look at the following thread.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Monday, October 8, 2018 9:10 PM
    Moderator
  • Hi Karen,

    Sorry for the slow reply. BTW - I had completely forgotten about the question I wrote about finding Access database relationships back in August of 2017. Thank you for the reminder.

    Speaking of which. I made a function that would return all the foreign keys for a specific table.

    Public Function dt_AccessTblRelationships(ByVal strCnn As String, _
                                                  ByVal strTbl As String) _
                                                  As DataTable
            Dim schemaTable As DataTable
            Dim restrictions() As String
            Dim dr As DataRow
            Call dt_Remove_Rel()
            dt_Rel.Clear()
            'Creating New columns for dt_Rel
            dt_Rel.Columns.Add("PK_Tbl_Name", _
                             Type.GetType("System.String"))
            dt_Rel.Columns.Add("PK_Col_Name", _
                               Type.GetType("System.String"))
            dt_Rel.Columns.Add("FK_Tbl_Name", _
                               Type.GetType("System.String"))
            dt_Rel.Columns.Add("FK_Col_Name", _
                               Type.GetType("System.String"))
    
            Using cnn As New OleDbConnection(strCnn)
                Try
                    With cnn
                        .Open()
                        restrictions = {Nothing, Nothing, Nothing, _
                                        Nothing, Nothing, strTbl}
                        schemaTable = .GetOleDbSchemaTable( _
                                      OleDbSchemaGuid.Foreign_Keys, _
                                      restrictions)
                        .Close()
                        'Call dt_Read(schemaTable)
                        'Loop through the schemaTable 
                        For RowCount = 0 To schemaTable.Rows.Count - 1
                            If InStr(UCase(schemaTable.Rows(RowCount)! _
                                           PK_TABLE_NAME.ToString), "MSYS") Then
                                Continue For
                            Else
                                dr = dt_Rel.NewRow
                                dr.Item("PK_Tbl_Name") = _
                                    schemaTable.Rows(RowCount)! _
                                    PK_TABLE_NAME.ToString()
                                dr.Item("PK_Col_Name") = _
                                    schemaTable.Rows(RowCount)! _
                                    PK_COLUMN_NAME.ToString()
                                dr.Item("FK_Tbl_Name") = _
                                    schemaTable.Rows(RowCount)! _
                                    FK_TABLE_NAME.ToString()
                                dr.Item("FK_Col_Name") = _
                                    schemaTable.Rows(RowCount)! _
                                    FK_COLUMN_NAME.ToString()
                                dt_Rel.Rows.Add(dr)
                            End If
                        Next RowCount
                        'Debug Purposes
                        'Call dt_Read(dt_Rel)
                    End With
                Catch ex As Exception
                    'Log error 
                    Dim el As New Log.ErrorLogger
                    el.WriteToErrorLog(ex.Message, ex.StackTrace, "Error")
                    Return Nothing
                End Try
            End Using
            Return dt_Rel
        End Function

    It returns a data table that populates a Data Grid View control.

    In order to place a dynamically created combobox and make it functional I have to create a Lambda function which uses both the primary table name and maybe the table's primary key to return specific information. For example: I choose the United States as the country. I will only want to return the states in the United States for use in the States combo box.

    The picture below is from my application as it stands now. This is a dynamically create form for the customers table inside an old Access database. The green arrows are what I want to achieve.

    I think I might have to use a LINQ function to query the relationships data table.

    Your thoughts please,


    MRM256

    Tuesday, October 9, 2018 5:51 PM
  • Hi MRM256,

    Do you solve the issue?If you resolve the issue, could you please mark the helpful as answer. it will be beneficial to other community.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 18, 2018 8:35 AM
  • No, I haven't. 

    While constructing the dynamic form it failed to create a text box of the appropriate length.

    This is a question for a different post.

    Thanks,


    MRM256

    Friday, October 19, 2018 5:53 PM