CONSTRAINTS

Chapter 9: MySQL – Advanced  MySQL provides feature to maintain the validity and correctness of data being entered in the database using some predefined or user defined constraints. Later these constrains can be modified, if required.  Some time, it is required to make a query which retrieves records to form a group reports based on the given criteria and column.  You can also join two or more tables in a SELECT query using some joining criteria. In this chapter you will learn all these issues. Integrity Constraints: One of the major responsibility of a DBMS is to maintain the Integrity of the data i.e. Data being stored in the Database must be correct and valid.  An Integrity Constraints or Constraints are the rules, condition or checks applicable to a column or table which ensures the integrity or validity of data.  Most of the constraints are applied along with Column definition which are called Column-Level (inline Constraints),but some of them may be applied at column Level as well as Table-Level (Out-line constraints) i.e. after defining all the columns with CREATE TABLE command. Ex.- Primary Key & Foreign Key The following constraints are commonly used in MySQL. Constrains Role NOT NULL Ensures that a column cannot have NULL value. DEFAULT Provides a default value for a column, when nothing is given with INSERT command. UNIQUE Ensures that all values in a column are different. CHECK Ensures that all values in a column satisfy certain user defined condition. PRIMARY KEY Used to identify a row uniquely. FOREIGN KEY Used to ensure Referential Integrity of the data. UNIQUE v/s PRIMARY KEY  UNIQUE allows NULL values but PRIMERY KEY does not.  Multiple columns may have UNIQUE constraints, but there is only one PRIMERY KEY constraints in a table.  Implementing Primary Key Constraints:  Defining Primary Key at Column Level: mysql> CREATE TABLE Student ( StCode char(3) NOT NULL PRIMARY KEY, Stname char(20) NOT NULL, ………………………..);  Defining Primary Key at Table Level: mysql> CREATE TABLE Student ( StCode char(3) NOT NULL, Stname char(20) NOT NULL, ……………………….. PRIMARY KEY (StCode)); A Composite (multi-column) Primary key can be defined as only a Table level whereas Single-column Primary key can be defined in both way i.e. Column level or Table level. Example: mysql> CREATE TABLE Student (StCode char(3) NOT NULL PRIMARY KEY, Stname char(20) NOT NULL, StAdd varchar(40), AdmNo char(5) UNIQUE, StSex char(1) DEFAULT ‘M’, StAge integer CHECK (StAge>=5) ); :: 74 ::  Implementing Foreign Key Constraints:  A Foreign key is non-key column in a table whose value is derived from the Primary key of some other table.  Each time when record is inserted or updated in the table, the other table is referenced. This constraint is also called Referential Integrity Constraints.  This constraint requires two tables in which Reference table (having Primary key) called Parent table and table having Foreign key is called Child table. Let us take two table Employee and Department as per given columnsThe following command can be given to implement Foreign key constraintModifying Constraints: Some time it is required to modify the defined constraints after creating a table. ALTER command can be used to modify (adding/deleting of columns) Table structure as well as modifying constraints.  Adding new column and Constraints ALTER TABLE ADD[][] Example: mysql> ALTER TABLE Student ADD (TelNo Integer); mysql> ALTER TABLE Student ADD (Age Integer CHECK (Age>=5)); mysql> ALTER TABLE Emp ADD Sal Number(8,2) DEFAULT 5000 ; mysql> ALTER TABLE Emp ADD PRIMARY KEY (EmpID); mysql> ALTER TABLE Emp ADD PRIMARY KEY (Name,DOB);  Modifying Existing Column and Constraints ALTER TABLE
MODIFY[] [] Example: mysql> ALTER TABLE Student MODIFY Name VARCHAR(40); mysql> ALTER TABLE Emp MODIFY (Sal DEFAULT 4000 ); mysql> ALTER TABLE Emp MODIFY (EmpName NOT NULL);  Removing Column & Constraints ALTER TABLE
DROP | Example: mysql> ALTER TABLE Student DROP TelNo; mysql> ALTER TABLE Emp DROP JOB, DROP Pay; mysql> ALTER TABLE Student DROP PRIMARY KEY; :: 75 ::  Changing Column Name of Existing Column ALTER TABLE
CHANGE Example: mysql> ALTER TABLE Student CHANGE Name Stname Char(40);  To View the Constraints The following command will show all the details like columns definitions and constraints of EMP table. mysql> SHOW CREATE TABLE EMP; Alternatively you can use DESCribe command: mysql> DESC EMP;  Enabling / Disabling Foreign Key Constraint  You may enable or disable Foreign key constraints by setting the value of FOREIGN_KEY_CHECKS variable.  You can’t disable Primary key, however it can be dropped (deleted) by Alter Table… command.  To Disabling Foreign Key Constraint mysql> SET FOREIGN_KEY_CHECKS = 0;  To Enable Foreign Key Constraint mysql> SET FOREIGN_KEY_CHECKS = 1;