1 / 20

Database Constraints and Key Management Best Practices

Learn about various database constraint types such as Primary Key, Unique Key, and Foreign Key, and how to implement them effectively for data integrity. Explore key management rules and guidelines for enforcing constraint validation in server and client environments.

melvin-herman
Download Presentation

Database Constraints and Key Management Best Practices

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. Constraints

  2. Primary Key Constraint • It is derived from the entity UID or created as surrogate key. • Uniqueness is enforced by an index: • Index is not defined in Oracle Designer • All columns in key are mandatory. • It should not be updatable. • Every table should have one!

  3. Unique Key Constraint • Derived from secondary entity UIDs • Can include NULL columns • Are optional • Recommended where a surrogate key is used for PK

  4. Foreign Key Constraint • Derived from relationships • Can reference either a primary or a unique key: • But avoid referencing unique keys! • Must not be partly NULL • Can reference its own table • Cannot reference tables on other databases

  5. RENTAL TITLE COPY COPY DML DML DML DML RENTAL_ITEM COPY BOOKING BOOKING NULL DML DEFAULT Foreign Key Rules DEFAULT NULLIFY CASCADE RESTRICT (default) • Server handles RESTRICT and CASCADE Delete only X

  6. Defining Foreign Key Rules Foreign Key Properties • Cascades • Defaults • Nullifies • Restricted Cascade Rules Delete Rule Update Rule • Avoid using any update rule: • PKs should not be updatable

  7. EMPLOYEES IDENTIFIER LAST_NAME FIRST_NAME SALARY POSITION Check Constraint CHECK ((position = 'TECHNICAL' AND salary BETWEEN 3000 AND 4000) OR (position != 'TECHNICAL'))

  8. A C ID ID B FK_A FK_B ID CHECK ((FK_A IS NOT NULL AND FK_B IS NULL) OR (FK_B IS NOT NULL AND FK_A IS NULL)) Foreign Keys in Arcs DSD1

  9. Implementing Subtypes GAME and MOVIE subtypes in TITLES TITLES PRODUCT_CODE CHECK ((TI_TYPE = 'GA'AND GAME_CATEGORY IS NOT NULLAND MEDIUM IS NOT NULL AND MOVIE_CATEGORY IS NULL AND AGE_RATING IS NULL) OR ... TI_TYPE TITLE MOVIE_CATEGORY AGE_RATING GAME_CATEGORY MEDIUM

  10. More Uses for Check Constraints • START_DATE < END_DATE • Positive numeric column values > 0 • IS NOT NULL • Can only check columns within the same row; use triggers for more complex checks

  11. Create Check Constraint for EMPLOYEES : Name Check constraint name Check constraint condition Column Defining a Check Constraint • Specify the Where/Validation condition EMP_DEPT_FK

  12. Where Are Constraints Validated? • At the client, the server or both • Considerations: • Database integrity • Interactive response • Network traffic • Overall system performance

  13. Relational Table Definitions Foreign Keys BOO_COP_FK Foreign Key Properties Validation Validate in Where to enforce Client Server Both None Specifying Location of Validation

  14. Action Edit Block Field Customers: DML Posted Server Validation Server Client Guaranteed integrity Minimal network traffic No immediate feedback

  15. Action Edit Block Field Customers: DML Posted Server and Client Validation Server Client Guaranteed integrity Immediate feedback Network traffic for every check

  16. Action Edit Block Field Customers: DMLPosted Client Validation Server Client Immediate feedback No guaranteed integrity Network traffic for every check and again for DML or commit

  17. + + ? Guidelines for Choosing Where to Validate • Server validation: • Guaranteed data integrity • Client and server validation: • Check constraints • Lookup foreign keys • Case-by-case basis: • Other foreign keys • Unique keys

  18. Action Edit Block Field TransactionCommitted Customers: Controlling When to Enforce Client Server • Typical transactions: • Oracle validates each DML statement. • Complex transactions: • Oracle8 server can defer validation until COMMIT.

  19. Relational Table Definitions Foreign Keys BOO_COP_FK Foreign Key Properties Validation Defer Status INITIALLY DEFERRED INITIALLY IMMEDIATE NOT DEFERRED Specifying When Validation Occurs Whentoenforce

  20. Summary • Constraints enforce data integrity: • Entity integrity • Referential integrity • Arcs, subtypes, and simple business rules • Validation can be carried out: • Server and client • Deferred

More Related