Database Reference
In-Depth Information
Primary keys usually benefit the system. They provide better data integrity, and they improve supportability of
the system. I would recommend defining the primary keys when you can afford to have the additional index on the
primary key columns.
■
some sQL server features, such as transactional replication, require that tables have primary keys defined.
Defining a clustered index without a primary key is not sufficient.
Note
Because primary keys are implemented as regular indexes, there is no special catalog view for them. You can
look at the
is_primary_key
column in the
sys.indexes
catalog view to determine if the index is defined as the
primary key.
■
sQL server Catalog Views allow us to obtain information about database and server metadata programmatically.
Note
Unique Constraints
Unique constraints enforce the uniqueness of the values from one or multiple columns. Similar to primary keys,
unique constraints uniquely identify rows in a table, although they can be created on the nullable columns and would
thus treat nulls as one of the possible values. Like primary keys, unique constraints belong to the logical database
design and are implemented as unique, non-clustered indexes on the physical level.
The code in Listing 7-3 shows the table with two unique constraints defined: one constraint defined on the
SSN
column and another one on the combination of
DepartmentCode
and
IntraDepartmentCode
columns.
Listing 7-3.
Defining unique constraints
create table dbo.Employees
(
EmployeeId int not null
constraint PK_Employees primary key clustered,
Name nvarchar(64) not null,
SSN char(9) not null
constraint UQ_Employees_SSN unique,
DepartmentCode varchar(32) not null,
IntraDepartmentCode varchar(32) not null,
constraint UQ_Employees_Codes
unique(DepartmentCode, IntraDepartmentCode)
)
As you see in Figure
7-1
, SQL Server Management Studio lists unique (and primary key) constraints in the two
different places: under the
Key
and
Indexes
nodes.
