DATABASE CONSTRAINTS

THIS PRESENTATION CONTAIONS VARIOUS CONSTRAINTS USED IN DATABSE SUCH AS NOT NULL,UNIQUE,KEY CONSTRAINTS,DEFAULT ETC .

1. Database Constraints Constraints are used to specify rules for the data in a table . Constraints enforce limits to the data or type of data that can be inserted/updated/deleted from a table . The main purpose of constraints is to maintain the  data integrity  during an update/delete/insert into a table.

2. Types of constraints NOT NULL UNIQUE CHECK DEFAULT Key Constraints – PRIMARY KEY, FOREIGN KEY

3. Database Constraints NOT NULL NOT NULL constraint makes sure that a column does not hold NULL value. When we don’t provide value for a particular column while inserting a record into a table, it takes NULL value by default. By specifying NOT NULL constraint, we can be sure that a particular column(s) cannot have NULL values. Ex: CREATE TABLE STUDENT ( ROLL_NO INT NOT NULL , STU_NAME VARCHAR (35) NOT NULL , STU_AGE INT NOT NULL , STU_ADDRESS VARCHAR (235), PRIMARY KEY (ROLL_NO) );

4. Database Constraints UNIQUE: UNIQUE Constraint enforces a column or set of columns to have unique values. If a column has a UNIQUE constraint, it means that particular column cannot have duplicate values in a table. Ex: CREATE TABLE STUDENT ( ROLL_NO INT NOT NULL, STU_NAME VARCHAR (35) NOT NULL UNIQUE, STU_AGE INT NOT NULL, STU_ADDRESS VARCHAR (35) UNIQUE, PRIMARY KEY (ROLL_NO) );

5. Database Constraints CHECK CHECK constraint is used to restrict the value of a column between a range. It performs check on the values, before storing them into the database. Its like condition checking before saving data into a column. When this constraint is being set on a column, it ensures that the specified column must have the value falling in the specified range. Ex: create table Student ( S_id INT NOT NULL CHECK( S_id > 100), Name VARCHAR(60) NOT NULL, Age INT );

6. Database Constraints DEFAULT: The DEFAULT constraint provides a default value to a column when there is no value provided while inserting a record into a table. Ex: CREATE TABLE STUDENT ( ROLL_NO   INT  NOT NULL, STU_NAME VARCHAR (35) NOT NULL, STU_AGE INT NOT NULL, EXAM_FEE INT  DEFAULT 10000, STU_ADDRESS VARCHAR (35) , PRIMARY KEY (ROLL_NO) ); we have set the default value of EXAM_FEE column to 10000 using DEFAULT constraint.

7. Database Constraints Key constraints PRIMARY KEY: Primary key uniquely identifies each record in a table. It must have unique values and cannot contain nulls. In the below example the ROLL_NO field is marked as primary key, that means the ROLL_NO field cannot have duplicate and null values. ex: CREATE TABLE STUDENT ( ROLL_NO   INT  NOT NULL, STU_NAME VARCHAR (35)  NOT NULL UNIQUE, STU_AGE INT NOT NULL, STU_ADDRESS VARCHAR (35) UNIQUE, PRIMARY KEY (ROLL_NO) );

8. Database Constraints FOREIGN KEY Foreign keys are the columns of a table that points to the primary key of another table. FOREIGN KEY is used to relate two tables. FOREIGN KEY constraint is also used to restrict actions that would destroy link between table. Ex: CREATE table Order_Detail ( order_id int PRIMARY KEY, order_name varchar(60) NOT NULL, c_id int FOREIGN KEY REFERENCES Customer_Detail(c_id) ); In  Customer_Detail  table, c_id is the primary key which is set as foreign key in  Order_Detail  table. The value that is entered in c_id which is set as foreign key in  Order_Detail  table must be present in  Customer_Detail  table where it is set as primary key. This prevents invalid data to be inserted into c_id column of  Order_Detail table.

9. THANK YOU