Class 12 Informatics Practices Revision Notes Chapter 12 Integrity Constraints and Tables

Class 12 Informatics Practices Quick Revision notes Chapter 12 Integrity Constraints and Tables

Constraints
Constraints let you define the way so that the database engine automatically enforces the integrity of a database. Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity. Using constraints is preferred to using DML Triggers, rules and defaults. The query optimiser also uses constraint definitions to build high performance query execution plans.
Classes of Constraints
SQL server supports the following classes of constraints:
1. NOT NULL Constraints
It specifies that the column does not accept NULL values. When this constraint is applied with any column, then at the time of entering a new record, that column cannot be left blank. This constraint ensures that null values are not permitted for the column, e.g. the following SQL creates a new table called CUSTOMERS and adds four columns, three of which, ID, NAME and AGE specify not to accept NULL.

e.g. CREATE TABLE CUSTOMERS ( ID INTEGER NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INTEGER NOT NULL, ADDRESS CHAR(25) ) ;

2. Default Constraints
It is used to insert a default value into a column. The default value will be added to all new records.

e.g. CREATE TABLE PERSON ( Person_ID NOT NULL; LastName VARCHAR(30) NOT NULL, FirstName VARCHAR(40) , Address VARCHAR(50) , City VARCHAR(10) DEFAULT ‘Delhi’ ) ;

3. UNIQUE Constraints
It enforces the uniqueness of the values in a set of columns. In a UNIQUE constraint, no two rows in the table can have the same value for the columns. Primary key also enforces uniqueness, but primary keys do not allow for NULL . While a column with unique constraint allows NULL values.

e.g. CREATE TABLE PARTS ( Pa rt_ID VARCHAR(5) UNIQUE, Part_Name VARCHAR(20) UNIQUE, Cost DECIMAL ) ;

4. PRIMARY KEY Constraints
It identifies the column or set of columns that have values that uniquely identify a row in a table. No two rows in a table can have the same primary key value. You cannot enter NULL for any column in a primary key. We recommend using a small, integer column as a primary key. Each table should have a primary key. A column or combination of columns that qualify as a primary key value is referred to as a candidate key. The following example creates the PART_SAMPLE table and specifies the part_nmbr field as the primary key

e.g. CREATE TABLE PART_SAMPLE ( part_nmbr INTEGER PRIMARY KEY, part_name CHAR(30), part_weight DECIMAL(16,2) , part_color CHAR(15) );

5. FOREIGN KEY Constraints
It identifies and enforces the relationships between tables. A foreign key in one table points to a candidate key in another table. In the following example, the order_part table establishes a foreign key that references the primary key of PART_SAMPLE table defined previously

CREATE TABLE ORDER_PART ( order_nmbr INTEGER, part_nmbr INTEGER FOREIGN KEY REFERENCES PART_SAMPLE(part_nmbr) ON DELETE NO ACTION, qty_ordered INTEGER ) ;

You cannot insert a row with a foreign key value, except NULL, if there is no candidate key with that value. The ON DELETE clause controls what actions are taken when you try to delete a row to which existing foreign key point. The ON DELETE clause has the following options:

1. NO ACTION Specifies that the deletion fails with an error.
2. CASCADE Specifies that all the rows with foreign key pointing to the deleted row are also deleted.
3. SET NULL Specifies that all rows with foreign keys pointing to the deleted row are set to NULL.
4. SET DEFAULT Specifies that all rows with foreign keys pointing to the deleted row are set to their default value.

The ON UPDATE clause defines the actions that are taken, if you try to update a candidate key value to which existing foreign keys point. This clause also supports the NO ACTION, CASCADE, SET NULL and SET DEFAULT options.
6. CHECK Constraints
It enforces domain integrity by limiting the values that can be put in a column. A CHECK constraint specifies a boolean (evaluates to TRUE, FALSE or unknown) search condition that is applied to all values that are entered in the column. All values that evaluate to FALSE are rejected. You can specify multiple CHECK constraints for each column. The following sample shows creating the constraint chk_id. This constraint additionally enforces the domain of the primary key by making sure that only numbers within a specified range are entered for the key.

CREATE TABLE CUST_SAMPLE ( cust_id INTEGER PRIMARY KEY, cust_name CHAR(50) , cust_address CHAR(50), cust_credit_limit INTEGER, CONSTRAINT chk_id CHECK(cust_id BETWEEN 0 AND 10000) );

