Apa itu constraint di mysql?

Introduction to MySQL Constraints

MySQL constraints are statements that can be applied at the column level or table level to specify rules for the data that can be entered into a column or data table, i.e constraints are basically limitations or restrictions on the type of data and hence they ensure reliability, consistency, and accuracy of the data. In SQL, we will come across the following types of constraints. Here is an overview of the generic purpose that these constraints serve.

Types of Constraints in MySQL with Examples

Following are the types and examples of constraints which are given below:

1. NOT NULL CONSTRAINT

When NOT NULL constraint is applied to a column, it ensures that the column will not accept NULL values. The syntax for NOT NULL constraint in MYSQL is as follows :

Syntax:

CREATE  TABLE table_name(
column_name_1 datatype  NOT NULL,
column_name_2 datatype  NOT NULL,
.
.
column_name_n datatype  NOT NULL
);

Parameters:

The parameters used in the syntax are :

• CREATE TABLE table_name: This statement is used for creating a new table in a database. Specify the name of the table.
• column_name1, column_name2, …: Specify the name of columns you want to create on the table.
• datatype: Specify the datatype of each column in the table.
• [NULL | NOT NULL]: Specify if it can hold NULL values or not.

Example

SQL query to create NOT NULL constraint while creating a table.

Query:

CREATE TABLE students (
student_ID int NOT NULL,
student_Name varchar(255) NOT NULL,
class_name varchar(255) NOT NULL,
Age int
);

Output:

Here, we have created a student table with three columns that accept non-NULL values. Let’s check if these columns accept null values or not.

We will try to insert a student record in the table which contains data for three columns only. The insert statement will fail because we did not supply any values for class_name.

INSERT INTO students (student_ID,student_Name,Age)
VALUES(13,’Holly’,9);

Output:

2. CHECK CONSTRAINT

When CHECK constraint is applied to a column, it ensures that the column will not accept data values outside the specified range. The CHECK constraint can also be mentioned to ensure that data meets a specified condition like less than, equal to, not, etc.

Syntax:

CREATE  TABLE table_name(
column_name_1 datatype,
column_name_2 datatype
CHECK(condition_on_column_name_2),
.
.
column_name_n datatype
);

Parameters:

• CREATE TABLE table_name: This statement is used for creating a new table in a database. Specify the name of the table.
• column_name1, column_name2, … : Specify the name of columns you want to create on the table.
• datatype: Specify the datatype of each column in the table.
• CHECK(condition_on_column_name_2): Specify the condition which data should meet before getting entered into a column.

Example

SQL query to create CHECK constraint while creating a table.

Query:

CREATE TABLE students (
student_ID int NOT NULL,
student_Name varchar(255) NOT NULL,
class_name varchar(255) NOT NULL,
Age int
CHECK(Age >9)
);

Output:

Here, we have created a student table with three columns that accept non-NULL values and the fourth column which will age greater than 9 only. Let’s check if the columns meet the specified conditions. We will try to insert a student record in the table which contains age less than 9. The insert statement will fail.

INSERT INTO students (student_ID,student_Name,class_name,Age)
VALUES(13,’Holly’,’IV’,8);

Output:

3. UNIQUE KEY CONSTRAINT

A unique key is a constraint in SQL which helps in uniquely identifying a record in the datatable. It can be considered somewhat similar to the Primary key as both of them guarantees the uniqueness of a record. But unlike primary key, a unique key can accept NULL values and it can be used on more than one column of the datatable.

Syntax:

CREATE TABLE table_name
(
Column_name1 datatype [NULL | NOT NULL] UNIQUE,
Column_name2 datatype [NULL | NOT NULL],
Column_name3 datatype [NULL | NOT NULL] );

Parameters:

• CREATE TABLE: This statement is used for creating a new table in a database.
• Column_name1, Column_name2,Column_name3: Mention the name of columns you want to create on the table.
• datatype: Mention the datatype of each column in the table.
• [NULL | NOT NULL]: Mention if it can hold NULL values or not.
• UNIQUE: Unique keyword written with the column name creates a unique key constraint. It ensures that there are no duplicate values in that particular column.

Example

SQL query to create unique constraints while creating a table.

Query:

