SQL-Basics (3B)(Constraints)

                            SQL Constraints

                          -----------------------

• SQL constraints are used to specify rules for the data in a table.
• Constraints are used to limit the type of data that can go into a table.
• This ensures the accuracy and reliability of the data in the table. 
• If there is any violation between the constraint and the data action, the action is aborted.
• Constraints can be used column level or table level. 
• Column-level constraints apply to a column, and table-level constraints apply to the whole table.

------>As per my understanding, we have 6 types of constraints

                            1)Primary key

                            2)Foreign Key

                            3)Check

                             4)Unique

                             5)Not Null

                             6)Default 

Primary key

---------------:-

------>Nothing Should be null.

------>In this column, all the values should be unique.

----->It is used to create the relation b/w one table to other tables.

create table if not exists test(

test_id int,

test_name varchar(30),

test_mailid varchar(30),

test_address varchar(30),

primary key(test_id)) 

insert into test values(1,'galla','galla@gmail.com','Hyderabad')

select count(*) from test

select * from test

insert into test values(1,'galla','galla@gmail.com','Hyderabad')

---Error Code: 1062. Duplicate entry '1' for key 'test.PRIMARY'

insert into test values(2,'galla','galla@gmail.com','Hyderabad')

insert into test values('galla','galla@gmail.com','Hyderabad')

---Error Code: 1136. Column count doesn't match value count at row 1

insert into test(test_name,test_mailid,test_address) values('galla','galla@gmail.com','Hyderabad')

---Error Code: 1364. Field 'test_id' doesn't have a default value

Not null:-

------------

----->Column can't have a null value.

create table if not exists test1(

test_id int not null,

test_name varchar(30),

test_mailid varchar(30),

test_address varchar(30))

insert into test1 values(1,'galla','galla@gmail.com','Hyderabad')

select * from test1

insert into test1 values('galla','galla@gmail.com','Hyderabad')

Error Code: 1136. Column count doesn't match value count at row 1

insert into test1(test_name,test_mailid,test_address) values('galla','galla@gmail.com','Hyderabad')

--Error Code: 1364. Field 'test_id' doesn't have a default value

Auto_increament(keyword it is not a constraint)

-----------------------------------------------------------

create table if not exists test2(

test_id int auto_increment,

test_name varchar(30),

test_mailid varchar(30),

test_address varchar(30))

--Error Code: 1075. Incorrect table definition; there can be only one auto column and it must be defined as a key

create table if not exists test2(

test_id int not null auto_increment,

test_name varchar(30),

test_mailid varchar(30),

test_address varchar(30))

---Error Code: 1075. Incorrect table definition; there can be only one auto column and it must be defined as a key

Note:-

-----

---->While am creating the table with only the auto-increment keyword it is not allowing me to create the table, Then I try to create the table with not null constraint but still am getting the same issue then I try to create the table with the primary key constraint, now it is allowing to create the table. 

create table if not exists test2(

test_id int auto_increment,

test_name varchar(30),

test_mailid varchar(30),

test_address varchar(30),

primary key(test_id))

insert into test2 values(1,'galla','galla@gmail.com','Hyderabad')

insert into test2 values('galla','galla@gmail.com','Hyderabad')

---Error Code: 1136. Column count doesn't match value count at row 1

Note:-

-----

---->Now I tried to check how the auto-increment keyword is work, I tested with the above syntax and am getting an error.

---->But am trying to insert the data with the below syntax it is allowing me to insert the data.

insert into test2(test_name,test_mailid,test_address) values('galla','galla@gmail.com','Hyderabad');

---->But in the above syntax am not mentioned the column name test_id(it is an auto-increment column with a primary key), but it is allowing to insert of the data(Bcz of auto-increment keyword it is generating data for the column test_id).

select * from test2;

create table if not exists test2(

test_id int auto_increment,

test_name varchar(30),

test_mailid varchar(30),

test_address varchar(30),

test_salary int auto_increment,

primary key(test_id))

Error Code: 1075. Incorrect table definition; there can be only one auto column and it must be defined as a key

