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

Relationships and Data Integrity


The Primary Key


Relational Databases


A relational database is a system in which information flows from one database object to another. For example, on a bank database, you can use one object to create accounts for customers and use another object to process transactions that the owners of bank accounts need. The reason is that the same customer may need to perform various transactions, regularly. Instead of creating a new account every time the customer wants to perform a new transaction, you can use one account as a reference and bring up this account whenever the customer wants to deposit or withdraw money.

To apply the rules of relational databases, you create some types of relationships among the objects of the database.

The transactions among the various objects of a database should make sure information of one object is accessible to another object. The objects that hold information, as we have mentioned already, are the tables.

To manage the flow of information from one table (A) to another table (B), the table that holds the information, A, must make it available to other tables, such as B. There are various issues that must be dealt with:

You must be able to uniquely identify each record from a table (A) without any confusion. For example, if you create a list of cars on a table, you should make sure that there is a unique (no duplicate) tag number for each car because each car should have one and must have one tag number. This ensures that there are no duplicate records on the table.
A table (A) that holds information should make that information available to other tables (such as B)
Two tables must not serve the same purpose. Once you have unique information on each table, one table can make its data available to other tables that need it so that the same information should not be entered in more than one table
These problems are solved by specifying a particular column as the "key" of the table. Such a column is referred to as the primary key.

In a relational database, which is the case for most of the databases you will be creating, each table should have at least one primary key. As an example, a primary key on an Account table of a bank database can be set on a Bank Account field because each customer should have a unique bank account number. A table can also use more than one column to represent the primary key if you judge it necessary.

Once you have decided that a table will have a primary key, you must decide what type of data that field will hold. If you are building a table that can use a known and obvious field as unique, an example would be the shelf number of a library, you can set its data type as char or varchar and make it a primary key. In many other cases, for example if you cannot decide on a particular field that would hold unique information, an example would be customers Contact Name, you should create your own unique field and make it the Primary Key. Such a field should have an int data type. Practical Learning: Introducing Relationships




Open SQL Server Management Studio and connect to the server
Right-click the name of the server and click New Query
To start a new database, type the following code:
-- =============================================
-- Database: YugoNationalBank
-- Author: FunctionX
-- Date Created: Monday 09 April 2007
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'YugoNationalBank'
)
DROP DATABASE YugoNationalBank
GO

CREATE DATABASE YugoNationalBank
GO






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