Constraints In Sql

Constraints In Sql

• 1.
  Constraints In SQLPresented By Priyanka Kumari
• 2.
  Topics to beCovered What is a constraint? Column Level Constraints Table Level Constraints Not Null Constraint Unique Key Constraint Default Constraint Check Constraint Primary Key Constraint Foreign Key Constraint Defining Constraint in Alter Table Command
• 3.
  What is aConstraint? Integrity Constraints are the rules in real life which are to be imposed on the data. Besides the cell name, cell length and the cell data type, there are other parameters that is other data constraints that can be passed to the DBA at cell creation time. These data constraints are connected to a cell by the DBA as a flag. Whenever a user attempts to load a cell with data, DBA will check the data being loaded into the cell against the data constraint defined at the cell creation time. If the data being loaded fails to satisfy 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.
• 4.
  The constraints caneither be placed at the column level or at the table level. Column Level Constraint- These constraints are defined along with the column definition. These constraints can be applied to any one column at a time. If the constraints spans across multiple columns ,then the table level constraints are used. Table Level Constraints- If the data constraint attached to a specific cell in a table references the content of another cell in the table then the table level constraint is used.
• 5.
  Types Of ConstraintsNot Null Constraint – When a column name is defined as not null, then that column becomes a mandatory column. It implies that the user is enforced to enter data into that column. Principles of Null Values : 1.Setting the null value is appropriate when the actual value is unknown or a value would not be meaningful. 2.A null value would not be equivalent to a value of zero. 3.A null value would evaluate to null in any expression. Ex-Null multiplied by 10 is null. CREATE TABLE student (rollNo varchar2(4) NOT NULL, name varchar2(20) NOT NULL, address varchar2(30) , marks number(5,2));
• 6.
  Unique Key Constraint – The purpose of a unique key is to ensure that information in the column for each record is unique. Unique Key as a column constraint : CREATE TABLE student (rollNo varchar2(4) UNIQUE , name varchar2(20) ,address varchar2(30) , marks number(5,2)); Unique Key as a table constraint : CREATE TABLE student (rollNo varchar2(4) ,name varchar2(20) , address varchar2(30) ,marks number(5,2) CONSTRAINT roll_key UNIQUE(rollNo));
• 7.
  Default Key Constraint – 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. CREATE TABLE student (rollNo varchar2(4) ,name varchar2(20) , address varchar2(30) , marks number(5,2) DEFAULT 0);
• 8.
  Check Constraint – It is used when we need to enforce integrity rules that can be evaluated based on a logical expression. CREATE TABLE student (rollNo varchar2(4) CHECK(rollNo like ‘C%’), name varchar2(20) CONSTRAINT chk_nm CHECK(name = upper(name)), address varchar2(30) , marks number(5,2) CHECK(marks > 40));
• 9.
  Primary Key Constraint – It is used to uniquely identify each row in the table. Primary key values must not be null and must be unique across the column. Primary Key as a column constraint – CREATE TABLE student (rollNo varchar2(4) PRIMARY KEY, name varchar2(20) , address varchar2(30) , marks number(5,2)); Primary Key as a table constraint – CREATE TABLE student (rollNo varchar2(4) ,name varchar2(20) , address varchar2(30) ,marks number(5,2) PRIMARY KEY(rollNo));
• 10.
  Foreign Key Constraint – 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 correponding primary key value in the primary key table to have a meaning. Foreign Key/References constraint : 1.Rejects an INSERT or UPDATE of a value if a corresponding value does not currently exist in the primary key table. 2.Rejects a DELETE,if it would invalidate a REFERENCES constraint. 3.Must reference a PRIMARY KEY or UNIQUE column in primary key table. 4.Will reference the PRIMARY KEY of the primary key table if no column or group of columns is specified in the constraints. 5.Must refer a table , not a view. 6.Requires that a FOREIGN KEY column and the CONSTRAINT column have matching data types.
• 11.
  Foreign Key asa column constraint- CREATE TABLE report (slNo number(2) PRIMARY KEY, roll varchar2(4) REFERENCES student(rollNo), grade char(1)); Foreign Key as a table constraint- CREATE TABLE report (slNo number(2) PRIMARY KEY, roll varchar2(4) ,grade char(1), FOREIGN KEY(roll) REFERENCES student(rollNo);
• 12.
  Defining Constraint inthe Alter table Add PRIMARY KEY ALTER TABLE student ADD PRIMARY KEY(rollNo); Add FOREIGN KEY ALTER TABLE report ADD CONSTRAINT rk REFERENCES student(roll); Add NOT NULL ALTER TABLE student MODIFY(name varchar2(20) NOT NULL);
• 13.
  THANK YOU