Column and Table Constraints
It can be column constraints or table constraints. A column constraint is specified as part of a column definition and applies only to that column. A table constraint is declared independently from a column definition and can be applied to more than one column in a table. Table constraints must be used when more than one column must be included in a constraint, e.g. if a table has two or more columns in the primary key, you must use a table constraint to include both columns in the primary key.
Consider a table that records event occurring in a computer in a factory. Assume that events of several types can occur at the same time, but that no two events occurring at the same time can be of the same type. This can be enforced in the table by including both the event_type and event time columns in a two column primary key, as shown in the following example:

CREATE TABLE FACT0RY_PR0CESS ( event_type INTEGER, event_time DATETIME, event_site CHAR(50), event_desc CHAR(1024), CONSTRAINT event_key PRIMARY KEY (event_type, event_time) );

Integrity Constraints
Integrity constraints are used to ensure accuracy and consistency of data in a relational database. Infact, integrity constraints are the rules that a database must comply at all times. Integrity constraints determine what all changes are permissible to a database. There are many types of integrity constraints that play a role in maintaining the integrity of data.
Various types of integrity constraints are as follows:
1. Entity Integrity
The entity integrity constraint states that no primary key value can be null. This is because the primary key value is used to identify individual tuples in a relation. Having null value for the primary key implies that we cannot identify some tuples. This also specifies that there may not be any duplicate entry in primary key.
2. Referential Integrity
The referential integrity constraint is specified between two relations and is used to maintain the consistency among tuples in the two relations. Informally, the referential integrity constraint states that a tuple in one relation that refers to another relation must refer to an existing tuple in that relation. It is a rule that maintains consistency among the rows of the two relations.
3. Domain Integrity
The domain integrity constraint states that every element of a relation should respect the type and restrictions of its corresponding attribute. A type can have a variable length which needs to be respected. Restrictions could be the range of values that the element can have, the default value, if none is provided and if the element can be null.
4. User Defined Integrity
A business rule is a statement that defines or constraints some aspects of the business. It is intended to assert business structure or to control or influence the behaviour of the business.
Alter Table
SQL server supports the following Alter tables:
1. Adding a Unique Constraint The following example adds a unique constraint on the column column_name in the table whose name is table_name. The name of the constraint is constraint_name.

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column_name);

2. Adding a New Column To add a new column in a table, you can use the ALTER TABLE command as per following specification

ALTER TABLE <table_name> ADD [COLUMN] <column_name> <data_type> [NOT NULL] [<integrity constraint definition>];

3. Modifying and Changing a Column Using ALTER TABLE command, we can modify many properties of a given table. In order to modify a table, we have to use the ALTER TABLE command in following manner

ALTER TABLE <table_name> CHANGE [COLUMN] <old_column_name> <new_column_name> < column_definition> or ALTER TABLE <table_name>MODIFY [COLUMN] <column_name>< column_definition>

MODIFY and CHANGE both allow you to make changes in column definition, but there is a slight difference between the two: With MODIFY you cannot change the name of a column whereas with CHANGE, you can do so.
4. Renaming a Column The following example renaming the column name whose name is the column_name

ALTER TABLE <table_name>RENAME COLUMN column_name to new_column_name;

5. Adding Constraints To add constraints to your existing table, the ALTER TABLE command can be used in the following manner.

ALTER TABLE <table_name>ADD<Constraint-definition>;

6. Removing a Constraint Using table name constraint

ALTER TABLE <table_name>DROP CONSTRAINT <constraint_name>;

7. Deleting Columns To delete a column from a table, the ALTER TABLE command can be used in following ways

ALTER TABLE <table_name>DROP COLUMN <column_name>;

8. Enabling, Disabling Constraints The following example enable constraint from a table whose name is table_name

ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;

Integrity constraints can be disabled with the alter table command as shown below:

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

Viewing Constraints
To view all the information about how an existing table has been created include its constraints, you need to write following statement.

SHOW CREATE TABLE <table_name>:

DROP TABLE
The DROP TABLE statement is used to move a table or object table to the recycle bin or to remove the table and all its data from the database entirely. For an external table, this statement removes only the table’s metadata from the database. It has no effect on the actual data, which resides outside of the database. When you drop a table that is part of a cluster, the table is moved to the recycle bin. However, if you subsequently drop the cluster, the table is purged from the recycle bin and can no longer be recovered with a FLASHBACK TABLE operation.
Dropping a table invalidates dependent objects and removes object privileges on the table. If you want to recreate the table, then you must regrant object privileges on the table, recreate the indexes, integrity constraints and triggers for the table and specify its storage parameters. Truncating has none of these effects. Therefore, removing rows with the TRUNCATE statement can be more efficient than dropping and recreating a table.