Microsoft SharePoint Team Blog
The official blog of the Microsoft SharePoint Product Group

Creating a Foreign Key in SQL



You can also create a foreign key in the SQL. The basic formula to use is:

FOREIGN KEY REFERENCES ParentTableName(ForeignKeyCcolumn)
The FOREIGN KEY expression and the REFERENCES keyword are 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 Persons
(
PersonID int identity(1,1) PRIMARY KEY NOT NULL,
FirstName varchar(20),
LastName varchar(20) NOT NULL,
GenderID int NULL FOREIGN KEY REFERENCES Genders(GenderID)
);
The Foreign Key Constraint



Notice that the foreign key doesn't have an object name as we saw for the primary key. If you don't specify a name for the foreign key, the SQL interpreter would automatically create a default name for you. Otherwise, to create a name, after creating the column, enter the CONSTRAINT keyword followed by the desired name and continue the rest as we saw above. Her is an example:

CREATE TABLE Persons
(
PersonID int identity(1,1) PRIMARY KEY NOT NULL,
FirstName varchar(20),
LastName varchar(20) NOT NULL,
GenderID int NULL CONSTRAINT FKGenders
FOREIGN KEY REFERENCES Genders(GenderID)
);
Establishing a Relationship


Introduction



As mentioned already, a relational database is one in which information flows from one table to another. To prepare the tables for this, you create primary and foreign keys, which we have done so far. Once the tables are ready, you can link them, which is referred to as creating a relationship between two tables.

If you didn't create a foreign key with SQL code, you can create it when establishing a relationship between two tables.

Creating a Relationship



To create a relationship between two tables

Open the child table in the design view
Right-click (anywhere in) the table and click Relationships...
If the (necessary) foreign key doesn't exist, click Add and specify its name under Identity) in the right side.
Under Selected Relationships, click the foreign key that will hold the relationship
In the right side, expand Tables And Columns Specification
Click its ellipsis button
In the Primary Key Table combo box, select the parent table that holds the primary data
Under the parent table, click and select its primary key column
Under Foreign Key Table, make sure the name of the current table is set.
Under the name of the child table, click and select the name of the foreign key column. Here is an example:

Click OK.
When a relationship has been created, it would show in the Tables And Column Specification section:

In the same way, you can create other relationships by clicking Add and configuring the link.
Once you have finished, click Close




source : http://blogs.msdn.com/sharepoint/default.aspx