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: