find and solve || findandsolve.com
Please wait.....

SQL Check Constraint Multiple Columns,integrity constraints in sql



All businesses of the world run on business data being gathered, stored and analyzed. Business managers determine a set of business rules that must be applied to their data prior to it being stored in the database/ table to ensure its integrity.
For instance, no employee in the sales department can have a salary of less than Rs. 1000/-
Such rules have to be enforced on data stored. Only data, which satisfies the conditions set, should be stored for future analysis. If the data gathered fails to satisfy the conditions set, it must be rejected. This ensures that the data stored in a table will be vaild, and have integrity.

Business rules that are applied to data are completely System dependent. The rules applied to data gathered and processed by a Saving bank system will be very different, to the business rules applied to data gathered and processed by Inventory system, which in turn will be very different, to the business rules applied to data gathered and processed by a Personnel management system

Business rules, which are enforced on data being stored in a table, are called Constraints. Constraints, Super control the data being entered into a table for permanent storage.
To understand the concept of data constraints, several tables will be created and different types of constraints will be applied to table columns or the table itself. The set of table are described below, Appropriate examples of data constraints are bound to these tables.

Applying Data Constraints

Oracle permits data constraints to be attached to table columns via SQL syntax that checks data for integrity prior storage. Once data constraints are part of a table column construct, the Oracle database engine checks the data being entered into a table column against the data constraints. If the data passes this check, it is stored in the table column, else the data is rejected. Even if a single column of the record being entered into the table fails a constraint, the entire record is rejected and not stored in the table. 
Both the Create Table an Alter Table SQL verbs can be used to write SQL sentences that attach constraints (i.e. Business / System rules) to a table column.


Caution :-  Until now tables created in this material have not had any data constraints attached to their table columns. Hence the tables have not been given any instructions to filter what is being stored in the table. This situation can and does, result in erroneous data being stored in the table.
Once a constraint is attached to a table column, any SQL INSERT or UPDATE statement automatically causes these constraints to be applied to data prior it is being inserted into the table column for storage.

Note:- Oracle also permits applying data constraints at Table Level. More on table level constraints later in this material.

TYPES OF DATA CONSTRAINTS

There are two types of data constraints that can be applied to data being inserted into a Oracle table. One type of constraint is called an I/O constraint (input / output). This data constraint determines the speed at which data can be inserted or extracted from a Oracle table. The other type of constraint is called a business rule constraint.

I/O Constraints
The input/output data constraints are further divide into two distinctly different constraints.


The PRIMARY KEY Constraint

A primary key is one or more column (s) in a table used to uniquely identify each row in the table. None of the fields that are part of the primary key can contain a null value. A table can have only one primary key. A Primary key column in a table has special attributes:
⦁ It defines the column, as a mandatory column (i.e. the column cannot be left blank). As the NOT NULL attribute is active
⦁ The data held across the column MUST be UNIQUE

A single column primary key is called a Simple key. A multi column primary key is called a Composite primary key. The only function of a primary key in a table is to uniquely identify a row. When a record cannot be uniquely identified using a value in a simple key, a composite key must be defined. A primary key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.
For Example, a SALES_ORDER_DETAILS table will hold multiple records that are sales orders. Each such sales order will have multiple products that have been ordered. Standard business rules do not allow multiple entries for the same product. However,multiple  orders will definitely have multiple entries of the same product.
Under these circumstances, the only way to uniquely identify a row in the SALES_ORDER_DETAILS table is via a composite primary key, consisting of ORDER_NO and PRODUCT_NO. Thus the combination of order number and product number will uniquely identify a row.

Features of Primary key
1. Primary key is a column or a set columns that uniquely identifies a row. Its main purpose is the Record Uniqueness
2. Primary key will not allow delicate values
3. Primary key will also not allow null values
4. Primary key is not compulsory but it is recommended
5. Primary key helps to identify one record from another record and also helps in relating tables with one another
6. Primary key cannot be LONG or LONG RAW data type
7. Only one Primary key is allowed per table
8. Unique Index is created automatically if there is a Primary key
9. One table can combine upto 16 columns in a Composite Primary key.



Sundar  Neupane

Sundar Neupane

I like working on projects with a team that cares about creating beautiful and usable interfaces.

Comments



Report Response