Constraints in SQL | Every Constraints You Need to Understand In-Depth

Summary: You will learn in this article, What Are Constraints in SQL? such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT etc. Let’s understand the Constraints in SQL.

TABLE OF CONTENT

1. What Are Constraints in SQL?
2. How to Create Constraints in SQL?
3. Types of Constraints in SQL
4. FAQs?

I will explain in this article SQL NOT NULL, Unique, and SQL Primary Key constraints in SQL Server with a detailed illustration.

Therefore, SQL is short for Structured Query Language, which is used for the purpose of storing, manipulating, and retrieving data from a database. as we know that it is an American National Standards Institute standard language. Furthermore, several Relational Database Management Systems (RDMS) use SQL to allow users to work with data.

Since, when you are working with data using SQL, then it is essential to handle and describe it. That’s wherever constraints in SQL come into the scenario or picture.

All Constraints are predefined rules and restrictions that are enforced in a single column or multiple columns, regarding the values allowed in the columns, to keep up the integrity, accuracy, and reliableness of that column’s data. In another way, we can say that if the inserted data meets the constraint rule, then it will be inserted successfully. Furthermore, if the inserted data in SQL violates the defined constraint, the insert operation will be aborted.

What Are Constraints in SQL?

SQL constraints are set of rules that you can imply to the data in tables in relational databases to dictate what data can be inserted, updated, or deleted in its tables.

It allows or permits you to restrict only specific data that meets the regulations to go to a table. Therefore, To place it simply, only if the data meets the constraint’s rules, the insert operation will be successful, or else it’ll be aborted.

Constraints in SQL will help us to maintain the accuracy, integrity, and reliability of a table’s data. Therefore, it can create them at a column or table level. Suppose that if you declare constraints at the column level, then it will apply them to a single column.

In other words, if you declare them at the table level, it’ll implement them in more than one column. In SQL, you can create constraints while creating a table using the CREATE TABLE command or later using the ALTER TABLE command. If you create a constraint with the ALTER TABLE command, the creation will only be successful if all the existing data meets the constraint rules.

How to Create Constraints in SQL?

As mentioned in the below SQL syntax, you can create constraints in SQL using the CREATE TABLE command while creating a new table or ALTER TABLE command while altering an existing table. Furthermore, the basic syntax of creating an SQL constraint using the CREATE TABLE command is:

CREATE TABLE Table_Name(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
column_nameN data_type(size) constraint_name,
);

CREATE TABLE Table_Name(

column_name1 data_type(size) constraint_name,

column_name2 data_type(size) constraint_name,

column_name3 data_type(size) constraint_name,

....

column_nameN data_type(size) constraint_name,

);

In the above syntax defined Parameters:

Table_Name: Mentioned the table Name which you want to create
column_name: defined the column name you want to create
data_type: Data type of the value which you want to add to the column
size: defined the maximum size (length) of the column
constraint_name: write the constraint name which you want to create and implement

Furthermore, you can also create a constraint in SQL using the SQL ALTER TABLE command through the given below the following syntax:

ALTER TABLE Table_Name ALTER COLUMN column_name data_type(size) constraint_name

1	ALTER TABLE Table_Name ALTER COLUMN column_name data_type(size) constraint_name

Types of Constraints in SQL

constraints in SQL can be at a column or a table level. first of all, column-level constraints apply to specific columns in a table and don’t specify a column name except the check constraints. They refer to the column which they are following. Furthermore, the names are specified by the Table-level constraints of the columns to which they apply.
The given following list of the most commonly used column and table level SQL constraints:

SQL Constraints List include:

NOT NULL Constraint
UNIQUE Constraint
DEFAULT Constraint
CHECK Constraint
PRIMARY KEY Constraint
FOREIGN KEY Constraint
INDEX Constraint

Let us now discuss in detail every SQL constraint! why we use them and how to apply and remove them.

1). The NOT NULL Constraint

A NOT NULL constraint specifies that no cell value for any row during this column will be blank. Generally, this rule is applied to columns that capture information that’s absolutely vital to identify and extract data from a table. Furthermore, continued the Orders table example, Order_Id, and also the Order_Amount would be potential columns for applying the NOT NULL constraint.

