SQL Constraints

1.UNIQUE
2.NOT NULL
3.CHECK
4.DEFAULT

SQL Constraints

Here we Go…

UNIQUE:

  •  it does not allow duplicate values and it is as same as using DISTINCT in Select sql statement.

Let us alter the banana table column weight with Unique constraint.

 

When we insert duplicate FruitName on UNIQUE column name Fruitname then it throws Error.

Main Difference between Primary key and UNIQUE constraint

  • Primary key does not allow duplicate and does not allow Null values.
  • UNIQUE does not duplicate values and it allows Null value.

Here you can see Primary key table apple does not allow NULL and Duplicate values.

UNIQUE does not allow Duplicates but it allows null or zero values.

Here you can UNIQUE value could be 0 or NULL value.

UNIQUE does not allow duplicate values.


NOT NULL:

  • It restricts or does NOT allow the data base table column to have the NULL value.

Let us check this NOT NULL by creating a Banana table with FruitID as not Null, which means it does not accept the NULL value.
it restricts us to enter null value into the table.

Now we try to insert NULL value into above table, Let us see what happens.

you are getting an error that Cannot be null.


CHECK :

  • If we use check constraint in our sql statement, It will check the column range value.

DEFAULT:
  • Default is to set the default value.
  • we can alter by modifying the field with default value.