create table if not exists test2(

test_id int,

test_name varchar(30),

test_mailid varchar(30),

test_address varchar(30),

test_salary int auto_increment,

primary key(test_id))

Error Code: 1075. Incorrect table definition; there can be only one auto column and it must be defined as a key

Note:-

----

---->Finally it allows putting only one column as the auto_increment keyword.

create table if not exists test2(

test_id int auto_increment,

test_name varchar(30),

test_mailid varchar(30),

test_address varchar(30),

test_salary int auto_increment,

primary key(test_id),

primary key(test_salary))

Note:-

----

---->I try to create the table with 2 primary keys and 2 auto_increment keywords it is not allowing it.

create table if not exists test2(

test_id int auto_increment,

test_name varchar(30),

test_mailid varchar(30),

test_address varchar(30),

test_salary int,

primary key(test_id),

primary key(test_salary))

Error Code: 1068. Multiple primary key defined

Note:-

--------

------->We can create the primary key for the multiple columns at the time of table creation itself only

after that, it is not allowed.

Check constraint

-----------------:-

---->It ensures the data insert into the specific column is satisfied with the check condition.

create table if not exists test3(

test_id int auto_increment,

test_name varchar(30),

test_mailid varchar(30),

test_address varchar(30),

test_salary int check (test_salary>500),

primary key(test_id))

insert into test3 values(1,'galla','galla@gmail.com','Hyderabad',450)

----Error Code: 3819. Check constraint 'test3_chk_1' is violated.

insert into test3 values(1,'galla','galla@gmail.com','Hyderabad',500)

----Error Code: 3819. Check constraint 'test3_chk_1' is violated.

Note:-

--->Even it is not allowing equal value also.

insert into test3 values(1,'galla','galla@gmail.com','Hyderabad',501)

How to add multiple check constraints for a table in the MYSQL database

------------------------------------------------------------------------------------------

create table if not exists test3(

test_id int auto_increment,

test_name varchar(30) check (test_name='galla'),

test_mailid varchar(30) check(test_mailid='galla@gmail.com'),

test_address varchar(30) check(test_address=Hyderabad),

test_salary int check (test_salary>500),

primary key(test_id))

---Error Code: 3813. Column check constraint 'test3_chk_3' references other columns.

Reason:-

-------

---->I have not mentioned properly the 3rd check condition (I forget to put the string in single quotes)

create table if not exists test3(

test_id int auto_increment,

test_name varchar(30) check (test_name='galla'),

test_mailid varchar(30) check(test_mailid='galla@gmail.com'),

test_address varchar(30) check(test_address='Hyderabad'),

test_salary int check (test_salary>500),

primary key(test_id))

insert into test3 values(1,'srinu','galla@gmail.com','Hyderabad',501)

---Error Code: 3819. Check constraint 'test3_chk_1' is violated.

insert into test3 values(1,'galla','srinu@gmail.com','Hyderabad',501)

---Error Code: 3819. Check constraint 'test3_chk_2' is violated.

insert into test3 values(1,'galla','galla@gmail.com','Bangalore',501)

---Error Code: 3819. Check constraint 'test3_chk_3' is violated.

insert into test3 values(1,'galla','galla@gmail.com','Hyderabad',450)

---Error Code: 3819. Check constraint 'test3_chk_4' is violated.

insert into test3 values(1,'galla','galla@gmail.com','Hyderabad',500)

---Error Code: 3819. Check constraint 'test3_chk_4' is violated.

insert into test3 values(1,'galla','galla@gmail.com','Hyderabad',501)

How to add the check constraints if the table already exists

-----------------------------------------------------------------------

create table if not exists test3(

test_id int auto_increment,

test_name varchar(30),

test_mailid varchar(30),

test_address varchar(30) check(test_address='Hyderabad'),

test_salary int check (test_salary>500),

primary key(test_id))

insert into test3 values(1,'galla','galla@gmail.com','Hyderabad',501)

syntax:-

--------

alter table <table name> add constraint <constraint_name> check(Condition)

alter table test3 add constraint name_on check (test_name='galla')

desc test3

insert into test3 values(1,'srinu','galla@gmail.com','Hyderabad',501)