CREATE TABLE students (
student_ID int UNIQUE,
student_Name varchar(255) NOT NULL,
class_name varchar(255) NOT NULL,
Age int
);

Output:

4. PRIMARY KEY CONSTRAINT

A primary key constraint is used to uniquely identify a record or row in the data table. It does not accept NULL values and accepts only unique values.

Syntax:

CREATE  TABLE table_name(
column_name_1 datatype  NOT NULL,
column_name_2 datatype  NOT NULL,
.
.
column_name_n datatype  NOT NULL
PRIMARY KEY (column_name_1)
);

Parameters:

• CREATE TABLE table_name: This statement is used for creating a new table in a database. Specify the name of the table.
• column_name1, column_name2, … : Specify the name of columns you want to create on the table.
• datatype: Specify the datatype of each column in the table.
• PRIMARY KEY (column_name_1): Specify the column name on which primary key constraint has to be applied. It should be a NOT NULL column.

Example

The SQL query to create primary key constraints while creating a table.

Query:

CREATE TABLE Students(
Student_ID int NOT NULL,
Student_Name varchar(255) NOT NULL,
Class_Name varchar(255),
Age int,
PRIMARY KEY (Student_ID)
);

Output:

5. FOREIGN KEY CONSTRAINT

A foreign key constraint is used to uniquely identify a record or row in another data table. It refers to the primary key in the other table. So, it basically acts as a link between two tables.

Syntax:

CREATE  TABLE table_name_1(
column_name_1 datatype  NOT NULL,
column_name_2 datatype  NOT NULL,
.
.
column_name_n datatype  NOT NULL
PRIMARY KEY (column_name_1)
FOREIGN KEY (column_name_2) REFERENCES table_name_2(column_name_2)
);

Parameters:

1. CREATE TABLE table_name_1: This statement is used for creating a new table in a database. Specify the name of the table.
2. column_name1, column_name2, … : Specify the name of columns you want to create on the table.
3. datatype: Specify the datatype of each column in the table.
4. PRIMARY KEY (column_name_1): Specify the column name on which primary key constraint has to be applied. It should be a NOT NULL column.
5. FOREIGN KEY (column_name_2): Specify the column name on which foreign key constraint has to be applied.
6. REFERENCES table_name_2(column_name_2): Specify the table name and its column on which foreign key in the first table is linked.

Example

SQL query to create foreign key constraint while creating a table.

Query:

CREATE TABLE Students(
Student_ID int NOT NULL,
Student_Name varchar(255) NOT NULL,
Class_Name varchar(255),
Age int,
PRIMARY KEY (Student_ID)
FOREIGN KEY (Class_Name) References classes(Class_Name)
);

Output:

In the above example, we have created a foreign key constraint on Class_Name which references the classes table.

6. DEFAULT CONSTRAINT

In order to ensure consistency in data, we sometimes require missing data or non-entered values to be set to a default value. We can use DEFAULT constraint in such cases to ensure that the column is set to default.

Syntax:

CREATE  TABLE table_name(
column_name_1 datatype  NOT NULL,
column_name_2 datatype DEFAULT ‘default_value’,
.
.
);

Example

SQL query to create default constraint while creating a table.

Query:

CREATE TABLE Students(
Student_ID int NOT NULL,
Student_Name varchar(255) NOT NULL,
Class_Name varchar(255) DEFAULT 'IV'
);

Output:

In the above example, the Class_Name column in the student’s table will set the class name to IV by default if nothing is specified.

We are inserting values for two columns only.

INSERT INTO Students ( [Student_ID], [Student_Name] ) VALUES (142,’Molly’);

Output:

We just inserted values for two columns but we can see in the table that class name has been set to an IV default value.

SELECT TOP 1000 [Student_ID], [Student_Name], [Class_Name] FROM [practice_art] . [dbo] . [Students]

Output:

Recommended Articles

This is a guide to MySQL Constraints. Here we discuss the Introduction and types of constraints along with different examples and its code implementation. You may also look at the following articles to learn more –

1. ANY in MySQL | How to Work?
2. Guide to Foreign Key in PostgreSQL
3. Guide to MySQL Subquery
4. MySQL IN Operator | Examples
5. MySQL Timestamp | How to Work?
6. IF Statement in MySQL | How to Work?