The transactions among 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 two issues that must be dealt with:
- Each record that a table (A) holds should/must be unique among all the
other records of the same table (A). For example, if you create a list of
bank accounts for different customers on a table, you should make sure
that there is a unique (no duplicate) bank account number for each
customer because each customer should have one and must have one
account number. This ensures that there are no duplicate records on
- A table (A) that holds information must make it 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.
These two 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 in SQL Server, 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 as 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 constraint. Such a field should have
an int data type.
To specify a primary key on a table, you
create one column as the PRIMARY KEY constraint and there can be only one
PRIMARY KEY constraint on a table. To do this in Enterprise Manager,
create a column and specify its data type. Then, on the toolbar, click the
Set Primary Key button .
create a primary column using SQL, on the right side of the column
definition, type PRIMARY KEY.