Microsoft Access Database Development With VBA

SQL Keywords: FOREIGN

   

Description

A foreign key is a column on a table whose data is coming from another table. The formula to create a foreign key in the SQL is:

ColumnName DataType REFERENCES ParentTableName(ForeignKeyCcolumn) 

The REFERENCES keyword is required. In the ParentTableName placeholder, enter the name of the primary table that holds the information that will be accessed in the current table. In the parentheses of ParentTableName, enter the name of the primary column of the parent table. Here is an example:

CREATE TABLE Departments(Gender varchar(20) PRIMARY KEY);

CREATE TABLE Persons
(
    PersonalIdentification char(10),
    FirstName varchar(20),
    LastName varchar(20) NOT NULL,
    Gender varchar(20) REFERENCES Departments(Gender),
    CONSTRAINT PK_Persons PRIMARY KEY(PersonalIdentification)
);

This technique allows you to ask the database interpreter to give a name to the foreign key you are creating. An alternative is to specify a name of your choosing. To do this, after the list of columns of the table, create a foreign key constraint with an explicit name. Here is an example:

Private Sub cmdCreateForeignKey_Click()
    DoCmd.RunSQL "CREATE TABLE CoursesLevels" & _
                 "(" & _
                 "    CourseLevel varchar(25), " & _
                 "    Comments note, " & _
                 "    CONSTRAINT PK_CoursesLevels PRIMARY KEY(CourseLevel) " & _
                         ");"

    DoCmd.RunSQL "CREATE TABLE Courses" & _
                 "( " & _
                 "    CourseCode varchar(10) not null, " & _
                 "    CourseName varchar(60), " & _
                 "    CourseLevel varchar(20), " & _
                 "    Notes note, " & _
                 "    CONSTRAINT FK_CourseLevels Foreign Key(CourseLevel)  " & _
                 "      REFERENCES CoursesLevels(CourseLevel), " & _
                 "    CONSTRAINT PK_Courses PRIMARY KEY(CourseCode) " & _
                 ");"
End Sub

Adding a Foreign Key to an Existing Table

Consider the following table named Courses:

Private Sub cmdCreateTable_Click()
    DoCmd.RunSQL "CREATE TABLE CoursesLevels" & _
                 "(" & _
                 "    CourseLevel varchar(25), " & _
                 "    Comments note, " & _
                 "    CONSTRAINT PK_CoursesLevels PRIMARY KEY(CourseLevel) " & _
                         ");"

    DoCmd.RunSQL "CREATE TABLE Courses" & _
                 "( " & _
                 "    CourseCode varchar(10) not null, " & _
                 "    CourseName varchar(60), " & _
                 "    Notes note, " & _
                 "    CONSTRAINT PK_Courses PRIMARY KEY(CourseCode) " & _
                 ");"
End Sub

This Courses table can benefit from a foreign key that represents the first table. If you have an existing table and you want to add a new column that would act as a foreign key,

Private Sub cmdCreateForeignKey_Click()
    DoCmd.RunSQL "ALTER TABLE Courses " & _
                 "ADD COLUMN CourseLevel varchar(25) " & _
                 "      REFERENCES CoursesLevels(CourseLevel);"
End Sub

As mentioned already, this type of code lets the database engine specify the name of the foreign key. If you want to create a name of your choice, explicitly create a constraint and give it a name. Here is an example:

Private Sub cmdCreateForeignKey_Click()
    DoCmd.RunSQL "ALTER TABLE Courses " & _
                 "ADD COLUMN CourseLevel varchar(25), " & _
                 "CONSTRAINT FK_CoursesLevels FOREIGN KEY(CourseLevel) " & _
                 "  REFERENCES CoursesLevels(CourseLevel);"
End Sub

Imagine you have a table that has a column that is in fact the foreign key of a parent table but the column was not specified as such.  To formally change such as a column into a foreign key, start an ALTER TABLE expression and add a foreign key that indicates the parent table and the corresponding column. Here is an example:

Private Sub cmdAddForeignKey_Click()
    DoCmd.RunSQL "ALTER TABLE Employees " & _
                 "ADD CONSTRAINT FK_Departments FOREIGN KEY(DepartmentCode) " & _
                 "  REFERENCES Departments(DepartmentCode);"
End Sub
 
 
     
 

Home Copyright © 2011-2013 FunctionX, Inc. Home