Database Integrity


When you execute a DML statement (an INSERT , UPDATE , or DELETE , for example), the database ensures that the rows in the tables maintain their integrity. This means that any changes you make to the rows in the tables must always be in keeping with the primary key and foreign key relationships set for the tables.

Enforcement of Primary Key Constraints

Let s examine some examples that show the enforcement of a primary key constraint. The customers table s primary key is the customer_id column, which means that every value stored in the customer_id column must be unique. If you try to insert a row with a duplicate value for a primary key column, the database returns the error ORA-00001 , for example:

 SQL>  INSERT INTO customers (  2  customer_id, first_name, last_name, dob, phone  3  ) VALUES (  4  1, 'Jason', 'Price', '01-JAN-60', '800-555-1211'  5  );  INSERT INTO customers (* ERROR at line 1: ORA-00001: unique constraint (STORE.CUSTOMERS_PK) violated 

If you attempt to update a primary key value to a value that already exists in the table, the database returns the same error:

 SQL>  UPDATE customers  2  SET customer_id = 1  3  WHERE customer_id = 2;  UPDATE customers * ERROR at line 1: ORA-00001: unique constraint (STORE.CUSTOMERS_PK) violated 

Enforcement of Foreign Key Constraints

A foreign key relationship is where a column from one table is referenced in another. The product _ type_id column in the products table references the product_type_id column in the product_types table using a foreign key relationship. The product_types table is known as the parent table, and the products table is known as the child table because the product_type_id column in the products table is dependent on the product_type_id column in the product_types table.

If you try to insert a row into the products table with a nonexistent product_type_id , the database will return the error ORA-02291 . This error indicates the database couldn t find a matching parent key value (the parent key is the product_type_id column of the product_types table). For example:

 SQL>  INSERT INTO products (  2  product_id, product_type_id, name, description, price  3  ) VALUES (  4  13, 6, 'Test', 'Test', NULL  5  );  INSERT INTO products (* ERROR at line 1: ORA-02291: integrity constraint (STORE.PRODUCTS_FK_PRODUCT_TYPES)  violated - parent key not found 

Similarly, if you attempt to set the product_type_id of a row in the products table to a nonexistent parent key value, the database returns the same error. For example:

 SQL>  UPDATE products  2  SET product_type_id = 6  3  WHERE product_id = 1;  UPDATE products * ERROR at line 1: ORA-02291: integrity constraint (STORE.PRODUCTS_FK_PRODUCT_TYPES)  violated - parent key not found 

If you attempt to delete a row in the parent table that already had dependent child rows, the database returns error ORA-02292 , which means a child record was found. For example, if you attempt to delete the row with a product_type_id of 1 in the product_types table, the database will return error ORA-02292 because the products table contains rows with product _type_id values equal to that value:

 SQL>  DELETE FROM product_types  2  WHERE product_type_id = 1;  DELETE FROM product_types * ERROR at line 1: ORA-02292: integrity constraint (STORE.PRODUCTS_FK_PRODUCT_TYPES)  violated - child record found 

If the database were to allow this deletion, the child rows would be invalid because they wouldn t point to valid values in the parent table.




Oracle Database 10g SQL
Oracle Database 10g SQL (Osborne ORACLE Press Series)
ISBN: 0072229810
EAN: 2147483647
Year: 2004
Pages: 217

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net