The NOT NULL constraint can be declared either during the creation of the table or it can be put in place later via an ALTER statement.
Let’s declare a NOT NULL Constraint during the Creation of a Table:

The given following SQL query creates a NOT NULL constraint in the columns ‘Order_Id’, ‘Order_Amount’ and ‘Product_Name’ when the table ‘Orders’ is created:

CREATE TABLE Orders (
    Order_Id int NOT NULL,
    Order_Amount int NOT NULL,
    Product_Name varchar(255) NOT NULL,
    Order_Date date,
    Earn_Profit int
);

CREATE TABLE Orders (

Order_Id int NOT NULL,

Order_Amount int NOT NULL,

Product_Name varchar(255) NOT NULL,

Order_Date date,

Earn_Profit int

);

Explanation: By specifying the words NOT NULL after the column definition, we create an ‘Orders’ table where the ‘Order_Id’, ‘Order_Amount’ and ‘Product_Name’ columns cannot be blank. The column ‘Profit’ can have null values.

SQL Alter Statement: Let’s alter a NOT NULL Constraint after the Creation of a Table:

Consider, that once the creation of the ‘Orders’ table and storing info within the same, business logic changes and currently we’re instructed that no orders can be recorded within the ‘Orders’ table while not recording the amount of profit that was attained on the sale. therein case, we are going to currently add a constraint that the profit column can’t be null. this is often however we’d do it:

ALTER TABLE Orders
MODIFY Earn_Profit int NOT NULL;

1 2	ALTER TABLE Orders MODIFY Earn_Profit int NOT NULL;

2). The UNIQUE Constraint

The UNIQUE constraint in SQL specifies that no cell value in a column can be repeated throughout the table. Furthermore, each row for this column in the table has to be unique & non-repetitive. In SQL PRIMARY KEY & UNIQUE constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint in SQL has a UNIQUE constraint automatically. Therefore, you can have many UNIQUE constraints in a table but can notice here that only one PRIMARY KEY constraint can be there in one table.

Let’s apply the UNIQUE constraint:

The UNIQUE constraint can be introduced either during the creation of the table or can be put in place later via an SQL ALTER statement.
Declaring a UNIQUE Constraint in SQL during the Creation of a Table:

The given below the following SQL creates a UNIQUE constraint in the columns ‘Order_Id’ when the table ‘Orders’ is created in various relational databases:

SQL Server / Oracle / MS Access:

CREATE TABLE Orders (
    Order_Id int NOT NULL UNIQUE,
    Order_Amount int NOT NULL,
    Product_Name varchar(255) NOT NULL,
    Order_Date date,
    Earn_Profit int
);

CREATE TABLE Orders (

Order_Id int NOT NULL UNIQUE,

Order_Amount int NOT NULL,

Product_Name varchar(255) NOT NULL,

Order_Date date,

Earn_Profit int

);

MySQL:

CREATE TABLE Orders (
    Order_Id int NOT NULL,
    Order_Amount int NOT NULL,
    Product_Name varchar(255) NOT NULL,
    Order_Date date,
    Earn_Profit int,
    UNIQUE(Order_Id)
);

CREATE TABLE Orders (

Order_Id int NOT NULL,

Order_Amount int NOT NULL,

Product_Name varchar(255) NOT NULL,

Order_Date date,

Earn_Profit int,

UNIQUE(Order_Id)

);

To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, then use the following given below SQL syntax:

SQL Server / Oracle / MS Access:

CREATE TABLE Orders (
    Order_Id int NOT NULL,
    Order_Amount int NOT NULL,
    Product_Name varchar(255) NOT NULL,
    Order_Date date,
    Earn_Profit int,
    CONSTRAINT UC_Orders UNIQUE (Order_Id,Order_Amount));

CREATE TABLE Orders (

Order_Id int NOT NULL,

Order_Amount int NOT NULL,

Product_Name varchar(255) NOT NULL,

Order_Date date,

Earn_Profit int,

CONSTRAINT UC_Orders UNIQUE (Order_Id,Order_Amount));

