tailieunhanh - Microsoft SQL Server 2008 R2 Unleashed- P83

Microsoft SQL Server 2008 R2 Unleashed- P83:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 764 CHAPTER 24 Creating and Managing Tables Constraints are covered in detail in Chapter 26 Implementing Data Integrity. This chapter introduces the basic means for adding constraints to a table. You can add constraints at the time of table creation or you can add them after a table has been created by using the ALTER TABLE statement. Listing shows a CREATE TABLE statement that has an example of each one of the five constraint types listed. The PRIMARY KEY constraint is created at the bottom of the script and is named PK_TitleHistory. The FOREIGN KEY constraint is created on the title_id column and is named FK_titles_titleHistory. The UNIQUE constraint is part of the primary key and can be identified with the UNIQUE keyword. The CHECK constraint is created on the price column it checks to make sure the price is greater than zero. Finally a DEFAULT constraint is created on the modify_user column it sets the user to the value of system if no explicit value is specified. LISTING Example of Creating Constraints with CREATE TABLE CREATE TABLE title_id CONSTRAINT FK_titles_titleHistory REFERENCES titles title_id NOT NULL change_date datetime NOT NULL title varchar 80 NOT NULL type char 12 NOT NULL price money NULL CONSTRAINT CK_TitleHistory_Price CHECK Price 0 modify_user nchar 10 NOT NULL CONSTRAINT DF_TitleHistory_modify_user DEFAULT N system1 CONSTRAINT PK_TitleHistory UNIQUE CLUSTERED title_id ASC change_date ASC You can create the same constraints as in Listing by using the ALTER TABLE statement. This means you can first create the table without the constraints and then add the constraints afterward. Listing shows the creation of the same titleHistory table with the constraints added later via the ALTER TABLE statement. LISTING Example of Creating Constraints with ALTER TABLE IF EXISTS SELECT FROM WHERE id OBJECT_ID N dbo . TitleHistory AND OBJECTPROPERTY id N IsUserTable1 1 DROP TABLE dbo . .