SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword SQL Order BY LIMIT SQL Order BY RANDOM

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL How index works in SQL How to calculate age from Date of Birth in SQL How to Rename Column name in SQL What are single row and multiple row subqueries?

SQL CONSTRAINTS
2018-10-28 10:38:52

SQL CONSTRAINTS

SQL Constraints specifies the rules/limitations/restrictions for data present in table. SQL Constraints are specified at the time of table creation or after table creation using ALTER command. There are two types of constraints column-level constraint and table-level constraint. As the name column level specifies, it specifies a particular column. In a table level constraint, it specifies more than one column or all columns in the table. NOT NULL: It ensures that specified column value cannot be NULL. UNIQUE: It ensures that each value in the column is different. PRIMARY KEY: Primary key ensures both NOT NULL and UNIQUE. It means value cannot be null and it should be different from each other. Therefore, primary key uniquely identifies each record in the table. FOREIGN KEY: It uniquely identifies record from other table. Foreign key is a reference to the other table and it is like Primary key from other table. DEFAULT: It sets default value when value is not given. CHECK: It ensures that all values in the column satisfy a condition. INDEX: Index is created and used to retrieve data from database.

NOT NULL

Default value for every column is NULL. Not Null constraints ensures column value should not null. This constraint always make sure column has some value. It does not allow inserting or updating record without mentioning value to this field. User can add this constraint to single or multiple or all columns of the table. Syntax: CREATE  TABLE table_name (column 1 data type NOT NULL,………); Example: Create table STUDENT (student_id int NOT NULL, student_name varchar(10), student_dept varchar(10) NOT NULL, classyear int); In the above example, NOT NULL constraint is applied on two columns student_id and student_dept in STUDENT table while creating table. Description of STUDENT table shows which columns can be NULL or cannot be NULL. See the output screen at the time of inserting values in table, not null constraint does not allow entering NULL value to those fields on which constraint is applied. UNIQUE Unique constraint avoid duplicate entries in column. One table can have multiple unique keys. Syntax: CREATE  TABLE table_name (column 1 data type UNIQUE,………); Example: create table student (student_id int Unique, student_name varchar(10) unique, student_dept varchar(10) not null, classyear int ); You can write above query in other form. Create table student (student_id int, student_name varchar(10) unique, student_dept varchar(10) not null, classyear int , unique (student_id)); # define multiple columns unique in one time Syntax: CREATE  TABLE table_name (column 1 data type, ……… column n, constraint constraint_name Unique (column1,..)); Example: create table student (student_id int, student_name varchar(10) unique, student_dept varchar(10) not null, classyear int , Constraint cs_student unique (student_id, student_name));   # Add UNIQUE Constraint Using ALTER Statement Syntax: Alter table table_name Add Unique (column_name); Example: Alter table Student Add Unique (student_id); Syntax: Alter table table_name Add Constraint constraint_name Unique (column1,column2); Example: Alter table Student Add Constraint cs_student Unique (student_id,student_name); Primary Key Primary key uniquely identifies each record in the column, one table have single Primary key. Primary key can be a single column or group of columns. Syntax: Create table table_name ( column data type Primary key, column2 data type,………); Example: Create table STUDENT (student_id int Primary Key, student_name varchar(20), student_dept varchar(20), classyear int); Syntax: Create table table_name ( column1 data type, column2 data type,……… column n data type, Primary key (column_name)); Example: Create table Student (student_id int, student_name varchar(10), student_dept varchar(10), classyear int, Primary key (student_id)); # Using ALTER Statement Syntax: alter table table_name add primary key (column_name);   Example: alter table student add primary key (student_id); Syntax: alter table table_name drop primary key; Example: alter table student drop primary key;

DEFAULT

The DEFAULT constraint is used to set the default value to the column. This constraint can be applied on single column or multiple columns. NULL is a default value to all the columns in table. When user apply not null or primary constraint on any column then its default value is changed. Syntax: Create table table_name ( Column data type,……. Column data type default value); Example : Create table student ( student_id int, student_name varchar(20), student_dept varchar(20) default 'IT', classyear int); Output screen shows default value ‘IT’ assigned to column student_dept. ‘IT’ value set to the column student_dept when no value is provided for the column student_dept.  # Adding DEFAULT constraint on column using ALTER Statement   Syntax : alter table table_name alter column_name set default value; Example : alter table student alter student_dept set default 'CS'; Syntax: alter table table_name alter column_name drop default; Example: alter table student alter student_dept drop default;   Foreign Key Foreign key is a link between two tables. Primary key of one table can be reference/foreign key of another table. The table, which contains foreign key, called as child table and other table whose primary key referred as reference that table be a parent table. User has to enter records in parent table before child table if there no data present in parent table. One table can have single primary key. DEPARTMENT Table
DEPT_ID DEPT_NAME DEPT_HEAD DEPT_LOC
0001 IT Mr. S.K. Mishra 06
0002 CS Mr. D.P. Patil 04
0003 EXTC Mrs. S.K. Bhosale 05
0004 MECH Mr. A.B. Jha 07
STUDENT Table
STUDENT_ID STUDENT_NAME DEPT_NAME CLASSYEAR
10001 RAM IT 02
10002 KIRAN IT 04
10003 NEHA CS 01
10004 ROOPA MECH 01
STUDENT Primary key   student_id Foreign key   dept_name DEPARTMENT Primary key   dept_name Here, DEPARTMENT is parent table and STUDENT is child table. Primary key of DEPARTMENT table referenced as foreign key in student table named as dept_name. It may be possible that two table can have column with same name. Suppose in student table dept_name is present with different name like student_dept or anyting it does not create any problem. Table may contain one or more foreign key. # Apply constraint while table creation Syntax: //  Parent table must contain a primary key Create table table_name (column data type Primary key, Column …….., Column n data type); //child table contain foreign key Create table table_name (column data type, …………, Foreign key(column_name) References parent_table_name(column_name of primary key)); Example: Create table department (dept_id int, dept_name varchar(30) Primary key, dept_head varchar(50), dept_loc int); Create table student (student_id int primary key, student_name varchar(30), dept_name varchar(30), classyear int, Foreign key (dept_name) References department (dept_name)); # Apply foreign key constraint using Alter statement Syntax: alter table table_name add foreign key (column_name) references parent_table_name (column_name of primary key); Example: alter table student add foreign key (dept_name) references department(dept_name); Syntax: alter table table_name add constraint constraint_name foreign key (column_name) references parent_table_name (column_name of primary key); Example: alter table student add constraint fk_student foreign key (dept_name) references department(dept_name); # DROP constraint Syntax: alter table table_name drop foreign key constraint_name; Example: alter table student drop foreign key fk_student; Create table student (student_id int, student_name varchar(50) not null, student_dept varchar(30) default 'IT',classyear int check (classyear<5 and classyear >0), primary key (student_id));