Furthermore, let’s Altering a UNIQUE Constraint after the Creation of a Table as follows:
Therefore, first of all, to create a UNIQUE constraint on the “Order_Id” column when the table is already created, then use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD UNIQUE(Order_Id);

1 2	ALTER TABLE Orders ADD UNIQUE(Order_Id);

Furthermore, to name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, then use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD CONSTRAINT UC_Orders UNIQUE(Order_Id,Order_Amount);

1 2	ALTER TABLE Orders ADD CONSTRAINT UC_Orders UNIQUE(Order_Id,Order_Amount);

Dropping a UNIQUE Constraint:
Furthermore, to drop a UNIQUE constraint, then, first of all, we will need to specify the naming convention that was used during the creation of the constraint:

MySQL:

ALTER TABLE Orders
DROP INDEX UC_Orders;

1 2	ALTER TABLE Orders DROP INDEX UC_Orders;

SQL Server / Oracle / MS Access:

ALTER TABLE Orders
DROP CONSTRAINT UC_Orders;

1 2	ALTER TABLE Orders DROP CONSTRAINT UC_Orders;

3). The DEFAULT Constraint

The DEFAULT constraint is utilized for the purpose of specifying a default value that is to be entered in any record in a particular column that is left blank. Therefore, the default value will be added to all new records if no other value is specified.

Applying the DEFAULT Constraint:

The DEFAULT constraint in SQL can be defined either during the creation of the table or can be put in place later via an ALTER statement.

Therefore, To declare a DEFAULT Constraint during the Creation of a Table:

The given below the following SQL sets a DEFAULT value for the ‘Product_Name’ column when the ‘Orders’ table is created:

My SQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders (
    Order_Id int NOT NULL UNIQUE,
    Order_Amount int NOT NULL,
    Product_Name varchar(255) DEFAULT ‘Unknown Product’,
    Order_Date date,
    Earn_Profit int
);

CREATE TABLE Orders (

Order_Id int NOT NULL UNIQUE,

Order_Amount int NOT NULL,

Product_Name varchar(255) DEFAULT ‘Unknown Product’,

Order_Date date,

Earn_Profit int

);

The DEFAULT constraint in SQL can be used aslo for the purpose of populating columns with system values, for example, GETDATE().

CREATE TABLE Orders (
    Order_Id int NOT NULL UNIQUE,
    Order_Amount int NOT NULL,
    Product_Name varchar(255),
    Order_Date date DEFAULT GETDATE(),
    Earn_Profit int
);

CREATE TABLE Orders (

Order_Id int NOT NULL UNIQUE,

Order_Amount int NOT NULL,

Product_Name varchar(255),

Order_Date date DEFAULT GETDATE(),

Earn_Profit int

);

Furthermore, altering a DEFAULT Constraint after the Creation of a Table:

To create a DEFAULT constraint on the ‘Product_Name’ column when the table is already created, then use the following SQL query as given below:

MySQL:

ALTER TABLE Orders
ALTER Product_Name SET DEFAULT 'Unknown Product';

1 2	ALTER TABLE Orders ALTER Product_Name SET DEFAULT 'Unknown Product';

SQL Server:

ALTER TABLE Orders
ADD CONSTRAINT df_Product
DEFAULT 'Unknown Product' FOR Product_Name;

ALTER TABLE Orders

ADD CONSTRAINT df_Product

DEFAULT 'Unknown Product' FOR Product_Name;

MS Access:

ALTER TABLE Orders
ALTER COLUMN Product_Name SET DEFAULT 'Unknown Product';

1 2	ALTER TABLE Orders ALTER COLUMN Product_Name SET DEFAULT 'Unknown Product';

Oracle:

ALTER TABLE Orders
MODIFY Product_Name DEFAULT 'Unknown Product';

1 2	ALTER TABLE Orders MODIFY Product_Name DEFAULT 'Unknown Product';

Dropping a DEFAULT Constraint:
Furthermore, to drop a DEFAULT constraint, you will use the following SQL statement:

