Constraints in SQL

constraintmain

What are Constraints ??

Constraints may be defined as rules or conditions applied on the data columns of the table, that restrict the type of data entering into the table.They act as filter between the data and the table.

 

Levels of Constraints :

1. Column Level   Applied to single column of the table.E.g Not Null , Primary Key etc.

2. Table Level  –  Applied to multiple columns. E.g. Primary Key , Unique Key etc.

Primary Key Constraint work at both Column and Table Level.
For each constraint , oracle database creates a Unique Constraint Name E.g SYS_COO7229…etc.

Syntaxes :

1. To View all the Constraints definitions of all the tables which are accessible to Current User in Current User’s Own Schema :

SELECT * FROM USER_CONSTRAINTS ;

2. To View all the Constraints definitions of a particular table of Current User in Current User’s Own Schema :

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = ‘<TABLE NAME’>;

 

 

To View all the Constraints definitions of all the tables which are accessible to Current User present in Current User’s Own Schema as well as other User’s Schema :

To View all the Constraints definitions of a particular table of Current User present in Current User’s Own Schema as well as other User’s Schema :

To View all the Constraint definitions of all the tables present in all Schemas :

Note : User_Constraints , DBA_Constraints and ALL_Constraints , These all are Data Dictionary Views.

Defining Constraints :

Constraints are defined at the time of Table Creation.

Modifying Constraints :

Constraints can be modified using ALTER Command.

Adding Constraint after Creating the table :

Renaming the Constraint :

Dropping the Constraint :

Enabling the Constraint :

Disabling the Constraint :

Example Illustration :

Note : Types of constraints will be covered in next post , here we have used the names just to explain.

 

Advantages of Using Constraints :

  • They help in maintaining integrity of the data.
  • They make the data in the database more reliable.
Print Friendly, PDF & Email
Data Control Language in SQL ( DCL Commands )
Types of Constraints
Shikha Katariya

Shikha Katariya

Shikha Katariya ,the Blog author is QA Engineer by profession,Currently serving in MNC, She has more than 4 years of experience in software industry and has worked for domains like Insurance , Core & retail Banking. Always keen to learn new technologies , she has working experience in mainframes,informatica ,and ETL Testing.

You may also like...

Leave a Reply

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