19 Creating constraints and indexes

 

We talked about two important constraints in chapter 18: PRIMARY KEY and FOREIGN KEY constraints. This chapter looks at a few more constraints that help us ensure the integrity of the data in our tables.

We’ll also discuss indexes, which are table-related objects that help with the performance of our queries. Just as indexes in books like this one can help you quickly find the subject you’re looking for, indexes in a database can reduce the time it takes queries to find specific data.

I hope that you’ve enjoyed creating tables and associated constraints because you’re about to create more.

19.1 Constraints

By completing the examples in chapter 18, you learned that you can create constraints in two ways: by creating them for an existing table using ALTER TABLE or by creating a new table using CREATE TABLE.

You have two ways to create a constraint using CREATE TABLE. First, you can create the constraint after all columns are declared, as you did in chapter 18. Here’s an example of the PRIMARY KEY constraint you created for the category table:

CREATE TABLE category (
    CategoryID int,
    CategoryName varchar(20),
    CONSTRAINT PK_category PRIMARY KEY (CategoryID)
    );

You can also create a constraint as part of the declaration of the column after the data type has been declared. Here’s an example of how you could have done that for the primary key of the category table:

19.1.1 NOT NULL constraints

19.1.2 DEFAULT constraints

19.1.3 UNIQUE constraints

19.1.4 CHECK constraints

19.2 Automatically incrementing values for a column

19.3 Indexes

19.3.1 Clustered indexes

19.3.2 Nonclustered indexes

19.4 Lab

19.5 Lab answers