Which Of The Following Commands Can Be Used To Make Structural Changes To An Existing Table?

SQL Change TABLE

Last update on Feb 26 2020 08:07:43 (UTC/GMT +8 hours)

ALTER Tabular array

The SQL Alter TABLE command is used to modify the structure of an existing table. Information technology helps to add together or delete columns, create or destroy indexes, change the blazon of existing columns, or rename columns or the table itself.

It can also exist used to change the comment for the table and type of the table.

Syntax:

Change Tabular array <table_name>( ADD column1    data_type[(size)], ADD column2    data_type[(size)], ...);        

Parameters:

Proper noun	Description
table_name	Name of the table where data is stored.
column1,column2	Name of the columns of a table.
data_type	Char, varchar, integer, decimal, date and more.
size	Maximum length of the cavalcade of a table.

Contents:

• SQL ALTER TABLE statement to add a column to a table
• SQL ALTER TABLE statement to drop a column
• SQL Alter TABLE argument to alter or drop default value of a column
• SQL Modify TABLE statement to driblet default value of a column
• SQL ALTER Tabular array statement to add private column constraint
• SQL ALTER Tabular array argument to drop private cavalcade constraint
• SQL Change TABLE statement to change size and information type of a column
• SQL Modify TABLE statement to add or drop Chief KEY of a tabular array
• SQL Change Table statement to driblet existing PRIMARY Fundamental of a table
• SQL ALTER Table statement to add together or drib Strange Key of a tabular array
• SQL ALTER Tabular array argument to drop existing Strange KEY of a table
• SQL Modify TABLE argument to add CHECK CONSTRAINT
• SQL Alter TABLE argument to drop Bank check CONSTRAINT
• SQL ALTER TABLE statement to change PRIMARY KEY CONSTRAINT
• SQL Change TABLE statement to change Strange KEY CONSTRAINT

SQL Change TABLE argument to add a cavalcade to a tabular array

In the following topic, nosotros are discussing the SQL ALTER Tabular array statement, which adds a column to a table. If not specified otherwise, the column will be added at the end of the table.

Sample table: agent1

To add a new cavalcade 'email' at the end of the table 'agent1' with field name and data type

Field Name	Information Type	Size	Decimal Places	NULL	Constraint
email	char	25		No

the following SQL statement can be used :

SQL Lawmaking:

          Change TABLE agent1 ADD electronic mail char(25);                  

Output:

To see the modified structure of the said table:

SQL Code:

          Describe agent1;                  

Structure

SQL ALTER TABLE statement to drop a cavalcade

In the following example, we are discussing, how a column tin can be dropped from a table if it exists in the table, using the SQL Change TABLE statement.

Sample table: agent1

To drop the existing column 'land' from the table 'agent1', the following SQL statement can be used:

SQL Code:

          Alter TABLE agent1 Drib(land);                  

Output:

To meet the modified structure of the said table:

SQL Code:

          DESCRIBE agent1;                  

Structure

SQL ALTER Tabular array statement to alter or drop default value of a cavalcade

In the following example, we are going to discuss how to drop the default value of a cavalcade from a tabular array if the column exists in the table using SQL ALTER TABLE argument.

Example:

Sample table: agent1

To modify the existing column 'committee' of 'agent1' tabular array with a default value .05,

the following SQL statement tin be used:

SQL Code:

          ALTER Tabular array agent1  MODIFY commission DEFAULT .05;                  

Output:

To meet the modified construction of the said tabular array :

SQL Code:

          DESCRIBE agent1;                  

Construction

SQL ALTER TABLE statement to drop default value of a column

To drop the existing default value of column 'commission' of 'agent1' table, the post-obit SQL statement tin be used:

SQL Code:

          ALTER Tabular array agent1 MODIFY commission NUMBER;                  

Output:

SQL ALTER TABLE statement to add individual column constraint

In the following case, nosotros are going to discuss about the usage of SQL Modify TABLE statement to add a constraint for a column and also drop an existing constraint of a column from a table if the column exists in the table.

Sample table: agent1

To add a UNIQUE CONSTRAINT named 'dup_che_con' for the the existing column 'agent_code' of 'agent1' tabular array,

the following SQL statement can be used:

SQL Code:

          Modify TABLE agent1 ADD CONSTRAINT dup_che_con UNIQUE(agent_code);                  

Output:

SQL Alter TABLE statement to driblet private cavalcade constraint

To drop the existing UNIQUE CONSTRAINT 'dup_che_con' from the table 'agent1',

the post-obit SQL statement can be used:

SQL Lawmaking:

          Change Tabular array agent1 Drib CONSTRAINT dup_che_con;                  

Output:

SQL ALTER Table argument to alter size and information type of a column

In the post-obit case, we are going to hash out about the usage of SQL ALTER TABLE statement to change the size and information type of a cavalcade in an existing table, if the column exists in the tabular array.

Instance:

Sample table: agent1

To alter the datatype and size of the column 'country' of 'agent1' table, the following SQL statement can be used:

SQL Code:

          ALTER Table  agent1 Modify (country VARCHAR2(35));                  

Output:

SQL ALTER TABLE argument to add or drop PRIMARY KEY of a tabular array

In the following example, we are going to discuss about the usage of SQL Modify Tabular array statement to add together and drop primary fundamental of a tabular array.

Sample tabular array: agent1

To add a Principal KEY CONSTRAINT named 'pk_ag_code' for the column 'agent_code' of the 'agent1' tabular array, the following SQL statement can be used:

SQL Code:

          ALTER Table  agent1 Add CONSTRAINT pk_ag_code PRIMARY Fundamental(agent_code);                  

