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

Creating a Primary Key With SQL



To create a primary column using SQL, the primary thing to do is, on the right side of the column definition, type PRIMARY KEY. Here is an example:

CREATE TABLE Persons
(
PersonID int identity(1,1) PRIMARY KEY NOT NULL,
FirstName varchar(20),
LastName varchar(20) NOT NULL
);
The Primary Key Constraint



In the SQL, you can give a specific name to a primary key. To do this, you can first create the column. Then, somewhere before the closing parenthesis of the table, specify the primary key column using the following formula:

CONSTRAINT PrimaryKeyName PRIMARY KEY(ColumnName)
In this formula, the CONSTRAINT keyword and the PRIMARY KEY (case-insensitive) expression are required. In the PrimaryKeyName placeholder, enter the name you want to give to the primary key. In the parentheses of the PRIMARY KEY expression, enter the name of the column that will be used as the primary key. Here is an example:

CREATE TABLE Persons
(
PersonID int identity(1,1) NOT NULL,
FirstName varchar(20),
LastName varchar(20) NOT NULL,
CONSTRAINT PrimKeyPeople PRIMARY KEY(PersonID)
);
By convention or tradition, the name of the primary starts with PK_ followed by the name of the table. Here is an example:

USE Exercise2;
GO

CREATE TABLE Persons
(
PersonID int identity(1,1) NOT NULL,
FirstName varchar(20),
LastName varchar(20) NOT NULL,
CONSTRAINT PK_Persons PRIMARY KEY(PersonID)
);
GO
The Foreign Key


Introduction



Continuing with our bank database, imagine a customer comes to the bank to deposit money. We already established that it would be redundant to create a new account every time the customer comes to perform a transaction. Instead, you would get the customer's information from his or her account, provide that information to the table used to process transactions. As we described earlier, the account table should be able to provide its data to the other tables that would need that data. To make this flow of information possible from one table to another, you must create a relationship between them.

Creating a Foreign Key in the Table Design View



To make it possible for a table B to receive data from a table A, the table B must have a column that represents the table A. This columns acts as an "ambassador" or a link. As a pseudo-ambassador, the column in the table B almost doesn't belong to that table: it primarily allows both tables to communicate. For this reason, the column in the table B is called a foreign key.

A foreign key is a column on a table whose data is coming from another table.

To create a foreign key in the Table Design window, in the table that will receive the key, simply create a column with the following rules:

The column should have the same name as the primary column of the table it represents (but this is not a requirement)
The column must (this is required) have the same data type as the primary column of the table it represents
Here is an example of a column named GenderID that is a foreign key:






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