1 / 16

Rohit Khokher

Relational Integrity Constraints. Rohit Khokher. Relational Integrity Constraints. • A relational database schema (i.e. DB definition) consists of – relation schemas (table definitions) – integrity constraints • Integrity constraints are rules which all instances

nadine
Télécharger la présentation

Rohit Khokher

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Relational Integrity Constraints Rohit Khokher

  2. Relational Integrity Constraints • A relational database schema (i.e. DB definition) consists of – relation schemas (table definitions) – integrity constraints • Integrity constraints are rules which all instances of the DB must satisfy in order to correctly model the real world. • Any operation that would violate a declared constraint will be disallowed

  3. Integrity and DB design • Many real-world constraints are imposed by a combination of – good design of relations (such as via ER modelling) plus – maintenance of key (uniqueness) constraints

  4. Types of constraints • • Domain constraints • Entity integrity constraints • • Referential integrity constraints • • Semantic integrity constraints

  5. Entity Integrity Constraint • Entity constraints are of two types: • Unique constraints • Primary key constraints • The unique value rule/ constraint means that each value in a particular column is unique, such as DEPTNO in the DEPT table. Oracle rejects duplication of records when the unique key constraint is used. • The primary key value / constraint specifies that each row of a table must be indemnified by a unique value. It is almost similar to unique key constraint. Its needs are best felt when a relation has to be set between tables, because in addition to preventing duplication it also does not allow null values.

  6. Real world rules: Employee Relation An employee has only one name, is in one department, has one supervisor It is impossible to violate the rules so long as no two tuples in the employee relation have the same ID.

  7. Key constraints • specify attributes or combinations of attributes which must be unique. • Primary keys must be unique to allow the key to be use to identify tuples. – e.g. Employee ID must be unique in the Employee table • we may wish to specify that other (non primary key) attributes should be unique

  8. NOT NULL constraints • ENTITY INTEGRITY: no part of a primary key field can contain NULL – if it did it would not be distinguishable from any other NULL. • Total participation constraints are encoded by insisting that the foreign key representing the relationship is NOT NULL – E.g. Student Course can not be NULL • We may wish to specify that other attributes are required (i.e. not null). – e.g. every order must have a date associated with it. • In Access use “Required” property for such fields.

  9. Domain Integrity Constraint • These constraints set a range, and any violations that take place will prevent the user from performing the manipulation that caused the breach. There are basically two types of Domain Integrity Constraints. • Not Null constraint • Check constraint • By default the tables can contain null values. The enforcement of Not Null constraint in a table ensures that the table contains values. Oracle will not validate the record until this is satisfied. • The other type of constraint available under this classification is the ‘check’ constraint. This can be defined to allow only a particular range of values. When the demarcation specified in this range is violated Oracle rejects the records.

  10. Referential integrity constraints • Aka Foreign Key constraints; • require that a value referred to in some attribute actually exists as an entity in some table. E.g. – The employee in a Works-On tuple, unless it is null, must exist as an employee ID in the Employee table • Most referential integrity constraints arise from relationships in the ER model.

  11. Sample Ref. Integrity constraints Customers Orders Order-items Items Each arrow represents a referential integrity constraint. The source of the arrow is called a foreign key.

  12. Maintaining referential integrity • DBMS checks ref. integrity when a referencing value is inserted or changed • e.g. – when an order is entered, the entered customer id must exist in Customers – if the customer associated with an order is changed, the new value must exist in Customers • user is asked for a valid value

  13. Maintaining referential integrity (cont.) • DBMS checks ref. integrity when a tuple in the referenced relation is deleted or its primary key is modified e.g. – if you try to delete a customer tuple and there is an order which references that customer – if you change the item# of an item which appears on some orders • If deletion or modification would cause a constraint violation, DBMS has three options.

  14. Options • cascade the deletion or modification – e.g. delete any order associated with the deleted customer – e.g. change that item# in all orders to be the new item# • nullify the referencing value – i.e. set the order customer number to NULL • reject the deletion or modification

  15. Semantic integrity constraints • express general restrictions on the data and changes to it. – e.g. salary should not exceed 200,000 – e.g. salary cannot decrease – e.g. an employee’s salary should not be greater than that of the manager of the department. • Limited support for semantic constraints in some RDBMS.

More Related