T-SQL笔记5：键、代理键、约束
本章摘要
1:Keys
1.1：Creating aTable with aPrimary Key
1.2：Adding aPrimary Key Constraint to an Existing Table
1.3：Creating aTable with aForeign Key Reference
1.4：Adding aForeign Key to an Existing Table
1.5：Allowing Cascading Changes in Foreign Keys
2:Surrogate Keys
3:Constraints
3.1:Creating a Unique Constraint
3.2:Using CHECK Constraints
1:Keys
A primary keyis aspecial type of constraint, which identifies asingle column or set of columns, which in turn uniquely identifies all rows in the table.
Constraintsplace limitations on the data that can be entered into a column or columns. A primary key enforces entity integrity, meaning that rows are guaranteed to be unambiguous and unique. Best practices for database normalization dictate that every table should have a primary key. A primary key provides a way to access the record, and ensures that the key is unique. A primary key column can’t contain NULL values.
To designate a primary key on a single column, use the following syntax in the column definition:
The token PRIMARY KEY is included at the end of the column definition.
A composite primary key is the unique combination of more than one column in the table. In order to define a composite primary key, you must use a table constraint instead of acolumn constraint. Setting a single column as the primary key within the column definition is called acolumn constraint. Defining the primary key (single or composite) outside of the column definition is referred to as a table constraint.
The syntax for atable constraint for aprimary key is as follows:
Foreign key constraints establish and enforce relationships between tables and help maintain referential integrity, which means that every value in the foreign key column must exist in the corresponding column for the referenced table.
Foreign key constraints also help define domain integrity, in that they define the range of potential and allowed values for aspecific column or columns. Domain integrity defines the validity of values in acolumn.
The basic syntax for aforeign key constraint is:
CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES [ schema_name.] referenced_table_name [ ( ref_column ) ]
Examples：
1.1：Creating aTable with aPrimary Key
In this recipe, I’ll create atable with asingle column primary key:
CREATE TABLE Person.CreditRating( CreditRatingID int NOT NULL PRIMARY KEY, CreditRatingNM varchar(40) NOT NULL) GO
1.2：Adding aPrimary Key Constraint to an Existing Table
In this recipe, I’ll demonstrate how to add aprimary key to an existing table using ALTER TABLEand
ADD CONSTRAINT: ALTER TABLE Person.EducationType ADD CONSTRAINT PK_EducationType PRIMARY KEY (EducationTypeID)
1.3：Creating aTable with aForeign Key Reference
In this recipe, I’ll demonstrate how to create atable with aforeign key. In this example, aforeign key
reference is included in a CREATE TABLE statement:
CREATE TABLE Person.EmployeeCreditRating( EmployeeCreditRating int NOT NULL PRIMARY KEY, EmployeeID int NOT NULL, CreditRatingID int NOT NULL, CONSTRAINT FK_EmployeeCreditRating_Employee FOREIGN KEY(EmployeeID) REFERENCES HumanResources.Employee(EmployeeID), CONSTRAINT FK_EmployeeCreditRating_CreditRating FOREIGN KEY(CreditRatingID) REFERENCES Person.CreditRating(CreditRatingID) )
How It Works
In this example, a table was created with two foreign key references. The first four lines of code defined the table name and its three columns:
CREATE TABLE Person.EmployeeCreditRating( EmployeeCreditRating int NOT NULL PRIMARY KEY, EmployeeID int NOT NULL, CreditRatingID int NOT NULL,
On the next line, the name of the first foreign key constraint is defined (must be a unique name in the current database):
CONSTRAINT FK_EmployeeCreditRating_Employee
The constraint type is defined, followed by the table’s column (which will be referencing an outside primary key table):
The referenced table is defined, with that table’s primary key column defined in parentheses:
REFERENCES HumanResources.Employee(EmployeeID),
A second foreign key is then created for the CreditRatingIDcolumn, which references the primary key of the
Person.CreditRating table: CONSTRAINT FK_EmployeeCreditRating_CreditRating FOREIGN KEY(CreditRatingID) REFERENCES Person.CreditRating(CreditRatingID) )
As Idemonstrated in this example, a table can have multiple foreign keys—and each foreign key can be based on a single or multiple (composite) key that references more then one column (referencing composite primary keys or unique indexes). Also, although the column names needn’t be the same between a foreign key reference and a primary key, the primary key/unique columns must have the same data type. Also, you can’t define foreign key constraints that reference tables across databases or servers.
1.4：Adding aForeign Key to an Existing Table
Using ALTER TABLEand ADD CONSTRAINT, you can add aforeign key to an existing table. The syntax for doing so is as follows:
ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES [ schema_name.] referenced_table_name [ ( ref_column ) ]
This example adds aforeign key constraint to an existing table:
ALTER TABLE Person.EmergencyContact ADD CONSTRAINT FK_EmergencyContact_Employee FOREIGN KEY (EmployeeID) REFERENCES HumanResources.Employee (EmployeeID)
How It Works
This example demonstrated adding aforeign key constraint to an existing table. The first line of code defined the table where the foreign key would be added:
The second line defines the constraint name:
ADD CONSTRAINT FK_EmergencyContact_Employee
The third line defines the column from the table that will reference the primary key of the primary key table:
The last line of code defines the primary key table, and primary key column name:
REFERENCES HumanResources.Employee (EmployeeID)
1.5：Allowing Cascading Changes in Foreign Keys
Foreign keys restrict the values that can be placed within the foreign key column or columns. If the associated primary key or unique value does not exist in the reference table, the INSERTor UPDATE to the table row fails. This restriction is bi-directional in that if an attempt is made to delete aprimary key, but arow referencing that specific key exists in the foreign key table, an error will be returned. All referencing foreign key rows must be deleted prior to deleting the primary key or unique value in question, otherwise an error will be raised.
SQL Server 2005 provides an automatic mechanism for handling changes in the primary key/unique key column, called cascading changes.
In previous recipes, cascading options weren’t used. You can allow cascading changes for deletions or updates using ON DELETEand ON UPDATE. The basic syntax for cascading options are as follows:
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] [ NOT FOR REPLICATION ]
NO ACTION : The default setting for a new foreign key is NO ACTION, meaning if an attempt to delete arow on the primary key/unique column occurs when there is areferencing value in aforeign key table, the attempt will
raise an error and prevent the statement from executing.
CASCADE For ON DELETE, if CASCADE is chosen, foreign key rows referencing the deleted primary key are also deleted. For ON UPDATE, foreign key rows referencing the updated primary key are also updated.
SET NULL New in SQL Server 2005, if the primary key row is deleted, the foreign key referencing row(s) can also be set to NULL(assuming NULLvalues are allowed for that foreign key column).
SET DEFAULT New in SQL Server 2005, if the primary key row is deleted, the foreign key referencing row(s) can also be set to a DEFAULT value. The new cascade SET DEFAULT option assumes the column has adefault value set
for acolumn. If not, and the column is nullable, a NULL value is set.
NOT FOR REPLICATION The NOT FOR REPLICATION option is used to prevent foreign key constraints from being enforced by SQL Server Replication Agent processes (allowing data to arrive via replication potentially out-of-
order from the primary key data).
In this example, a table is created using cascading options:
CREATE TABLE Person.EmployeeEducationType( EmployeeEducationTypeID int NOT NULL PRIMARY KEY, EmployeeID int NOT NULL, EducationTypeID int NULL, CONSTRAINT FK_EmployeeEducationType_Employee FOREIGN KEY(EmployeeID) REFERENCES HumanResources.Employee(EmployeeID) ON DELETE CASCADE, CONSTRAINT FK_EmployeeEducationType_EducationType FOREIGN KEY(EducationTypeID) REFERENCES Person.EducationType(EducationTypeID) ON UPDATE SET NULL)
How It Works
In this recipe, one of the foreign key constraints uses ON DELETE CASCADE in a CREATE TABLE definition:
CONSTRAINT FK_EmployeeEducationType_Employee FOREIGN KEY(EmployeeID) REFERENCES HumanResources.Employee(EmployeeID) ON DELETE CASCADE
Using this cascade option, if a row is deleted on the HumanResources.Employeetable, any refer-encing EmployeeIDin the Person.EmployeeEducationType table will also be deleted.
A second foreign key constraint was also defined in the CREATE TABLE using ON UPDATE:
CONSTRAINT FK_EmployeeEducationType_EducationType FOREIGN KEY(EducationTypeID) REFERENCES Person.EducationType(EducationTypeID) ON UPDATE SET NULL
If an update is made to the primary key of the Person.EducationTypetable, the EducationTypeID
column in the referencing Person.EmployeeEducationTypetable will be set to NULL.
2:Surrogate Keys
The IDENTITY column property, allows you to define an automatically incrementing numeric value for a single column in atable. An IDENTITY column is most often used for surrogate primary key columns, as they are more compact than non-numeric data type natural keys.
The basic syntax for an IDENTITYproperty column is as follows:
[ IDENTITY [ ( seed ,increment ) ] [NOT FOR REPLICATION] ]
NOT FOR REPLICATION option preserves the original values of the Publisher IDENTITY column data when
replicated to the Subscriber, retaining any values referenced by foreign key constraints (preventing the break of relationships between tables that may use the IDENTITYcolumn as a primary key and foreign key reference).
Unlike the IDENTITY column, which guarantees uniqueness within the defined table, the ROWGUIDCOL property ensures a very high level of uniqueness (Microsoft claims that it can be unique for every database networked in the world). This is important for those applications which merge data from multiple sources, where the unique values cannot be duplicated across tables. This unique ID is stored in auniqueidentifier data type and is generated by the NEWID system function.
The ROWGUIDCOL is a marker designated in a column definition, allowing you to query atable not only by the column’s name, but by the ROWGUIDCOL designator, as this recipe demonstrates.
Which surrogate key data type is preferred? Although using a uniqueidentifier data type with a NEWID value for a primary key may be more unique, it takes up more space than an integer based IDENTITY column. If you only care about unique values within the table, you may be better off using an integer surrogate key, particularly for very large tables. However if uniqueness is an absolute requirement, with the expectation that you may be merging data sources in the future, uniqueidentifier with NEWID may be your best choice.
3:Constraints
Constraints are used by SQL Server to enforce column data integrity. Both primary and foreign keys
are forms of constraints.
3.1:Creating a Unique Constraint
You can only have a single primary key defined on a table. If you wish to enforce uniqueness on other non-primary key columns, you can use a UNIQUE constraint. A unique constraint, by definition, creates an alternate key.
Unlike a PRIMARY KEY constraint, you can create multiple UNIQUE constraints for a single table and are also allowed to designate a UNIQUE constraint for columns that allow NULL values (although only one NULL value is allowed for a single column key, per table). Like primary keys, UNIQUE constraints enforce entity integrity by ensuring that rows can be uniquely identified.
The UNIQUE constraint creates an underlying table index when it is created. This index can be CLUSTERED or NONCLUSTERED, although you can’t create the index as CLUSTERED if a clustered index already exists for the table.
As with PRIMARY KEYc onstraints, you can define a UNIQUE constraint when atable is created either on the column definition, or at the table constraint level.
The syntax for defining a UNIQUE constraint during a table’s creation is as follows:
This example demonstrates creating a table with both a PRIMARY KEY and UNIQUE key defined:
CREATE TABLE HumanResources.EmployeeAnnualReview( EmployeeAnnualReviewID int NOT NULL PRIMARY KEY, EmployeeID int NOT NULL, AnnualReviewSummaryDESC varchar(900) NOT NULL UNIQUE)
You can apply aunique constraint across multiple columns by creating atable constraint:
CONSTRAINT constraint_name UNIQUE (column [ ASC | DESC ] [ ,...n ] )
3.2:Using CHECK Constraints
CHECK constraint is used to define what format and values are allowed for acolumn. The syntax of the CHECK constraint is as follows:
CHECK ( logical_expression )
If the logical expression of CHECK evaluates to TRUE, the row will be inserted. If the CHECK constraint expression evaluates to FALSE, the row insert will fail.
This example demonstrates adding a CHECK constraint to a CREATE TABLE definition. The GPA column’s values will be restricted to aspecific numeric range:
CREATE TABLE Person.EmployeeEducationType( EmployeeEducationTypeID int NOT NULL PRIMARY KEY, EmployeeID int NOT NULL, EducationTypeID int NULL, GPA numeric(4,3) NOT NULL CHECK (GPA > 2.5 AND GPA <=4.0))
In the previous example, the CHECK constraint expression was defined at the column constraint level. A CHECK constraint can also be defined at the table constraint level—where you are allowed to reference multiple columns in the expression, as this next example demonstrates:
CREATE TABLE Person.EmployeeEducationType( EmployeeEducationTypeID int NOT NULL PRIMARY KEY, EmployeeID int NOT NULL, EducationTypeID int NULL, GPA numeric(4,3) NOT NULL, CONSTRAINT CK_EmployeeEducationType CHECK (EducationTypeID > 1 AND GPA > 2.5 AND GPA <=4.0))
How It Works
In the first example, a CHECK column constraint was placed against the GPA column in the Person.EmployeeEducationType table:
Only aGPAcolumn value greater than 2.5 or less than/equal to 4.0 is allowed in the tableanything else out of that range will cause any INSERT or UPDATE to fail.
In the second example, the CHECKtable constraint evaluates two table columns:
This CHECKconstraint requires that the EducationTypeIDvalue must be greater than 1, in addition to the GPA requirements.