In previous articles i have given the idea about the alter statements and its use in real world. In this article i would like to give you idea about Alter Statement to Add constraints with examples. The alter statement is used for multiple purposes. I have already explained how alter statement is used to add the columns or alter statement to drop the columns. In this article i would like to explain Alter statement to Add Constraints with multiple real life examples. Before that you need to know about constraints in SQL and type of constraints in SQL.
Alter Statement to Add Constraints Syntax and Examples :
In this section i would like to give you examples about multiple Alter statements to add constraints in the table.
Basic Syntax of Alter :
Alter Table Tablename
ADD/DROP [column\index\constraint];
Adding and dropping Not Null Constraint :
The not null constraint is used when there is condition where null values are not been inserted in specified column.How to insert the not null constraint in on table column.
Syntax to Add not null constraint :
Alter table table_name
Add column column_name
Set Not Null;
You can directly add the constraint as well using following syntax ,
Alter table table_name
Add Constraint Column_name Not Null;
Example :
If in Customer table we require to add constraint for the column named Customer_id then following example is used,
Alter table Customer
Add Constraint Customer_id not null;
If the columnis not available then you can add the column as well,
Alter table Customer
Add column Customer_id set not null;
If you want to remove the not null constraints then we require to use the drop statement with alter statements,
Syntax to drop not null constraint :
Alter table table_name
Alter column column_name
drop NOT NULL;
Example :
Alter table Customer
Alter column Customer_id
drop Not Null;
The above statement will drop the Not Null Constraint of Customer_id Column.
Adding the Check Constraint :
The check constraint is used to Ensure that the value in a column meets a specific condition. We can also use alter statement to add the check constraint on specified column.
Syntax for Check constraint :
Alter table table_name Add Constraint column_name
Check [Check condition];
Example :
If you want to add the customers whose income is more than 1000 then we need to add check constraint,
Alter table Customer Add Constraint Income
Check Income>1000;
The above statement will add the check constraint to Income column of customer table.
Adding the Primary key Constraint :
In this section i would like to explain how to add the primary key constraint using alter table command.
Note : The primary key column does not contain the null values so you require to remove the null values in the table column to add the constraint.
Syntax to add primary key :
Alter table table_name
Add Primary Key Column_name;
Example:
If you want to add the customer_id as primary key then you require to remove the null values from the column and then you need to fire following query,
Alter table Customer
Add primary key Customer_id;
The above statement will create the primary key on column Customer_id.
Adding Foreign Key Constraint :
The foreign key constraint Ensure the referential integrity of the data in one table to match values in another table.
Syntax to add Foreign key constraint :
Alter table Child_table Add Constraint child_column
Foreign key Parent_column REFERENCES Parent_table;
If there are two tables One is Customer_Master and other is Customer table. We require to add foreign key to customer table then following statement needs to be used,
Alter table Customer Add Constraint Cust_id
Foreign key Cust_id REFERENCES Customer_Master;
The above statement will add foreign key to customer table.
Hope you like the article of Alter statement with adding constraint.