Browse Prior Art Database

Strategy for Automatically Defining the Columns for a Foreign Key

IP.com Disclosure Number: IPCOM000110086D
Original Publication Date: 1992-Oct-01
Included in the Prior Art Database: 2005-Mar-25
Document File: 2 page(s) / 102K

Publishing Venue

IBM

Related People

Li, ST: AUTHOR [+2]

Abstract

Disclosed is a strategy for automatically constructing foreign key definitions. Defining primary and foreign keys are an integral part of establishing links between tables in a relational database. After the definitions are automatically constructed, the user can accept or modify these definitions before committing them.

This text was extracted from an ASCII text file.
This is the abbreviated version, containing approximately 52% of the total text.

Strategy for Automatically Defining the Columns for a Foreign Key

       Disclosed is a strategy for automatically constructing
foreign key definitions.  Defining primary and foreign keys are an
integral part of establishing links between tables in a relational
database.  After the definitions are automatically constructed, the
user can accept or modify these definitions before committing them.

      Foreign keys are defined for a table when the user wants
columns in a table to match those of another table.  (These tables
are known as the dependent and parent tables, respectively).  The
parent table must have a primary key defined for it, and the columns
in the foreign key must be of the same type as the corresponding
columns in the primary key.  They have a one-to-one relationship in
definition, although values for the columns in the foreign key may
have a many-to-one relationship to the column values for the parent
table's primary key.

      For databases with a large number of tables, the process of
defining foreign keys is likely to be tedious and time consuming.  A
strategy (or algorithm) that could pre-construct the foreign keys
would save the user time and effort.  Due to the vast number of
possibilities that may exist when constructing foreign keys, the user
would still need to verify that the constructed foreign key
definitions are correct before committing them to the database.  With
the following strategy in place, the user would be able to save
several steps when defining a foreign key for a database table
object:
      (1) Before the user begins to define a foreign key, the parent
table name must be given.  From this, the program can retrieve the
primary key definition.
      (2) Each column in the primary key will have a set of columns
from the dependent table (the one with the foreign key) associated
with it.  When this set has only one member, a match is found.  If
more than one column exists in the set at the end of the algorithm, a
match will be arbitrarily made.  If the set is empty, no matches were
found.
      (3) For each column set, any columns in the set that do not
match the data type for their corresponding primary key column will
be removed.  If a column set has only one member left, a match was
found and the column set does not need to proceed further.  If the
set is empty, a match was not found, and the field will be left
blank, since the algorithm cannot determine which column from the
dependent table should link with the corresponding column in the
primary key of the parent table.  (In cases like this, the table
might not have the right types of columns...