MySQL:

ALTER TABLE Orders
ALTER Product_Name DROP DEFAULT;

1 2	ALTER TABLE Orders ALTER Product_Name DROP DEFAULT;

SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ALTER COLUMN Product_Name DROP DEFAULT;

1 2	ALTER TABLE Orders ALTER COLUMN Product_Name DROP DEFAULT;

4). The CHECK Constraint

The CHECK constraint in SQL is used to ensure or confirm that all the records in a certain column follow a specific rule. Basically, this constraint is used to enforce business logic on values in a column to make sure that no corrupt information is entered. as an example, in the ‘Orders’ table, as an example, within the ‘Orders’ table, let’s say that the business has fixed a rule that Orders created to a definite product ‘XYZ’ should not be entered into the ‘Orders’ table. To ensure that orders to this product are not entered into our table, Furthermore, we add a CHECK constraint on the ‘Product_Name’ column which will reject any operation that tries to insert the value ‘XYZ’ in the ‘Product_Name’ column.

Applying the CHECK Constraint:

The CHECK constraint in SQL can be defined either throughout the creation of the table or can be put in place later via an SQL ALTER statement.

Declaring a CHECK Constraint throughout the Creation of a Table:

The following SQL query creates a CHECK constraint on the column ‘Product_Name’ once the table ‘Orders’ is created in various relational databases:

MySQL:

CREATE TABLE Orders (
    Order_Id int NOT NULL UNIQUE,
    Order_Amount int NOT NULL,
    Product_Name varchar(255),
    Order_Date date,
    Earn_Profit int,
CHECK (Product_Name< > ’XYZ’)
);

CREATE TABLE Orders (

Order_Id int NOT NULL UNIQUE,

Order_Amount int NOT NULL,

Product_Name varchar(255),

Order_Date date,

Earn_Profit int,

CHECK (Product_Name< > ’XYZ’)

);

SQL Server / Oracle / MS Access:

CREATE TABLE Orders (
    Order_Id int NOT NULL UNIQUE,
    Order_Amount int NOT NULL,
    Product_Name varchar(255) CHECK (Product_Name< > ’XYZ’),
    Order_Date date,
    Earn_Profit int
);

CREATE TABLE Orders (

Order_Id int NOT NULL UNIQUE,

Order_Amount int NOT NULL,

Product_Name varchar(255) CHECK (Product_Name< > ’XYZ’),

Order_Date date,

Earn_Profit int

);

Furthermore, currently that besides the rule for the seller ‘XYZ’, the business only wants orders with a profit greater than 300 to be recorded in the ‘Orders’ table.

After that to allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, then use the following SQL syntax as given below:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders (
    Order_Id int NOT NULL,
    Order_Amount int NOT NULL,
    Product_Name varchar(255),
    Order_Date date,
    Earn_Profit int,
CONSTRAINT Chk_Orders CHECK (Product_Name < > ’XYZ’ and Profit>300)
);

CREATE TABLE Orders (

Order_Id int NOT NULL,

Order_Amount int NOT NULL,

Product_Name varchar(255),

Order_Date date,

Earn_Profit int,

CONSTRAINT Chk_Orders CHECK (Product_Name < > ’XYZ’ and Profit>300)

);

Furthermore, Altering a CHECK Constraint after the Creation of a Table:
Furthermore, To create a CHECK constraint on the ‘Product_Name’ column once the table is already created, then use the following SQL query:

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE Orders
ADD CHECK (Product_Name< > ‘XYZ’);

1 2	ALTER TABLE Orders ADD CHECK (Product_Name< > ‘XYZ’);

Furthermore, To allow naming of a CHECK constraint in SQL, and for defining a CHECK constraint on multiple columns, let use the following SQL syntax as given below:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD CONSTRAINT Chk_Orders CHECK (Product_Name < > ’XYZ’ and Profit>300)

1 2	ALTER TABLE Orders ADD CONSTRAINT Chk_Orders CHECK (Product_Name < > ’XYZ’ and Profit>300)

