Transcript Constraints
Constraints cis 407 Types of Constraints & Naming Key Constraints Unique Constraints Check Constraints Default Constraints Misc Rules and Defaults Triggers What is a Constraint • A constraint is a restriction. Placed at either column or table level, a constraint ensures that your data meets certain data integrity rules. Types of Constraints • Domain Constraints deal with one (or more) columns, e.g., “unit price > 0” – The domain of unit price is the positive integers. Dom(unitPrice) • Entity Constraints: – Each row must have unique column or combination of columns called Keys. • Referential Integrity Constraints – E.g., all parts must be supplied by a valid supplier Constraint Naming • You really should NAME each constraint you create (though SQL Server does not require this). See example for my naming convention or book pg 155. – E.g. CKPriceExceedsCost • Prefer to create separately tables and constraints (scripts are easier to manage). Key Constraints • Primary Key Constraint for a table – Used with relating that table to another • Relational databases relate tables by primary and foreign keys. • Can create primary key during table creation (see example 156) • Prime key implies unique Prime Key on Existing Table • Use Accounting • Alter Table Employees add constraint PK_EmployeeID PRIMARY KEY (employeeID) • Note use of constraint name PK_EmployeeID – Constraint can be addressed later for alternation or deletion • Note naming convention (from MS) Foreign Key • Create TABLE orders ( orderID int identity not null, customerNO int not null ) • Alter table orders add constraint FK_EmployeeCreatesOrder FOREIGN KEY (employeeID) references employees (employeeID) Cascading Actions • Be careful with foreign key constraints! • Default SQL Server behavior is to restrict parent row from being deleted if any child rows exist – E.g. cannot delete supplies until all supplied parts deleted. • However, can set thing up to auto delete child rows if parent row deleted • Be careful of dependency chains! Cascade (pg 162) • Referential integrity action • Alter table orderDetails add constraint FK_orderContainsDetails foreign key (orderid) references orders(orderid) on update no action no delete cascade UNIQUE Constraint • Unique does not automatically prevent null – must choose not null, but can only have one null in that column. • Can add unique constraint on existing table, but if any rows already in table, may have problems • Note exec sp_helpconstraint Check Constraint • Cross check within row • Alter table customers add constraint CN_customerDateInSystem check (dateInSystem <= getDate() ) Default • Default value if one not offered in insert statement • Alter table customers add constraint CN_customerDefaultDateInSystem default getdate() for dateInSystem • Alter table customers add constraint CN_customerAddress default ‘unknown’ for address1 Disabling Constraint • Constraints can be temp turned off when doing bulk data loads – Must generate exception report and clean up data – Then can turn constraint back on Constraints v. Exception Reports • Can use exception reports instead of constraint – Constraint forces only good data to be entered in the system • Slows down entry, customer service, etc – Exception report finds bad data • You then allow bad data to be entered – e.g., part for which no supplier • Must have gang of people to clean up mess • MUCH BETTER TO ONLY HAVE CLEAN DATA IN DATABASE Triggers • Triggers can be used to create integrity constraints • Triggers are like a stored procedure but fired when certain conditions in database met. • Covered in a later chapter