Answer:
Integrity constraints ensure that changes made to the database by authorized users do not result in a loss of data consistency. Thus, integrity constraints guard against accidental damage to the database.
Besides the cell name, cell length and cell data type, there are other parameters i.e. other data constraints that can be passed to the DBA at cell creation time.
These data constraints will be connected to a cell by the DBA as flags. Whenever a user attempts to load a cell with data, the DBA will check the data being loaded into the cell against the data constraints defined at the time the cell was created. If the data being loaded fails any of the data constraint checks fired by the DBA, the DBA will not load the data into the cell, reject the entered record, and will flash an error message to the user.
These constraints are given a constraint name and the DBA stores the constraints with its name and instructions internally along with the cell itself.
The constraint can either be placed at the column level or at the table level.
Column Level Constraints: If the constraints are defined along with the column definition, it is called as a column level constraint. Column level constraint can be applied to anyone column at a time i.e. they are local to a specific column. If the constraint spans across multiple columns, the user will have to use table level constraints.
Table Level Constraints: If the data constraint attached to a specific cell in a table references the contents of another cell in the table then the user will have to use table level constraints. Table level constraints are stored as a part of the global table definition.
NULL Value Concepts
While creating tables, if a row lacks a data value for a particular column, that value is said to be null. Columns of any data types may contain null values unless the column was defined as not null when the table was created.
Principles of NULL Values
- Setting a null value is appropriate when the actual value is unknown, or when a value would not be meaningful.
- A null value is not equivalent to a value of zero.
- A null value will evaluate to null in any expression. e.g. null multiplied by 10 is null.
- When a column name is defined as not null, then that column becomes a mandatory column. It implies that the user is forced to enter data into that column.
Example: Create table client master with a not null constraint on columns client no, Name, address, address2.
NOT NULL as a column constraint:
CREATE TABLE client master
(client_no varchar2(6) NOT NULL,
name varchar2(20) NOT NULL,
address 1 varchar2(30) NOT NULL,
address2 varchar2(30) NOT NULL,
city varchar2(15), state varchar2(15), pin code number( 6),
remarks varchar2(60), bal_due number (10,2));
Primary Key Concepts
A primary key is one or more columns in a table used to uniquely identify each row in the table. Primary key values must not be null and must be unique across the column.
A multicolumn primary key is called a composite primary key. The only function that a primary key performs is to uniquely identify a row and thus if one column is used it is just as good as if multiple columns are used. Multiple columns i.e. (composite keys) are used only when the system designed requires a primary key that cannot be contained in a single column.
Example: Primary Key as a Column Constraint:
Create client_master where client_no is the primary key.
CREATE TABLE client master (client_no varchar2(6) PRIMARY KEY,
name varchar2(20), add}-essl varchar2(30), address2 varchar2(30),
city varcbar2(15), state varchar2(15), pincode number(6),
remarks varchar2(60), bal_due number (10,2));
Primary Key as a Table Constraint:
Create a sales order details table where
| Column Name Data | Type Size Attributes | ||
| S_order_no | varchar2 | 6 | Primary Key |
| product_no | varchar2 | 6 | Primary Key |
| qty_ordered | Number | 8 | |
| qty-disp | Number | 8 | |
| product_rate | Number | 8,2 |
CREATE TABLE sales order details
(s_order_no varchar2(6), product_no varchar2(6),
qty _ordered number(8), qty – disp number(8),
product_rate number(8,2),
PRIMARY KEY (s_order_no, product_no));
Unique Key Concepts
A unique key is similar to a primary key, except that the purpose of a unique key is to ensure that information in the column for each record is unique, as with telephone or driver’s license numbers. A table may have many unique keys.
Example: Create Table client_master with unique constraint on column client_no
UNIQUE as a Column Constraint:
CREATE TABLE client master
(client_no varchar2( 6) CONSTRAINT cnmn – ukey UNIQUE,
name varchar2(20), address 1 varchar2(30), address2 varchar2(30),
city varchar2(15), state varchar2(l5), pincode number(6),
remarks varch_2(60), bal_due number(lO,2), partpaY311 char(l));
UNIQUE as a Table Constraint:
CREATE TABLE client master
(client_no varchar2(6), name varchar2(20),
addressl varchar2(30), address2 varchar2(30),
city varchar2(15), state varchar2(15), pincode number(6),
remarks varchar2(60), bal_due number(lO,2),
CONSTRAINT cnmn_ukey UNIQUE (client_no));
Default Value Concepts
At the time of cell creation a ‘default value’ can be assigned to it. When the user is loading a ‘record’ with values and leaves this cell empty, the DBA will automatically load this cell with the default value specified – The data type of the default value should match the data type of the column. You can use the default clause to specify any default value you want.
CREATE TABLE sales_order
(s_order_no varchar2(6) PRIMARY KEY,
s _order_date date, client_no varchar2(6),
dely _Addr varchar2(25), salesman _no varchar2(6),
dely_type char(l) DEFAULT ‘F’,
billed_yn char( l), dely_date date,
order_status varchar2(1 0))
Create sales_order table where:
| Column Name | Data Type | Size | Attribute |
| S_order no | varchar2 | 6 | Primary key |
| S_order date | Date | ||
| Client_no | varchar2 | 6 | |
| Dely_Addr | varchar2 | 25 | |
| Salesman_no | varchar2 | 6 | |
| Dely_type | char | I | Delivery: part (P)/Full (F) Default ‘F’ |
| Billed_yn | Char | I | |
| Dely_date | Date | ||
| Order_status | varchar2 | 10 |
Foreign Key Concepts
Foreign keys represent relationships between tables. A foreign key is a Column (or a group of columns) whose values-are derived from the primary key of the same or some other table.
The existence of a foreign key implies that the table with the foreign key is related to the – primary key table from which the foreign-key is derived. A foreign key must have a corresponding primary key value in the primary key table to have a meaning.
Example: The s_order_no column is the primary key of table sales_order. In table sales_order details, s _order_no is a foreign key that references the s_order_.no values in table sales order.
The Foreign Key References constraint are as follows:
- Rejects an INSERT or UPDATE of a value, if a corresponding value does not currently exist in the primary key table
- Rejects a DEL_TE, if it would invalidate a REFERENCES constrain
- Must reference a PRIMARY KEY or UNIQUE column(s) in primary key table
- Will reference the PRIMARY KEY of the primary key table if no column or group of columns is specified in the constraint
- Must reference a table, not a view or cluster;
- Requires that you own the primary key table, have REFERENCE privilege on it, or have column-level REFERENCE privilege on the referenced colwnns in the primary key table;
- Doesn’t restrict how other constraints may reference the same tables;
- Requires that the FOREIGN KEY column(s) and the CONSTRAINT column(s) have matching data types;
- May reference the same table named in the CREATE TABLE statement;
- Must not reference the same column more than once (in a single constraint).
Example: Create table sales_order _details with primary key as s_order_no and product_no and foreign key as s_order_no referencing column s_order_no in the sales order table.
FOREIGN KEY as a Column Constraint:
CREATE TABLE sales order details
( s_order_no varchar2(6) REFERENCES sales_order,
product_no varchar2(6),
qty _ordered number(8), qty – disp number(8), product_rate number(8,2),
PRIMARY KEY (s_order_no, product_no));
FOREIGN KEY as a Table Constraint:
CREATE TABLE sales order details
( s _order_no varchar2( 6),
product_no varchar2(6),
qty_ordered number(8), qty_disp number(8),
product_rate number(8,2),
PRIMARY KEY (s_order_no, product_no),
FOREIGN KEY (s_order_no) REFERENCES sales_order);
CHECK Integrity Constraints
Use the CHECK constraint when you need to enforce integrity rules that can be evaluated based on a logical expression. Never use CHECK constraints if the constraint can be defined using the not null, primary key or foreign key constraint.
Following are a few examples of appropriate CHECK constraints:
- a CHECK constraint on the client no column of the client master so that no client no value starts with ‘C’.
- a CHECK constant on name column of the client master so that the name is entered in upper case.
- a CHECK constraint on the city column of the client_master so that only the cities “BOMBAY”, “NEW DELHl “, “MAPRAS” and “CALCUTTA” are allowed.
CREATE TABLE client master
(client_no varchar2(6) CONSTRAINT ck_clientno
CHECK ( client_no like ‘C%’),
name varchar2(20) CONSTRAINT ck_cname
CHECK (name = upper(name»,
address I varchar2(30), address2 varchar2(30),
city varchar2( 15) CONSTRAINT ck _city
CHECK (city IN CNEWDELHI’, ‘BOMBAY’, ‘CALCUTTA’, ‘MADRAS’)),
state varchar2(l5), pin code number(6),
remarks varchar2(60), bal- due number(10,2));
When using CHECK constraints, consider the ANSI I ISO standard which states that a CHECK constraint is violated only if the condition evaluates to False, True and unknown values do not violate a check condition. Therefore, make sure that a CHECK constraint that you define actually enforces the rule you need to enforce.
Example: Consider the following CHECK constraint for emp table: CHECK ( sal > 0 or comm >= 0 )
At first glance, this rule may be interpreted as “do not allow a row in emp table unless the employee’s salary is greater than 0 or the employee’s commission is greater than or equal to “0”.