Output:

SQL ALTER TABLE statement to driblet existing Chief KEY of a tabular array

To drop the existing PRIMARY Primal CONSTRAINT named 'pk_ag_code' form the 'agent1' table, the following SQL argument can be used:

SQL Code:

          ALTER TABLE  agent1 Driblet CONSTRAINT pk_ag_code;                  

Output:

SQL ALTER TABLE statement to add or driblet FOREIGN KEY of a table

In the post-obit example, we are going to hash out most the usage of SQL Change Tabular array statement to add and drop foreign primal of a table.

Sample table: customer1

To add a Foreign Cardinal CONSTRAINT named 'fk_ag_code' for the column 'agent_code' of the 'customer1' tabular array, the following SQL statement can be used:

SQL Code:

          ALTER TABLE customer1 ADD CONSTRAINT  fk_ag_code FOREIGN KEY (agent_code) REFERENCES agents(agent_code);                  

Output:

SQL ALTER Table statement to driblet existing Strange Fundamental of a tabular array

To drop the existing Foreign Cardinal CONSTRAINT named 'fk_ag_code' course the 'customer1' tabular array, the following SQL statement can be used :

SQL Code:

          ALTER Tabular array  customer1 DROP CONSTRAINT fk_ag_code;                  

Output:

SQL Change TABLE argument to add Check CONSTRAINT

In the following example, we are going to talk over most the usage of SQL Modify Table argument to add and driblet CHECK CONSTRAINT of column(s) of a table.

Sample table : customer1

To add a CHECK CONSTRAINT named 'du_che_con' for the column 'grade' of the 'customer1' tabular array, which checks whether the values of 'grade' are within the range 1 to 3 at the time of inserting rows into the table, the post-obit SQL argument tin be used :

SQL Code:

          ALTER TABLE customer1 Add together CONSTRAINT du_che_con Bank check(grade>=1 AND grade<=3);                  

Output:

SQL ALTER TABLE argument to drop CHECK CONSTRAINT

To drop the existing Cheque CONSTRAINT named 'du_che_con' course the 'customer1' table, the following SQL statement tin be used :

SQL Code:

          Change TABLE customer1 DROP CONSTRAINT du_che_con;                  

Output:

SQL ALTER Table argument to modify Primary Central CONSTRAINT

In the following example, nosotros are going to discuss about the usage of SQL Change Tabular array statement to modify the PRIMARY KEY and FOREIGN Fundamental constraint.

To modify a PRIMARY Key and Strange Central constraint, firstly it is needed to remove the existing Primary KEY and FOREIGN KEY constraint and then re-create it.

Sample table : agent1

Suppose in that location is a PRIMARY KEY CONSTRAINT named 'pk_ag_code' for the column 'agent_code' of the 'agent1' table.

To modify the Chief Primal CONSTRAINT named 'pk_ag_code, the post-obit SQL statements tin be used :

SQL Code:

          Alter Tabular array  agent1 DROP CONSTRAINT pk_ag_code;                  

SQL Code:

          Modify Table  agent1 Add together CONSTRAINT pk_ag_code Master KEY(agent_code);                  

SQL ALTER TABLE statement to change Strange KEY CONSTRAINT

Sample tabular array: customer1

Suppose, in that location is a Strange KEY CONSTRAINT named 'fk_ag_code' for the column 'agent_code' of the 'customer1' table

To alter the FOREIGN KEY CONSTRAINT named 'fk_ag_code', the post-obit SQL statements can be used :

SQL Code:

          Alter Table customer1 Driblet CONSTRAINT  fk_ag_code;                  

SQL Lawmaking:

          Modify TABLE customer1  ADD CONSTRAINT fk_ag_code  Foreign KEY (agent_code)  REFERENCES agents(agent_code);                  

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

Practise SQL Exercises

• SQL Exercises, Do, Solution
• SQL Retrieve data from tables [33 Exercises]
• SQL Boolean and Relational operators [12 Exercises]
• SQL Wildcard and Special operators [22 Exercises]
• SQL Amass Functions [25 Exercises]
• SQL Formatting query output [10 Exercises]
• SQL Quering on Multiple Tables [7 Exercises]
• FILTERING and SORTING on 60 minutes Database [38 Exercises]
• SQL JOINS
• SQL JOINS [29 Exercises]
• SQL JOINS on HR Database [27 Exercises]
• SQL SUBQUERIES
• SQL SUBQUERIES [39 Exercises]
• SQL SUBQUERIES on HR Database [55 Exercises]
• SQL Matrimony[9 Exercises]
• SQL View[sixteen Exercises]
• SQL User Account Management [sixteen Exercise]
• Motion-picture show Database
• BASIC queries on motion-picture show Database [10 Exercises]
• SUBQUERIES on movie Database [sixteen Exercises]
• JOINS on movie Database [24 Exercises]
• Soccer Database
• Introduction
• BASIC queries on soccer Database [29 Exercises]
• SUBQUERIES on soccer Database [33 Exercises]
• JOINS queries on soccer Database [61 Exercises]
• Hospital Database
• Introduction
• Bones, SUBQUERIES, and JOINS [39 Exercises]
• Employee Database
• Basic queries on employee Database [115 Exercises]
• SUBQUERIES on employee Database [77 Exercises]
• More than to come!

Want to improve the in a higher place article? Contribute your Notes/Comments/Examples through Disqus.

Previous: Constraints
Side by side: SELECT Statement

Source: https://www.w3resource.com/sql/creating-and-maintaining-tables/sql-alter-table.php

Posted by: pratherfiefultoothe.blogspot.com

Share this post