1 / 12

INTEGRITY

INTEGRITY. Enforcing integrity in Oracle. Oracle Tables. mrobbert. owner. owner. granted access. Correct data types? Within ranges Any data integrity issues?. Is the data correct?. Is the data consistent?. Is the data consistent between tables? Are the same values equivalent

teresarutherford
Download Presentation

INTEGRITY

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. INTEGRITY Enforcing integrity in Oracle

  2. Oracle Tables mrobbert owner owner granted access

  3. Correct data types?Within rangesAny data integrity issues? Is the data correct?

  4. Is the data consistent? • Is the data consistent between tables? • Are the same values equivalent • Do foreign keys reference existent primary keys • Has logical consistency been maintained?

  5. How do we maintain correct, consistent data ? • Data Integrity Rules • Referential Integrity Rules • Business Rules

  6. Where do we place the constraints? • Base Tables • Constraints which always apply • Will always be enforced by DBMS • Views • Structure to meet needs • Only enforced when data accessed through view • Front end tool • more constraints avaiable • easier to fomrulate • only enforced if tool is used • lack of centralized control

  7. Base Tables • CREATE TABLE Command • makes base tables • base tables is data structure stored on DASD • All columns must have unique name • Primary key not always required syntactically but needed to establish relationships • SQL verifies • syntax • availability of tablespace • uniqueness of name • creator has proper authorization

  8. VIEWS • Virtual Table - Logical representation of another table or combination of tables. • CREATE VIEW viewname AS (SELECT ...) • Usage • provide security • hide data complexity • reduce syntactic complexity • present different perspective

  9. Operations on Views • Query • Standard select statement • Insert • Delete • SOME views may be used for updating • single table with view containing primary key • all not null fields included in view • views created from joining multiple tables usually do not permit updates

  10. SQL DDL • CREATE SEQUENCE • sequential values • CREATE SEQUENCE seqname INCREMENT BY 1 START WITH 1000 • INSERT INTO tablename VALUES (..., seqname.NEXTVAL,...) • DROP SEQUENCE, DROP INDEX, DROP VIEW

  11. Index • performance enhancer • CREATE INDEX indexnameON tablename (column) • Single or multiple indexes on a table through the selection of one or many columns containing unique data values. • Concatenation of columns to assure uniqueness

  12. Index con’t • Indexes automatically maintained and used by system • Trade off - access time vs update time • Unique index • CREATE UNIQUE INDEX indexnameON tablename(attribute)

More Related