---Error Code: 3819. Check constraint 'name_on' is violated.

insert into test3 values(1,'galla','galla@gmail.com','Hyderabad',501)

---Error Code: 1062. Duplicate entry '1' for key 'test3.PRIMARY'

insert into test3 values(2,'galla','galla@gmail.com','Hyderabad',501)

alter table test3 add constraint on_mailid check(test_mailid='galla@gmail.com')

insert into test3 values(3,'galla','srinu@gmail.com','Hyderabad',501)

----Error Code: 3819. Check constraint 'on_mailid' is violated.

insert into test3 values(3,'galla','galla@gmail.com','Hyderabad',501)

desc test3

How to drop the check constraint:-

---------------------------------------

Syntax:-

---------

alter table <table name> drop check <constraint_name>.

alter table test3 drop check on_mailid

insert into test3 values(4,'galla','srinu@gmail.com','Hyderabad',501)

select * from test3

How to drop the primary key

----------------------------------:-

Syntax:-

ALTER TABLE <table name> DROP PRIMARY KEY;

alter table test3 drop primary key

Error Code: 1075. Incorrect table definition; there can be only one auto column and it must be defined as a key.

Reason:-

---------

----->primary key column contains auto_increment.

https://stackoverflow.com/questions/2111291/remove-primary-key-in-mysql

alter table test3 modify test_id int not null;

alter table test3 drop primary key

insert into test3 values(1,'galla','srinu@gmail.com','Hyderabad',501)

select * from test3

How to drop the FOREIGN key

-------------------------------------

Syntax:-

-------

alter table <table name> drop DROP FOREIGN KEY <FOREIGN KEY_NAME>.

ALTER TABLE Orders

DROP FOREIGN KEY FK_PersonOrder;

How to drop unique constraints:-

-------------------------------------

alter table <table_name> drop index <Index_Name>;

Ex:-

ALTER TABLE Persons  DROP INDEX UC_Person;

Default constraints

-------------------:

---->It is used if we are not assigned any value for a column it will automatically assign the value that we have mentioned as a default value.

create table if not exists test6(

test_id int NOT NULL default 0,

test_name varchar(30),

test_mailid varchar(30),

test_adress varchar(30) check(test_adress='hyderabad'),

test_salary int check(test_salary > 10000))

insert into test6 values(1,'galla','galla@gmail.com','Hyderabad',10002)

select * from test6

insert into test6 values('galla','galla@gmail.com','Hyderabad',10002)

Error Code: 1136. Column count doesn't match value count at row 1

insert into test6(test_name,test_mailid,test_adress,test_salary) values('galla','galla@gmail.com','Hyderabad',10002)

select * from test6

--->Now for the above stmnt it will assign the values as 0 by default.

Unique constraint

---------------------

----->The column we have mentioned as unique constraints column should be always 

unique values(it will not be allowed duplicate values)

create table if not exists test7(

test_id int unique,

test_name varchar(30),

test_mailid varchar(30),

test_adress varchar(30),

test_salary int)

select * from test7

insert into test7 values(1,'galla','galla@gmail.com','Hyderabad',10)

select * from test7

Error Code: 1062. Duplicate entry '1' for key 'test7.test_id'

insert into test7 values(1,'galla','galla@gmail.com','Hyderabad',10)

select * from test7

How to put all the constraints in one table

-------------------------------------------------:-

create table if not exists test8(

test_id int auto_increment,

test_name varchar(30) unique,

test_mailid varchar(30) not null default 'unknown',

test_adress varchar(30) check(test_adress='Hyd'),

test_salary int check(test_salary>200),

primary key(test_id))

insert into test8 values(1,'galla','galla@gmail.com','Hyd',201)

select * from test8

insert into test8(test_name,test_adress,test_salary) values('srinu','Hyd',202)

select * from test8

insert into test8(test_id,test_name,test_adress,test_salary) values(2,'nag','Hyd',202)

-------How to change auto_increment Value in a specific table-----------

alter table test8 auto_increment=400

select * from test8

insert into test8(test_name,test_adress,test_salary) values('dhoni','Hyd',202)

select * from test8