Constraints In Microsoft SQL Server 2000 - Key Constraints (Page 3 of 8 )
Keys are the cornerstone concepts of database design and management. There are four different types of common keys used in almost all database endeavors. These are Primary keys, Foreign keys, Alternate keys and Inversion keys. We will be looking at the first three keys in detail today. Inversion keys are merely alternative ways of sorting the data.
Primary Key Constraints
Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key. A Primary key ensures uniqueness within the column declared as being part of that Primary key, and that unique value serves as an identifier for each row in that table. There are two ways to create Primary keys: the CREATE TABLE and ALTER TABLE commands. Using a small, integer column as a Primary key is recommended. Each table should have a Primary key.
Now let us see how to use Primary key constraint in a create table command:
USE Accounting
CREATE TABLE
Employees
{
EmployeeID int
IDENTITY NOT NULL PRIMARY KEY,
EmployeeName
varchar(30) NOT NULL,
SSN
varchar(11) NOTNULL,
Address1
varchar(25) NOT NULL,
Address2
varchar(20) NOT NULL,
City
varchar(20) NOTNULL,
State
varchar(2) NOT NULL,
Zip
varchar(10) NOT NULL,
Contact
varchar(25) NOT NULL,
Phone
char(15) NOT NULL,
Salary money NOT
NULL,
HireDate smalldatetime NOT
NULL,
TerminationDate smalldatetime
NULL,
EmpManagerID int NOT
NULL
Department varchar(25) NOT
NULL
}
Creating a Primary key on an already existing table, is easy and it looks like follows:
Use Accounting
ALTER TABLE Customers
ADD CONSTRAINT pk_custID
PRIMARY KEY
(CustomerID)
A table may have more than one combination of columns that could uniquely identify the rows in a table. Each combination is a Candidate key. We pick one of the Candidate keys to be the Primary key. For example, in the Car_Parts table (Example 3), both Part_No and Part_Name could be Candidate keys, but only Part_No is chosen as a Primary key.
CREATE TABLE
Car_Parts
(Part_No int PRIMARY
KEY,
Part_Name char(30),
Part_Weight
decimal(6,2),
Part_Color char(15) )
The index generated by a Primary key constraint cannot cause the number of indexes on the table to exceed 249 non-clustered indexes and 1 clustered index. If CLUSTERED or NONCLUSTERED is not specified for a Primary key constraint, CLUSTERED is used if there are no clustered indexes specified for UNIQUE constraints.
Next: Foreign Key Constraints >>
More Database Code Articles
More By Gayathri Gokul
/ 116 
