Relational Databases

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:

1. 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.
2. A table (A) that holds information should make that information available to other tables (such as B)
3. 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 car table of a car rental company can be set on a Tag Number field because each car should have a unique tag 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.

To create a primary key in the Microsoft SQL Server Management Studio or Microsoft Visual Studio, in the table, create a column and specify its data type:

• Then, on the toolbar, click the Set Primary Key button
• You can also right-click a column and click Set Primary Key

Here is an example:

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
);

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