Professional SQL Server™ 2005 Programming

5.3. Key Constraints

There are four different types of common keys that you may hear about in your database endeavors. These are primary keys, foreign keys, alternate keys, and inversion keys. This chapter looks at the first three of these, as they provide constraints on the database.

An inversion key is basically just any index that doesn't apply some form of constraint to the table (primary key, foreign key, unique). (I cover indexes in Chapter 8.) Inversion keys, rather than enforcing data integrity, are merely an alternative way of sorting the data.

Keys are one of the cornerstone concepts of database design and management, so fasten your seatbelt and hold on tight. I'm hoping that by the time you got to a "Professional" level book, you already had the fundamentals of this, but this is one of the most important concepts you'll read about in this book, and it will become absolutely critical as you move on to design in Chapter 7.

5.3.1. PRIMARY KEY Constraints

Before I define what a primary key actually is, I want to digress into a brief discussion of relational databases. Relational databases are constructed on the idea of being able to "relate" data. Therefore, it becomes critical in relational databases for most tables (there are exceptions, but they are very rare) to have a unique identifier for each row. A unique identifier enables you to reference accurately a record from another table in the database; thus, forming a relation between those two tables.

This is a wildly ...