Constraints In Sql

50 %
50 %
Information about Constraints In Sql
Education

Published on January 8, 2009

Author: anurag2k4

Source: slideshare.net

Constraints In SQL Presented By Priyanka Kumari

Topics to be Covered 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

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

What is a Constraint? 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.

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.

The constraints can either 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.

The constraints can either 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.

Types Of Constraints Not 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));

Not 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));

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));

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));

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);

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);

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));

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));

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));

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));

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.

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.

Foreign Key as a 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);

Foreign Key as a 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);

Defining Constraint in the 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);

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);

THANK YOU

THANK YOU

Add a comment

Related presentations

Related pages

SQL Constraints - W3Schools Online Web Tutorials

SQL Constraints. SQL constraints are used to specify rules for the data in a table. If there is any violation between the constraint and the data action ...
Read more

Constraints

Constraints can be column constraints or table constraints. A column constraint is specified as part of a column definition and applies only to that column.
Read more

Constraint – Wikipedia

Constraints in der Logistik. Ein Constraint wird auch in diesem Fall als Einschränkung für eine durchgeführte Aktion verwendet. Es wird nach ...
Read more

Datenbanken / CONSTRAINT | Datenbanken Online Lexikon

Mit Constraints (deutsch „Zwangsbedingungen“) werden in diversen Programmiersprachen bzw. Datenbanken Bedingungen definiert, die zwingend vom Wert ...
Read more

SQL Constraints - Tutorials for Clojure, Aurelia ...

SQL Constraints - Learn SQL (Structured Programming Language) in simple and easy steps starting from basics to advanced concepts. This tutorial would give ...
Read more

Oracle SQL Tutorial: Constraints

Definition von Integritätsbedingungen zur Begrenzung des Wertebereiches einzelner oder mehrerer Attribute. Constraints sind Tabellen und Spalten zuzuordnen.
Read more

table_constraint (Transact-SQL)

CONSTRAINT . Gibt den Anfang einer PRIMARY KEY-, UNIQUE-, FOREIGN KEY- oder CHECK-Einschränkung oder einer DEFAULT-Definition an. constraint_name
Read more

SQL CHECK Constraint - W3Schools Online Web Tutorials

SQL CHECK Constraint. The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a single ...
Read more

CHECK_CONSTRAINTS (Transact-SQL)

Gibt eine Zeile für jede CHECK-Einschränkung in der aktuellen Datenbank zurück. Diese Informationsschemasicht gibt Informationen zu den Objekten zurück ...
Read more

constraint - Oracle Help Center

constraint. Purpose. Use a constraint to define an integrity constraint--a rule that restricts the values in a database. Oracle Database lets you create ...
Read more