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:
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:
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: CREATE TABLE Persons
(
PersonID int identity(1,1) NOT NULL,
FirstName varchar(20),
LastName varchar(20) NOT NULL,
CONSTRAINT PK_Persons PRIMARY KEY(PersonID)
);
After creating a table or when inheriting a table created by someone else, you may find out that it lacks a primary key. You can add it, of course following some rules. You have two options. Imagine you have the following table: CREATE TABLE Employees
(
FirstName nvarchar(20),
LastName nvarchar(20),
DepartmentCode nchar(6)
);
You can add the PRIMARY KEY expresion after defining the new column. Here is an example: ALTER TABLE Employees
ADD EmployeeNumber int not null PRIMARY KEY;
As an alternative, you can add a column, and then use the CONSTRAINT formula to define the primary key. Here is an example: ALTER TABLE Employees
ADD EmployeeNumber int not null
CONSTRAINT PK_Employees PRIMARY KEY(EmployeeNumber);
|
|||||||||||||||||||||||||||
