Data integrity is about enforcing data validation rules such as checking that a percentage amount is between 0 and 100 and to ensure that invalid data does not get into your tables. Historically, these rules were enforced by the application programs themselves and the same rules were checked repeatedly in different programs. Oracle, however, enables you to define and store these rules against the database objects to which they relate so that you need to code them only once so they are enforced whenever any kind of change is made to the table, regardless of the insert, update, or delete statement. Integrity constraints enforce business rules at the database level by defining a set of checks for the tables in your system. These checks are automatically enforced whenever you issue an insert, update, or delete statement against the table. If any of the constraints are violated, the insert, update, or delete statement is rolled back. The other statements within the transaction remain in a pending state and can be committed or rolled back according to application logic. Because integrity constraints are checked at the database level, they are performed regardless of where the insert, update, delete statement originated whether it was an Oracle or a non-Oracle tool. Defining checks using these constraints is also quicker than performing the same checks using SQL. In addition, the information provided by declaring constraints is used by the Oracle optimizer to make better decisions about how to run a statement against the table. The Oracle Forms product can also use constraints to automatically generate code in the front-end programs to provide an early warning to the user of any errors. The types of integrity constraints that you can set up on a table are NOT NULL, PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, and indexes. NOT NULL You set the NOT NULL constraint against a column to specify that the column must always have a value on every row it can never be null. By default, all the columns in a table are null. For example, using a NOT NULL constraint on an orders table, you can specify that there must always be an order amount. Syntax Code: CREATE TABLE table_name (column1 datatype not null, column2 datatype null, ... ); Each column must have a datatype. The column should either be defined as "null" or "not null" and if this value is left blank, the database assumes "null" as the default. PRIMARY KEY The PRIMARY KEY constraint defines a column or a set of columns that you can use to uniquely identify a single row. No two rows in the table can have the same values for the primary key columns. In addition, the columns for a primary key constraint must always have a value in other words, they are NOT NULL. If you add a constraint to a table after it has been created, any columns that make up the PRIMARY KEY constraint are modified to NOT NULL. Only one PRIMARY KEY constraint can exist for any table. For example, using a PRIMARY KEY constraint on an orders table, you can specify that a table cannot have two records with the same order number. Syntax Code: CREATE TABLE table_name (column1 datatype PRIMARY KEY, column2 datatype null/not null, ... ); UNIQUE The UNIQUE constraint defines a secondary key for the table. This is a column or set of columns that you can use as another way of uniquely identifying a row. No two rows can have the same values for the UNIQUE key column or columns. Although it is not possible for a table to have more than one primary key, a table can have more than one UNIQUE constraint. The columns for a UNIQUE constraint do not have to be identified as NOT NULL . If the values for any of the columns that form the unique constraint are null, the constraint is not checked. For example, using a PRIMARY KEY and UNIQUE constraint on a customers table, you can specify that the customer number is a primary key and that the customer name is a unique key (which would mean that you could not have two customers with the same name on your table). The UNIQUE constraint is not checked if the values in the column are null. Syntax Code: CREATE TABLE table_name (column1 datatype null/not null, column2 datatype null/not null, ... CONSTRAINT constraint_name UNIQUE (column1, column2, . column_n) ); FOREIGN KEY The FOREIGN KEY or referential integrity constraint enforces relationship integrity between tables. It dictates that a column or set of columns on the table match a PRIMARY KEY or UNIQUE constraint on a different table. For example, you could set up a FOREIGN KEY constraint on the orders table to specify that whenever an order record is inserted or updated, the customer number must also exist in the customers table. This ensures that you don't get orders for nonexistent customers. You use FOREIGN KEY constraints to enforce parent/child relationships between tables. You can even use them to enforce self-referential constraints, usually in situations where a hierarchical structure is set up with all the rows held in the same table. If any of the columns of the foreign key are null, the constraint is not checked at all. Foreign key columns are usually declared as NOT NULL. The table containing foreign key is known as Dependent Table and the table that is referenced by foreign key is called Referenced table. Referenced table Syntax Code: CREATE TABLE table_name (column1 datatype PRIMARY KEY, column2 datatype null/not null, ... ); Dependent table Syntax Code: CREATE TABLE table_name (column1 datatype PRIMARY KEY, column2 datatype null/not null, column3 datatype References Referenced table name(column1 of referenced table), column4 datatype null/not null, ... ); CHECK A CHECK constraint specifies additional logic that must be true for the insert, update, or delete statement to work on the table. The additional logic returns a Boolean result, and in the check constraint, you ensure the values in the row being modified satisfy a set of validation checks that you specify. For example, using a CHECK constraint on the orders table, you can specify that the order amount must be greater than zero and the salesman's commission cannot be greater than 10 percent of the order total. Syntax Code: CREATE TABLE table_name (column1 datatype null/not null, column2 datatype null/not null, ... CONSTRAINT constraint_name CHECK (column_name condition); PRIMARY KEY and UNIQUE constraints automatically create an index on the columns they're defined against if the constraint is enabled upon creation. If an index already exists on the columns that form the PRIMARY KEY or UNIQUE constraint, that index is used, and Oracle cannot create a new one. Oracle creates indexes when the constraint is enabled (which is the default when the constraint is first added to the table). Oracle drops the indexes from the table when the constraint is disabled. Enabling and disabling constraints can take significant time and system overhead due to the index creation and removal. When you set up a FOREIGN KEY constraint, the columns are not indexed automatically. Because the foreign key columns are usually involved in joining tables together, you manually create indexes on those columns. Disabling a PRIMARY KEY or UNIQUE constraint drops the index for the constraint.
I am taking an Oracle class. One of the assignments is this: Write an ALTER TABLE statement that adds two new check constraints to the Invoices table of the AP schema. The first should allow: (1) payment_date to be null only if payment_total is zero and (2) payment_date to be not null only if payment_total is greater than zero. The second constraint should prevent the sum of payment_total and credit_total from being greater than invoice_total. The problem is the book (Murach's Oracle SQl and PL/SQL) doesn't give any decent examples. Most of the examples are on how to set constraints on primary keys and foreign keys. Can someone give some examples of what the syntax would be for this type of a constraint?
i believe you have some examples above Parallax, take a closer look maybe you can squeez something out of them.
hm, i'm still interested whether someone is going to provide further examples? just needed them too so far.