Dropping a CHECK Constraint:

Furthermore, To drop a CHECK constraint, we will need to specify or declare the naming convention that was used during the creation of the constraint:

MySQL:

ALTER TABLE Customers
DROP CHECK CHK_Orders;

1 2	ALTER TABLE Customers DROP CHECK CHK_Orders;

SQL Server / Oracle / MS Access:

ALTER TABLE Customers
DROP CONSTRAINT CHK_Orders;

1 2	ALTER TABLE Customers DROP CONSTRAINT CHK_Orders;

5). PRIMARY KEY Constraint

PRIMARY KEYS play an important role in SQL, they are unique identifiers for each row present in a table. Therefore, they can be values present in a single column of a table or a combination of multiple columns in the table. you need to notice here that the PRIMARY KEY column can not be NULL and has to be UNIQUE.

Furthermore, the value of the PRIMARY KEY in the table is a unique identifier for a particular row in the parent table which connects the row of the table to further info available in another table such as the child table, where the same unique identifier exists as a FOREIGN KEY.

Furthermore, every FOREIGN KEY value in the second table has to exist in the first as a PRIMARY KEY. this is often however information is kept consistent in relational databases when they are broken down into multiple Fact and Dimension tables. The PRIMARY KEY and FOREIGN KEY columns are used as the join condition between two tables and the contained information in the tables are extracted.

Let’s see How to apply the PRIMARY KEY Constraint:

The PRIMARY KEY constraint in SQL is used to be defined either during the creation of the table or can be put in place later via an SQL ALTER statement.

Furthermore, let’s declare a PRIMARY KEY Constraint during the Creation of a Table:

The following SQL statement creates a PRIMARY KEY on the ‘Order_Id’ column when the ‘Orders’ table is created in various relational databases:

MySQL:

CREATE TABLE Orders (
    Order_Id int NOT NULL,
    Order_Amount int NOT NULL,
    Product_Name varchar(255),
    Order_Date date,
    Earn_Profit int,
    PRIMARY KEY (Order_Id)
);

CREATE TABLE Orders (

Order_Id int NOT NULL,

Order_Amount int NOT NULL,

Product_Name varchar(255),

Order_Date date,

Earn_Profit int,

PRIMARY KEY (Order_Id)

);

SQL Server / Oracle / MS Access:

CREATE TABLE Orders (
    Order_Id int NOT NULL PRIMARY KEY,
    Order_Amount int NOT NULL,
    Product_Name varchar(255),
    Order_Date date,
    Earn_Profit int,
);

CREATE TABLE Orders (

Order_Id int NOT NULL PRIMARY KEY,

Order_Amount int NOT NULL,

Product_Name varchar(255),

Order_Date date,

Earn_Profit int,

);

Therefore, to allow naming of a PRIMARY KEY constraint in SQL query and for defining a PRIMARY KEY constraint on multiple columns, then use the following SQL syntax as given below:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders (
    Order_Id int NOT NULL,
    Order_Amount int NOT NULL,
    Product_Name varchar(255),
    Order_Date date,
    Earn_Profit int,
    CONSTRAINT PK_Orders PRIMARY KEY (Order_Id, Order_Amount) 
);

CREATE TABLE Orders (

Order_Id int NOT NULL,

Order_Amount int NOT NULL,

Product_Name varchar(255),

Order_Date date,

Earn_Profit int,

CONSTRAINT PK_Orders PRIMARY KEY (Order_Id, Order_Amount)

);

Important Note: In the example above there is only ONE PRIMARY KEY (PK_Orders). However, the VALUE of the primary key is made up of 2 COLUMNS (Order_Id + Order_Amount).

Let’s see when altering a PRIMARY KEY Constraint after the Creation of a Table:

Furthermore, to create a PRIMARY KEY constraint on the ‘Order_Id’ column when the table is already created, use the following SQL syntax as given below:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD PRIMARY KEY (Order_Id);

1 2	ALTER TABLE Orders ADD PRIMARY KEY (Order_Id);

