Check Constraint in SQL | Default Constraint in SQL

1. SQL Constraint – Objective

In this SQL tutorial, we are going to study SQL Constraint. Moreover, we will see the Check Constraint in SQL and Default Constraint in SQL. 
So, let us start Default and Check Constraint in SQL.

Check Constraint in SQL

Check Constraint in SQL | Default Constraint in SQL

2. Default and Check Constraint in SQL

The CHECK constraint in SQL is basically used to put a value limit on the values that can be put in a column.
A DEFAULT constraint, on the other hand, is used to assign default values to the columns.
Do you know about SQL Data Types

3. CHECK Constraint in SQL

The example uses a CHECK constraint in SQL on the column “Age” which is the column “Person”, this statement checks to not allow any person below the age of 18.

  • MySQL Check Constraint
CREATE TABLE Persons (
   ID int NOT NULL,
   LastName varchar(255) NOT NULL,
   FirstName varchar(255),
   Age int,
   CHECK (Age>=18)
);
  • SQL Server / Oracle / MS Access
CREATE TABLE Persons (
   ID int NOT NULL,
   LastName varchar(255) NOT NULL,
   FirstName varchar(255),
   Age int CHECK (Age>=18)
);

The following syntax is important in Check Constraint in SQL.

  • MySQL / SQL Server / Oracle / MS Access
CREATE TABLE Persons (
   ID int NOT NULL,
   LastName varchar(255) NOT NULL,
   FirstName varchar(255),
   Age int,
   City varchar(255),
   CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);
  • SQL Server CHECK on ALTER TABLE

To create a CHECK constraint in SQL on the “Age” column when the table is already created, use the following SQL:
Have a look at SQL Operators

  • MySQL / SQL Server / Oracle / MS Access
ALTER TABLE Persons
ADD CHECK (Age>=18);

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

  • MySQL / SQL Server / Oracle / MS Access
ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');
  • DROP a CHECK SQL Constraint

To drop a CHECK constraint in SQL, use the following SQL Constraint syntax:

  • SQL Server / Oracle / MS Access
ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;

MySQL Constraint:

  • ALTER TABLE Persons
DROP CHECK CHK_PersonAge;

Let’s discuss Distinct Keyword in SQL

4. DEFAULT Constraint in SQL

The syntax for Default SQL Constraint –

CREATE TABLE Persons (
   ID int NOT NULL,
   LastName varchar(255) NOT NULL,
   FirstName varchar(255),
   Age int,
   City varchar(255) DEFAULT 'Sandnes'
);

We can also use the DEFAULT constraint in SQL to insert system values, through functions like GETDATE():

CREATE TABLE Orders (
   ID int NOT NULL,
   OrderNumber int NOT NULL,
   OrderDate date DEFAULT GETDATE()
);
  • SQL DEFAULT on ALTER TABLE

When the table is already created.
MySQL Default Constraint

ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes';
SQL Server / MS Access:
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'Sandnes';
  • Alter Table Default Value Oracle
ALTER TABLE Persons
MODIFY City DEFAULT 'Sandnes';
  • DROP DEFAULT Constraint in SQL Server

To drop a DEFAULT constraint, use the following SQL

MySQL:
ALTER TABLE Persons
ALTER City DROP DEFAULT;
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;

Let’s revise Null values in SQL
So, this was all in SQL Constraint. Hope you like our explanation on Default and Check Constraint in SQL

5. SQL Constraint – Conclusion

In this SQL Constraint tutorial, we learned about the DEFAULT and CHECK Constraint in SQL. Moreover, in this SQL Constraint, we discussed SQL Server add default Constraint, drop constraint SQL server, set default value in SQL. Still, if any doubt regarding SQL Constraint, ask in the comment tab.
See also –
SQL Constraint Types
For reference

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.