MySQL Constraints
Hello everyone
in this article we learn about MySQL Constraint. Let’s go.
The constraint
in MySQL is used to define rules to agree or limit what data can be stored in
columns on a table. There are two types of categorized in MySQL Constraint – the
First one is Column level constraint (it can apply only to one column) and the second
one is Table level constraint (it applied to the entire table). MySQL
constraints can be declared when the table is created or after the table is
created. Constraints declared when the table is created you can use CREATE
TABLE statement and after table created you can use ALTER TABLE statement in
MySQL.
C# basic course click on: https://www.udemy.com/learn-csharp-with-sagar-jaybhay/
Web API 2-
.Net Core In depth In 5 Hours click on: https://www.udemy.com/web-api-2-net-core-in-depth-in-5-hrs-with-sagar-jaybhay/
Syntax of Constraints declaration:
With CREATE TABLE statement
CREATE TABLE
table_name
(
col_name1 datatype
constraint,
col_name2 datatype constraint,
col_name3 datatype constraint,
.
.
.
);
With ALTER TABLE statement
ALTER TABLE
table_name
ADD CONSTARINT
column_name
CONSTRAINT
(column_name) REFERENCES (column_name);
C# basic course click on: https://www.udemy.com/learn-csharp-with-sagar-jaybhay/
Web API 2-
.Net Core In depth In 5 Hours click on: https://www.udemy.com/web-api-2-net-core-in-depth-in-5-hrs-with-sagar-jaybhay/
Example of Constraints
declaration:
With CREATE TABLE statement
CREATE TABLE emp_details
(
eID INT AUTO_INCREMENT,
eFirst_Name VARCHAR (255) NOT
NULL,
eLast_Name VARCHAR (255) NOT
NULL,
eAge INT CHECK ( 0 > 18),
eAddress VARCHAR (50) DEFAULT
‘USA’,
PRIMARY KEY (eID)
);
With ALTER TABLE statement
ALTER TABLE emp_details
ADD CONSTARINT eAge
CONSTRAINT
CHK_EMP_DETAILS CHECK (Age > = 18 AND Address = ‘USA’)
Following are
the most important constraints used in MySQL:
1.
NOT
NULL CONSTRAINT
2.
UNIQUE
CONSTRAINT
3.
PRIMARY
KEY CONSTRAINT
4.
FOREIGN
KEY CONSTRAINT
5.
CHECK
CONSTRAINT
6.
DEFAULT
CONSTRAINT
7.
INDEX
CONSTRAINT
C# basic course click on: https://www.udemy.com/learn-csharp-with-sagar-jaybhay/
Web API 2-
.Net Core In depth In 5 Hours click on: https://www.udemy.com/web-api-2-net-core-in-depth-in-5-hrs-with-sagar-jaybhay/
1.
NOT NULL CONSTRAINT
MySQL
NOT NULL constraint a column in a table is not allowed to store NULL values.
2.
UNIQUE CONSTRAINT
MySQL
UNIQUE constraint does not allow inserting a duplicate value in a column.
3.
PRIMARY KEY CONSTRAINT
MySQL
PRIMARY KEY constraint uniquely identifies each row in a table and also it
creates a unique index for accessing the table will access faster.
4.
FOREIGN KEY CONSTRAINT
MySQL
FOREIGN KEY constraint uniquely identifies a row or record in another table.
Foreign key constraint creates a link between two table’s matches the Primary
key in one of the tables with a foreign key in the 2nd table. Foreign key also
called reference key.
5.
CHECK CONSTRAINT
MySQL
CHECK constraint controls the values in a column satisfies a specific
condition.
6.
DEFAULT CONSTRAINT
MySQL
DEFAULT constraint sets a default value for a column when the insert into
statement does not provide a specific value.
7.
INDEX CONSTRAINT
MySQL
INDEX constraint is used to create and retrieve data from the database very
speedily.
C# basic course click on: https://www.udemy.com/learn-csharp-with-sagar-jaybhay/
Web API 2-
.Net Core In depth In 5 Hours click on: https://www.udemy.com/web-api-2-net-core-in-depth-in-5-hrs-with-sagar-jaybhay/
Here we are just
looking Constraint and which constraints most important in MySQL. Now we will look
all constraint like Not Null, Unique, Primary Key, Foreign Key, Check, Default,
and Index in details in the next article.
Comments
Post a Comment