Furthermore, to allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, then use the following SQL syntax as given below:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD CONSTRAINT PK_ Orders PRIMARY KEY (Order_Id,Order_Amount);

1 2	ALTER TABLE Orders ADD CONSTRAINT PK_ Orders PRIMARY KEY (Order_Id,Order_Amount);

Important Note: If you utilize ALTER TABLE to add a primary key, then you need the primary key column(s) must have been declared to not contain NULL values and have only UNIQUE values, otherwise the SQL query will not be executed.

Let’ see the Dropping a PRIMARY KEY Constraint in SQL:
To drop a PRIMARY KEY constraint, then use the following SQL query as given below:

MySQL:

ALTER TABLE Orders
DROP PRIMARY KEY;

1 2	ALTER TABLE Orders DROP PRIMARY KEY;

SQL Server / Oracle / MS Access:

ALTER TABLE Orders
DROP CONSTRAINT PK_Orders;

1 2	ALTER TABLE Orders DROP CONSTRAINT PK_Orders;

6). FOREIGN KEY Constraint

The foreign key constraint in SQL is used for the purpose of preventing operations in a relational database that would destroy links between tables. Therefore, the SQL FOREIGN KEY is a column or a group of columns in one table, which refers to the PRIMARY KEY of another table. The table with the FOREIGN KEY is referred to as the child table whereas the referenced table with the PRIMARY KEY is called the parent table.

First of all, let’s consider the two following tables:
Table: Orders

Order_Id	Order_Amount	Product_Name	Order_Date	Earn_Profit
111	253	Maggi	02-11-2019	35
112	453	Wheat	03-11-2019	50
115	260	Brush	04-11-2019	20
121	1100	Rice	05-11-2019	235

Table: Customers

Customer_Id	Customer_Name	Customer_City	Order_Id
11	Aniket Singh	Noida	111
12	Shivaditya Shukla	Noida	112
13	Utkarsh Kumar	Noida	115
11	Pinki Gupta	Noida	121

Note:The ‘Order_Id’ column in the ‘Customers’ table refers to the ‘Order_Id’ in the ‘Orders’ table.
The ‘Order_Id’ in the ‘Orders’ table is the PRIMARY KEY.
The ‘Order_Id’ in the ‘Customers’ table is the FOREIGN KEY.

Notice that every value for ‘Order_Id’ present in the ‘Customers’ table is also available in the ‘Order_Id’ column in the ‘Orders’ table. this can be therefore because of the PRIMARY KEY – FOREIGN KEY relationship defined between the two tables. No value for an ‘Order_Id’ can be entered into the ‘Customers’ table that doesn’t already exist in the ‘Orders’ table. Furthermore, if we try to insert such a value, as a result of the PRIMARY KEY – FOREIGN KEY constraint, the insertion will be rejected.

Applying the FOREIGN KEY Constraint:

The FOREIGN KEY constraint in SQL can be defined either throughout the creation of the table or can be put in place later via an SQL ALTER statement.

Furthermore, When declaring a FOREIGN KEY Constraint during the Creation of a Table:

The following SQL statement creates a FOREIGN KEY on the ‘Order_Id’ column when the ‘Customers’ table is created:

MySQL:

CREATE TABLE Customers (
    Customer_Id int NOT NULL,
    Customer_Name varchar(255),
    Customer_City varchar(255),
    Order_Id int NOT NULL,
    PRIMARY KEY (Customer_Id),
    FOREIGN KEY (Order_Id) REFERENCES Orders(Order_Id)
);

CREATE TABLE Customers (

Customer_Id int NOT NULL,

Customer_Name varchar(255),

Customer_City varchar(255),

Order_Id int NOT NULL,

PRIMARY KEY (Customer_Id),

FOREIGN KEY (Order_Id) REFERENCES Orders(Order_Id)

);

SQL Server / Oracle / MS Access:

CREATE TABLE Customers (
    Customer_Id int NOT NULL PRIMARY KEY,
    Customer_Name varchar(255),
    Customer_City varchar(255),
    Order_Id int FOREIGN KEY REFERENCES Orders(Order_Id)
);

