constraints lesson 8. skills matrix constraints domain integrity: a domain refers to a column in a...
TRANSCRIPT
ConstraintsConstraints• Domain Integrity: A domain refers to a
column in a table. Domain integrity includes data types, rules, and defaults, constraints, triggers, and XML schema as applied to a column.
• Entity Integrity: Entity integrity applies to rows and includes rules, NULLs, defaults, constraints, and triggers.
• Referential Integrity: Referential integrity applies between tables or columns of the same table and includes constraints and triggers.
Check ConstraintsCheck Constraints
• Use a check constraint in a Transact-SQL statement linked to a field.
• Check constraints restrict the data acceptable in the field even if the data belongs to the correct data type.
Check ConstraintsCheck Constraints• You can’t use default constraints in a few
places, though:– Defaults can’t be used on columns with the
timestamp data type.– Defaults can’t be used on IDENTITY columns.
• IDENTITY columns contain a number automatically incremented with each new record.
– Defaults can’t be used on columns with the ROWGUIDCOL property set. • ROWGUIDCOL indicates that the column includes
a globally unique identifier (GUID) column for the table.
Default ConstraintsDefault Constraints
• If users leave fields blank by not including them in the INSERT or UPDATE statement that they use to add or modify a record, default constraints fill in those fields.
• This can be a big timesaver in a data-entry department—if you use it correctly.
Unique ConstraintsUnique Constraints
• You should use a unique constraint when you need to ensure that no duplicate values can be added to a field.
RulesRules
• Rules (CREATE RULE) store your defined constraints which can then be applied to multiple tables.
• Create it once and use it many times. • Microsoft promises to remove
support for rules in an upcoming version of SQL Server.
Primary Key ConstraintPrimary Key Constraint
• Primary key constraint uses one or more columns in a table. – When using two or more columns, it’s
called a composite primary key. – The primary key uniquely identifies a
row in a table. Some considerations include:
Primary Key ConstraintPrimary Key Constraint
• You may have but one primary key per table.
• The value of the column, or composite of two or more columns, must be unique. A duplicate value will be denied by the RDBMS.
• Null values are not allowed.• Defining a primary key also automatically
generates an index. SQL Server uses the index to enforce uniqueness.
Foreign Key ConstraintForeign Key Constraint
• A foreign key constraint relates to a column or combination of columns used to establish and enforce a link between the data in one or more tables or one or more columns in one table. Some considerations include:– The foreign key must reference a
primary key or unique constraint.– A user must have REFERENCES
permission on a referenced table.
Foreign Key ConstraintForeign Key Constraint– A foreign key constraint that uses only the
REFERENCES clause without the FOREIGN KEY clause refers to a column in the same table.
– Foreign keys do not create indexes automatically.
– The data type of the foreign key and the data type of the column to which it points must match. The names do not need to match.
– You may declare multiple foreign keys in a single table.
SummarySummary
• You learned that tables are wide open to just about any kind of data when they’re first created.
• Users can’t violate the data type of an attribute; other than that, the table accepts anything, including the illogical.
• Guard against a data-entry person making an appointment for a date in the past, for example.
SummarySummary
• To restrict the data your users can enter in a text box in the client application, you learned to create default, check, and unique constraints. – You learned rules are available but
discouraged.
• Primary key and unique constraints guard against duplicate data entry.
Summary for Certification ExaminationSummary for Certification Examination• Know your constraints. Understand the
constraints discussed in this lesson. – Check constraints restrict the data a user
may to enter in a column, even through the data type does not restrict the data.
– Default constraints fill in data for you automatically when you do not specify a value while inserting a new record.
– Unique constraints prevent users from accidentally inserting repetitive values.
• Know that SQL Server still supports rules.