How To Purpose Alteration Ascendance Inward Sql - Database Tutorial

In this SQL tutorial  we volition run across how to utilisation ALTER ascendency command inwards tabular array on database. ALTER ascendency is mainly used to add, modify as well as driblet columns, indexes as well as constraints on tabular array inwards relational database e.g. MySQL, Oracle, Sybase as well as SQL Server.  Though ALTER is non portion of classical CRUD performance only it’s ane of the of import DDL command. One of the most frequent utilisation of ALTER ascendency inwards SQL is adding as well as removing indexes to amend performance of SQL SELECT queries. In this SQL as well as database tutorial nosotros volition run across unlike ways of using ALTER command. By using ALTER ascendency nosotros tin add, edit or modify table, views as well as databases. We tin add together novel column on the table, nosotros tin alter value of column or nosotros tin rename column also.

10 ALTER ascendency Example inwards SQL

Let’s run across around of the most oft used instance of ALTER ascendency inwards SQL. In this department nosotros volition run across How to utilisation ALTER ascendency to add, modify as well as driblet existing columns inwards table.

Example 1 : How to add together column inwards existing tabular array using ALTER clause inwards SQL:

Suppose nosotros convey ane tabular array called pupil alongside champaign roll_number, stu_name, class, subject as well as nosotros desire to add together ane to a greater extent than column called marks-obtained, how volition produce using ALTER Command.

ALTER TABLE Student ADD marks_obtained Number (3);

When this champaign is added to the tabular array it volition comprise NULL Value yesteryear default.

Example two :  How to modify existing column using ALTER ascendency inwards SQL:

Suppose straight off nosotros desire to modify inwards a higher house added champaign mark_obtained from divulge to decimal information type nosotros tin utilisation modify column along alongside alter command.

ALTER TABLE Student ALTER COLUMN marks_obtained DECIMAL (5, 2);

Example three :  How to driblet existing column from tabular array using ALTER ascendency inwards SQL:

Now if nosotros desire to driblet around existing column from the tabular array nosotros utilisation driblet along alongside ALTER ascendency

ALTER TABLE Student DROP column marks_obtained;

Example 4. How to driblet Primary Key Constraint using ALTER ascendency inwards database :

Suppose at that topographic point is a PRIMARY KEY CONSTRAINT named 'pk_roll_num' for the column roll_number of the Student tabular array as well as nosotros desire to driblet this constraint

ALTER TABLE Student DROP CONSTRAINT 'pk_roll_num’;

Example 5. How to add together primary key constraints using ALTER ascendency inwards SQL:

Now if nosotros desire to add together primary key constraint inwards tabular array Student named 'pk_roll_num' for the column roll_number' . We produce similar this

ALTER TABLE Student ADD Constraint 'pk_roll_num PRIMARY KEY(roll_number)

Example 6. How to driblet unusual key Constraint using Alter command:

Suppose at that topographic point is a unusual key constraint named 'fk_roll_num' for the column roll_number' of the 'Marks' table as well as nosotros desire to driblet this constraint

ALTER TABLE Marks DROP CONSTRAINT 'fk_roll_num’;

Example seven : How to add together unusual key Constraints using alter command

Now if nosotros desire to add together foreign key constraint inwards tabular array Marks named 'fk_roll_num' for the column roll_number' .we produce similar this

ALTER TABLE Marks ADD Constraint 'fk_roll_num FOREIGN KEY (roll_number) REFERENCES Students (roll_number);

Example 8 : How to add together unique key Constraints using ALTER command

Now if nosotros desire to add together Unique key constraint inwards tabular array Student named ‘unique_roll_no ' for the column roll_number' .we produce similar this

ALTER TABLE Student ADD CONSTRAINT unique_roll_no UNIQUE (roll_Number);

Example 9 : How to driblet unique Key Constraint using ALTER command

Now if nosotros desire to driblet unique key constraint inwards tabular array Student named ‘unique_roll_no ' for the column roll_number' .we produce similar this

ALTER TABLE Student DROP CONSTRAINT unique_roll_no);

Example 10 : How to add together depository fiscal establishment fit Constraint using ALTER ascendency inwards SQL

Basic syntax for adding depository fiscal establishment fit constraint inwards tabular array is similar this

ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

This is all almost ALTER ascendency examples inwards SQL. We convey seen 10 unlike ways of using ALTER ascendency inwards SQL. ALTER is ane of the most of import patch working inwards whatsoever database e.g. MySQL, Oracle or SQL Server, peculiarly from ascendency line. Since nosotros often require to add, modify as well as driblet columns, indexes as well as constraints inwards table.

Further Learning
Difference betwixt clustered as well as non-clustered index inwards SQL

Difference betwixt View as well as materialized stance inwards SQL

Difference betwixt candidate key as well as primary key inwards SQL

Difference betwixt correlated as well as noncorrelated subquery inwards database

When to utilisation truncate vs delete inwards database