CREATE TABLE Customers (

Customer_Id int NOT NULL PRIMARY KEY,

Customer_Name varchar(255),

Customer_City varchar(255),

Order_Id int FOREIGN KEY REFERENCES Orders(Order_Id)

);

Furthermore, To allow naming of a FOREIGN KEY constraint in SQL Statement, and for defining a FOREIGN KEY constraint on multiple columns, then use the following SQL syntax as given below:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Customers (
    Customer_Id int NOT NULL,
    Customer_Name varchar(255),
    Customer_City varchar(255),
    Order_Id int NOT NULL,
    PRIMARY KEY (Customer_Id),
    CONSTRAINT FK_Orders_Customers FOREIGN KEY (Order_Id)
    REFERENCES Customers Orders(Order_Id)
);

CREATE TABLE Customers (

Customer_Id int NOT NULL,

Customer_Name varchar(255),

Customer_City varchar(255),

Order_Id int NOT NULL,

PRIMARY KEY (Customer_Id),

CONSTRAINT FK_Orders_Customers FOREIGN KEY (Order_Id)

REFERENCES Customers Orders(Order_Id)

);

When you want to Alter a FOREIGN KEY Constraint after the Creation of a Table:

Furthermore, To create a FOREIGN KEY constraint on the ‘Order_Id’ column when the table is already created, use the following SQL statement:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Customers
ADD FOREIGN KEY (Order_Id) REFERENCES Orders(Order_Id);

1 2	ALTER TABLE Customers ADD FOREIGN KEY (Order_Id) REFERENCES Orders(Order_Id);

Therefore, When you want to allow naming of a FOREIGN KEY constraint and for defining a FOREIGN KEY constraint on multiple columns, then use the following SQL statement:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Customers
ADD CONSTRAINT FK_Orders_Customers 
FOREIGN KEY (Order_Id) REFERENCES Orders(Order_Id);

ALTER TABLE Customers

ADD CONSTRAINT FK_Orders_Customers

FOREIGN KEY (Order_Id) REFERENCES Orders(Order_Id);

Let’s understand the dropping a FOREIGN KEY Constraint:
To drop a FOREIGN KEY constraint, then use the following SQL statement as given below:

MySQL:

ALTER TABLE Customers
DROP FOREIGN KEY FK_Orders_Customers;

1 2	ALTER TABLE Customers DROP FOREIGN KEY FK_Orders_Customers;

SQL Server / Oracle / MS Access:

ALTER TABLE Customers
DROP CONSTRAINT FK_Orders_Customers;

1 2	ALTER TABLE Customers DROP CONSTRAINT FK_Orders_Customers;

7). INDEX Constraint

The INDEX constraint in SQL is utilized to create indexes on a table in a relational database. Furthermore, Tables during a computer database will grow to be extraordinarily long with a good range of rows present in every table, under the circumstances, retrieving data via SQL will generally be a really time taking method. Furthermore, by creating an index, then the performance of data or information retrieval queries can be greatly improved. The users cann’t see the indexes, they’re simply utilized by the SQL engine to speed up searches/queries.

First of all, let’s apply the SQL INDEX Constraint:

We have the choice of creating an INDEX that allows duplicates, otherwise, we can create a unique INDEX. Furthermore, the Indexes can be created or dropped at any point in time and don’t have to be a part of the table definition at the time of table creation.

Let’s create an INDEX Constraint in SQL:

After that, creates an INDEX on a table with duplicate values allowed:

CREATE INDEX indx_id
ON Orders (Order_Id);

1 2	CREATE INDEX indx_id ON Orders (Order_Id);

When we create a unique index Constraint:

Furthermore, when we create a unique INDEX on a table. then note that the duplicate values aren’t allowed:

CREATE UNIQUE INDEX indx_id
ON Orders (Order_Id);

1 2	CREATE UNIQUE INDEX indx_id ON Orders (Order_Id);

Therefore, if you want to create an index on a combination of columns, then you can list the column names inside the parentheses, separated by commas:

CREATE UNIQUE INDEX indx_order
ON Orders (Order_Id, Order_Amount);

