2779B Module 5 - Implementing Data Integrity

Oh Mark, if I only had a nickel for every time you said, “this is going to be a very interesting module”

Constraints

- Domain Integrity (columnar) (the domain of values that are considered to be valid in a particular column)

- Entitiy (rownar*) Integrity ability to unique identify any row in a table

- Referential Integrity (between tables) –using foreign keys “we will not orphan records”

*rownar isn’t a real word I made it up, means the integrity of the row

Enforcing Data Integrity

List of Methods and the purpose of each one

-Data Types

-Determine the type of data that can be stored in a column.

-Rules going away?

-enforce domain integrity

-Determine the acceptable values that can be inserted into a column.

-Specifies a pattern that data has to match.

-i.e. there can only be five characters in this column no less no more.

-Not NC compliant, not strictly necessary.

-It’s a free standing DB object.

-Defaults going away?

-Determine the value of a column if a value isn’t specified

-work the same as rules, independent objects

-enforce domain integrity by providing a value that will be inserted into the table if no other value is available.

-Constraints

-Determine how the DB Engine enforces data integrity.

-ANSI compliant

-built into the table

-can be used to enforce all three kinds of integrity

-Triggers

-Determine code that Is executed automagically when a table is modified

-independent DB objects

-two kinds AFTER and INSTEAD OF

-Know the difference between a DDL trigger(only responds to CREATE, ALTER, or DROP) and a DML trigger(update,delete,insert)

-XML Schemas

-Determine the acceptable content of XML documents and fragments placed in a column defined with the XML Data type.

-XML Documents validate against an XML schema collection

-XML Schemas are contained within XML Collections

Constraints

-primary enforcement tool

Types of constraints available

Primary Key (Entity)

-Almost all tables require a PK

-Null values are not allowed

-Makes sure that the entire record is unique

-Only one PK per table

-will create a clustered index on the column that the PK is applied to… you can change this to a nonclustered index but you can not remove the index.

Default (Domain) going away?

-Determine the value of a column if a value isn’t specified

-System supplied

-tracking purposes, you can make a default constraint on a modified column to insert the user who is changing the column.

Note to self: I’ve done this with triggers… didn’t occur to me to do this with a default, maybe that would help performance? I need to check the lp_neighborhood table I just created in Triangle. Later Note: Dur, if there is already a default then this isn’t going to update.

Check (Domain or Referential)

-restricts the values that can be entered into a column with an insert or update statement.

-you can create multiple check constraints per column.

-you can reference columns in the same table

-check constraints can NOT contain sub queries.

Unique (Entity)

-Enforces uniqueness on every value in the column

-One and only one null value allowed in a unique column

-can include more than one column

-unique constraint creates a nonclustered index on the column on the constraint is applied to you can change this to clusterd but not common, you want the PK to have the clustered index.

-the index that this constraint creates can not be dropped without dropping the whole constraint.

-you cant have more than one PK but you CAN have more than one unique column.

Foreign Key (Domain or Referential)

-Enforces referential integrity between columns in same or different tables.

-Must reference a column with a primary key or unique constraint.

-user MUST have REFERENCES or SELECT permission on the referenced table to insert or update a constrained column.

Cascading Referential integrity, he says excitedly.

Scenario, you have a orders table and a customers table. In order to add a record to the orders table you HAVE to have a record in the customers table. (PK/FK relationship)

Say you get really mad at a customer who has placed many orders and you want them to be removed from the customers table. Because of the FK/PK relationship you cant delete this customer. You would be orphaning records in the orders table.

The traditional way of handling the situation is with a trigger….it would alter or delete the records in the orders table then allow you to delete the customer

Now is SQL Server 2005, you can cascade updates or deletes in the context of the foreign key constraint.

If your doing an update on the primary key field the FK filed will automagically update to match the PK.

If your doing a delete on the PK field (deleting a row in the parent table) every child record(FK) that referenced that PK will also be deleted.

You can also SET NULL, allowing nulls in your foreign key tables… which really isn’t a good idea.*

You can SET DEFAULT, on the foreign key column as well changing it to a default if the PK goes away. This allows you to put a record in the child table without having a corresponding record in the parent table. Also not a good idea.*

*The last two sentences are recommended only in unusual circumstances.

Considerations

-Give meaningful names to constraints

-You can create change drop constraints without having to drop and recreate the table

-perform error checking on your applications and transactions, try to check the data BEFORE it gets to your table to help performance.

-you can disable CHECK and FOREIGN KEY constraints to

-improve performance of large batch jobs

-avoid checking existing data when you add new constraints to a table. Awesome!!

This also applies the new constraint if you go back and update the legacy data.

-CHECK and FOREIGN KEY constraints are the only ones that can be disabled.