1 2	CREATE UNIQUE INDEX indx_order ON Orders (Order_Id, Order_Amount);

Understand the dropping an INDEX Constraint:
The DROP INDEX statement is basically utilized to delete an index in a table:

MS Access:

DROP INDEX indx_id ON Orders;

1	DROP INDEX indx_id ON Orders;

SQL Server:

DROP INDEX Orders.indx_id;

1	DROP INDEX Orders.indx_id;

DB2/Oracle:

DROP INDEX indx_id;

1	DROP INDEX indx_id;

MySQL:

ALTER TABLE Orders
DROP INDEX indx_id;

1 2	ALTER TABLE Orders DROP INDEX indx_id;

FAQs?

1). What are constraints in SQL?

Answer: SQL constraints, which are used for the purpose of specifying rules for the data in a table. furthermore, constraints in SQL are used to limit the type of data that can go into a table. This ensures the accuracy & reliability of the data in the table. Therefore, the Column level constraints apply to a column, and table-level constraints apply to the whole table.

2). What are 5 types of constraints?

NOT NULL constraints
Unique constraints
Primary key constraints
(Table) Check constraints
Foreign key (referential) constraints
Informational constraints.

3). What are the types of constraints in SQL?

There are the following SQL Server contains six types of constraints:

Not Null Constraint
Check Constraint
Default Constraint
Unique Constraint
Primary Constraint
Foreign Constraint

4). How many constraints are there in SQL?

Answer: There are six main constraints in SQL Server which are commonly used which are described above deeply with examples.

5). What is add constraint in SQL?

The ADD CONSTRAINT command in SQL Server is used for the purpose of creating a constraint after a table is already created.

6). What are the 3 three database constraints?

You can provide a default value for a column when none is specified.
1) UNIQUE Constraint − Ensures that everyone’s values in a column are different.
2) PRIMARY Key − Uniquely identifies every row/record in a database table.
3) FOREIGN Key − Uniquely identifies a row/record in any of the given database tables.

7). What is constraint name?

constraint_name. Assigns a name to the constraint in SQL Server. It should be a valid object name. The keyword CONSTRAINT should be used only when specifying a name.

8). Is null a constraint in SQL?

Therefore, by default, a column can hold NULL values. the NOT NULL constraint in the SQL server enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which implies that you simply can’t insert a new record, or update a record without adding a value to this field.

9). What are the key constraints?

Key constraints
Keys are the entity set that’s used to identify an entity within its entity set uniquely. furthermore, an entity set can have multiple keys, out of which one key will be the primary key. A primary key can contain a unique & null value in the relational table.

10). What is check constraint in database?

A check constraint in SQL is a type of integrity constraint that specifies a demand that must be met by each row in a database table. Furthermore, if the predicate evaluates to UNKNOWN, then the constraint isn’t violated and also the row can be inserted or updated within the table

11). What are constraints in engineering?

Constraints are limitations on the design planning, like available funds, resources, or time. Together, the criteria and constraints are referred to as the necessities for a successful solution. A further characteristic of engineering design is that ideas are tested before investing too much time, money, or effort.

12). What is constraints in DBMS with example?

Constraints are the rules that we are able to apply to the type of data in a table. That’s, we are able to specify the limit on the type of data that can be stored in a particular column in a table using constraints. Furthermore, The available constraints in SQL are: NOT NULL: This constraint tells that we can’t store a null value in a column.

13). Is primary key a constraint?

Primary keys and foreign keys are two types of constraints in SQL Server that can be used to enforce data integrity in SQL tables. These are important database objects.

14). What is a column level constraints?

In SQL server the column-level constraints refer to a single column in the table and don’t specify a column name (except check constraints). Furthermore, They refer to the column that they follow. a table-level constraint. also, Table-level constraints refer to one or more columns in the table.

Conclusion:

You have learned in this article, SQL constraints, this concludes our list of basically used SQL constraints. Which constraints is the most useful? Tell us all about your experience with constraints in SQL. Happy Learning! I hope you will enjoy it!