SQL ALTER TABLE
ALTER TABLE
The SQL ALTER TABLE command is used to change the structure of an existing table. It helps to add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself.
It can also be used to change the comment for the table and type of the table.
Syntax:
ALTER TABLE <table_name>( ADD column1 data_type[(size)], ADD column2 data_type[(size)], ...);
Parameters:
| Name | 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 column 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 statement to change or drop default value of a column
- SQL ALTER TABLE statement to drop default value of a column
- SQL ALTER TABLE statement to add individual column constraint
- SQL ALTER TABLE statement to drop individual column constraint
- SQL ALTER TABLE statement to change size and data type of a column
- SQL ALTER TABLE statement to add or drop PRIMARY KEY of a table
- SQL ALTER TABLE statement to drop existing PRIMARY KEY of a table
- SQL ALTER TABLE statement to add or drop FOREIGN KEY of a table
- SQL ALTER TABLE statement to drop existing FOREIGN KEY of a table
- SQL ALTER TABLE statement to add CHECK CONSTRAINT
- SQL ALTER TABLE statement to drop CHECK CONSTRAINT
- SQL ALTER TABLE statement to change PRIMARY KEY CONSTRAINT
- SQL ALTER TABLE statement to change FOREIGN KEY CONSTRAINT
SQL ALTER TABLE statement to add a column to a table
In the following topic, we are discussing the SQL ALTER TABLE 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 column 'email' at the end of the table 'agent1' with field name and data type
| Field Name | Data Type | Size | Decimal Places | NULL | Constraint |
|---|---|---|---|---|---|
| char | 25 | No |
the following SQL statement can be used :
SQL Code:
ALTER TABLE agent1
-- Add a new column named 'email' with a data type of CHAR(25)
ADD email CHAR(25);
Explanation:
- ALTER TABLE agent1: Begins the SQL statement to alter the structure of the existing table named 'agent1'.
- ADD email CHAR(25);: Adds a new column named 'email' to the 'agent1' table. The 'email' column is defined with a data type of CHAR and a maximum length of 25 characters.
Output:
To see the modified structure of the said table:
SQL Code:
DESCRIBE agent1;
Structure
SQL ALTER TABLE statement to drop a column
In the following example, we are discussing, how a column can be dropped from a table if it exists in the table, using the SQL ALTER TABLE statement.
Sample table: agent1+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
To drop the existing column 'country' from the table 'agent1', the following SQL statement can be used:
SQL Code:
ALTER TABLE agent1
-- Drop the column named 'country' from the table 'agent1'
DROP COLUMN country;
Explanation:
- ALTER TABLE agent1: Begins the SQL statement to alter the structure of the existing table named 'agent1'.
- DROP COLUMN country;: Specifies the action to drop the column named 'country' from the 'agent1' table.
Output:
To see the modified structure of the said table:
SQL Code:
DESCRIBE agent1;
Structure
SQL ALTER TABLE statement to change or drop default value of a column
In the following example, we are going to discuss how to drop the default value of a column from a table if the column exists in the table using SQL ALTER TABLE statement.
Example:
Sample table : agent1+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
To modify the existing column 'commission' of 'agent1' table with a default value .05,
the following SQL statement can be used:
SQL Code:
ALTER TABLE agent1
-- Modify the default value of the column 'commission' in the table 'agent1'
MODIFY commission DEFAULT .05;
Explanation:
- ALTER TABLE agent1: Begins the SQL statement to alter the structure of the existing table named 'agent1'.
- MODIFY commission DEFAULT .05;: Specifies the action to modify the 'commission' column in the 'agent1' table. The DEFAULT .05 part sets the default value of the 'commission' column to 0.05.
Output:
To see the modified structure of the said table :
SQL Code:
DESCRIBE agent1;
Structure
SQL ALTER TABLE statement to drop default value of a column
To drop the existing default value of column 'commission' of 'agent1' table, the following SQL statement can be used:
SQL Code:
ALTER TABLE agent1
-- Modify the data type of the column 'commission' in the table 'agent1'
MODIFY commission NUMBER;
Explanation:
- ALTER TABLE agent1: Begins the SQL statement to alter the structure of the existing table named 'agent1'.
- MODIFY commission NUMBER;: Specifies the action to modify the 'commission' column in the 'agent1' table. In this case, it changes the data type of the 'commission' column to NUMBER.
Output:
SQL ALTER TABLE statement to add individual column constraint
In the following example, we are going to discuss about the usage of SQL ALTER 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+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
To add a UNIQUE CONSTRAINT named 'dup_che_con' for the the existing column 'agent_code' of 'agent1' table,
the following SQL statement can be used:
SQL Code:
ALTER TABLE agent1
-- Add a unique constraint named 'dup_che_con' to the 'agent_code' column in the 'agent1' table
ADD CONSTRAINT dup_che_con UNIQUE(agent_code);
Explanation:
- ALTER TABLE agent1: Begins the SQL statement to alter the structure of the existing table named 'agent1'.
- ADD CONSTRAINT dup_che_con UNIQUE(agent_code);: Specifies the action to add a new constraint to the 'agent1' table. The UNIQUE(agent_code) part defines a unique constraint named 'dup_che_con' on the 'agent_code' column. This constraint ensures that each value in the 'agent_code' column must be unique across all rows in the table.
Output:
SQL ALTER TABLE statement to drop individual column constraint
To drop the existing UNIQUE CONSTRAINT 'dup_che_con' from the table 'agent1',
the following SQL statement can be used:
SQL Code:
ALTER TABLE agent1
-- Drop the constraint named 'dup_che_con' from the 'agent1' table
DROP CONSTRAINT dup_che_con;
Explanation:
- ALTER TABLE agent1: Begins the SQL statement to alter the structure of the existing table named 'agent1'.
- DROP CONSTRAINT dup_che_con;: Specifies the action to drop the constraint named 'dup_che_con' from the 'agent1' table.
Output:
SQL ALTER TABLE statement to change size and data type of a column
In the following example, we are going to discuss about the usage of SQL ALTER TABLE statement to change the size and data type of a column in an existing table, if the column exists in the table.
Example:
Sample table: agent1+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
To modify the datatype and size of the column 'country' of 'agent1' table, the following SQL statement can be used:
SQL Code:
ALTER TABLE agent1
-- Modify the data type of the 'country' column in the 'agent1' table
MODIFY (country VARCHAR2(35));
Explanation:
- ALTER TABLE agent1: Begins the SQL statement to alter the structure of the existing table named 'agent1'.
- MODIFY (country VARCHAR2(35));: Specifies the action to modify the 'country' column in the 'agent1' table. The VARCHAR2(35) part changes the data type of the 'country' column to VARCHAR2 with a maximum length of 35 characters.
Output:
SQL ALTER TABLE statement to add or drop PRIMARY KEY of a table
In the following example, we are going to discuss about the usage of SQL ALTER TABLE statement to add and drop primary key of a table.
Sample table: agent1+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
To add a PRIMARY KEY CONSTRAINT named 'pk_ag_code' for the column 'agent_code' of the 'agent1' table, the following SQL statement can be used:
SQL Code:
ALTER TABLE agent1
-- Add a primary key constraint named 'pk_ag_code' to the 'agent_code' column in the 'agent1' table
ADD CONSTRAINT pk_ag_code PRIMARY KEY(agent_code);
Explanation:
- ALTER TABLE agent1: Begins the SQL statement to alter the structure of the existing table named 'agent1'.
- ADD CONSTRAINT pk_ag_code PRIMARY KEY(agent_code);: Specifies the action to add a new constraint to the 'agent1' table. The PRIMARY KEY(agent_code) part defines a primary key constraint named 'pk_ag_code' on the 'agent_code' column. This constraint indicates that the 'agent_code' column will uniquely identify each row in the 'agent1' table.
Output:
SQL ALTER TABLE statement to drop existing PRIMARY KEY of a table
To drop the existing PRIMARY KEY CONSTRAINT named 'pk_ag_code' form the 'agent1' table, the following SQL statement can be used:
SQL Code:
ALTER TABLE agent1
-- Drop the primary key constraint named 'pk_ag_code' from the 'agent1' table
DROP CONSTRAINT pk_ag_code;
Explanation:
- ALTER TABLE agent1: Begins the SQL statement to alter the structure of the existing table named 'agent1'.
- DROP CONSTRAINT pk_ag_code;: Specifies the action to drop the constraint named 'pk_ag_code' from the 'agent1' table. This constraint was previously defined as the primary key constraint on the 'agent_code' column.
Output:
SQL ALTER TABLE statement to add or drop FOREIGN KEY of a table
In the following example, we are going to discuss about the usage of SQL ALTER TABLE statement to add and drop foreign key of a table.
Sample table: customer1+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
To add a FOREIGN KEY CONSTRAINT named 'fk_ag_code' for the column 'agent_code' of the 'customer1' table, the following SQL statement can be used:
SQL Code:
ALTER TABLE customer1
-- Add a foreign key constraint named 'fk_ag_code' to the 'agent_code' column in the 'customer1' table
ADD CONSTRAINT fk_ag_code
-- Specify the foreign key constraint, referencing the 'agent_code' column in the 'agents' table
FOREIGN KEY (agent_code)
REFERENCES agents(agent_code);
Explanation:
- ALTER TABLE customer1: Begins the SQL statement to alter the structure of the existing table named 'customer1'.
- ADD CONSTRAINT fk_ag_code: Specifies the action to add a new constraint to the 'customer1' table. The name 'fk_ag_code' is given to the foreign key constraint.
- FOREIGN KEY (agent_code): Indicates that the constraint being added is a foreign key constraint, and it applies to the 'agent_code' column in the 'customer1' table.
- REFERENCES agents(agent_code): Specifies that the foreign key constraint references the 'agent_code' column in the 'agents' table. This means that the values in the 'agent_code' column of the 'customer1' table must exist in the 'agent_code' column of the 'agents' table.
Output:
SQL ALTER TABLE statement to drop existing FOREIGN KEY of a table
To drop the existing FOREIGN KEY CONSTRAINT named 'fk_ag_code' form the 'customer1' table, the following SQL statement can be used :
SQL Code:
ALTER TABLE customer1
-- Drop the foreign key constraint named 'fk_ag_code' from the 'customer1' table
DROP CONSTRAINT fk_ag_code;
Explanation:
- ALTER TABLE customer1: Begins the SQL statement to alter the structure of the existing table named 'customer1'.
- DROP CONSTRAINT fk_ag_code;: Specifies the action to drop the constraint named 'fk_ag_code' from the 'customer1' table. This constraint was previously defined as a foreign key constraint.
Output:
SQL ALTER TABLE statement to add CHECK CONSTRAINT
In the following example, we are going to discuss about the usage of SQL ALTER TABLE statement to add and drop CHECK CONSTRAINT of column(s) of a table.
Sample table : customer1+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
To add a CHECK CONSTRAINT named 'du_che_con' for the column 'grade' of the 'customer1' table, which checks whether the values of 'grade' are within the range 1 to 3 at the time of inserting rows into the table, the following SQL statement can be used :
SQL Code:
ALTER TABLE customer1
-- Add a check constraint named 'du_che_con' to the 'customer1' table
ADD CONSTRAINT du_che_con
-- Specify the check constraint ensuring 'grade' values are between 1 and 3
CHECK (grade >= 1 AND grade <= 3);
Explanation:
- ALTER TABLE customer1: Begins the SQL statement to alter the structure of the existing table named 'customer1'.
- ADD CONSTRAINT du_che_con: Specifies the action to add a new constraint to the 'customer1' table. The name 'du_che_con' is given to the check constraint.
- CHECK (grade >= 1 AND grade <= 3): Defines the check constraint 'du_che_con'. This constraint ensures that the values in the 'grade' column must be between 1 and 3. If any value violates this condition, the constraint will prevent the operation.
Output:
SQL ALTER TABLE statement to drop CHECK CONSTRAINT
To drop the existing CHECK CONSTRAINT named 'du_che_con' form the 'customer1' table, the following SQL statement can be used :
SQL Code:
ALTER TABLE customer1
-- Drop the check constraint named 'du_che_con' from the 'customer1' table
DROP CONSTRAINT du_che_con;
Explanation:
- ALTER TABLE customer1: Begins the SQL statement to alter the structure of the existing table named 'customer1'.
- DROP CONSTRAINT du_che_con;: Specifies the action to drop the constraint named 'du_che_con' from the 'customer1' table. This constraint was previously defined as a check constraint.
Output:
SQL ALTER TABLE statement to change PRIMARY KEY CONSTRAINT
In the following example, we are going to discuss about the usage of SQL ALTER TABLE statement to modify the PRIMARY KEY and FOREIGN KEY constraint.
To modify a PRIMARY KEY and FOREIGN KEY constraint, firstly it is needed to remove the existing PRIMARY KEY and FOREIGN KEY constraint and then re-create it.
Sample table : agent1+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
Suppose there is a PRIMARY KEY CONSTRAINT named 'pk_ag_code' for the column 'agent_code' of the 'agent1' table.
To modify the PRIMARY KEY CONSTRAINT named 'pk_ag_code, the following SQL statements can be used :
SQL Code:
ALTER TABLE agent1
-- Drop the primary key constraint named 'pk_ag_code' from the 'agent1' table
DROP CONSTRAINT pk_ag_code;
Explanation:
- ALTER TABLE agent1: Begins the SQL statement to alter the structure of the existing table named 'agent1'.
- DROP CONSTRAINT pk_ag_code;: Specifies the action to drop the constraint named 'pk_ag_code' from the 'agent1' table. This constraint was previously defined as a primary key constraint.
SQL Code:
ALTER TABLE agent1
-- Add a primary key constraint named 'pk_ag_code' to the 'agent_code' column in the 'agent1' table
ADD CONSTRAINT pk_ag_code PRIMARY KEY(agent_code);
Explanation:
- ALTER TABLE agent1: Begins the SQL statement to alter the structure of the existing table named 'agent1'.
- ADD CONSTRAINT pk_ag_code PRIMARY KEY(agent_code);: Specifies the action to add a new constraint to the 'agent1' table. The constraint is named 'pk_ag_code', and it's defined as a primary key constraint (PRIMARY KEY). This means that the 'agent_code' column will be the primary key of the table.
SQL ALTER TABLE statement to change FOREIGN KEY CONSTRAINT
Sample table: customer1+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ |CUST_CODE | CUST_NAME | CUST_CITY | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO | AGENT_CODE | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+ | C00013 | Holmes | London | London | UK | 2 | 6000.00 | 5000.00 | 7000.00 | 4000.00 | BBBBBBB | A003 | | C00001 | Micheal | New York | New York | USA | 2 | 3000.00 | 5000.00 | 2000.00 | 6000.00 | CCCCCCC | A008 | | C00020 | Albert | New York | New York | USA | 3 | 5000.00 | 7000.00 | 6000.00 | 6000.00 | BBBBSBB | A008 | | C00025 | Ravindran | Bangalore | Bangalore | India | 2 | 5000.00 | 7000.00 | 4000.00 | 8000.00 | AVAVAVA | A011 | | C00024 | Cook | London | London | UK | 2 | 4000.00 | 9000.00 | 7000.00 | 6000.00 | FSDDSDF | A006 | | C00015 | Stuart | London | London | UK | 1 | 6000.00 | 8000.00 | 3000.00 | 11000.00 | GFSGERS | A003 | | C00002 | Bolt | New York | New York | USA | 3 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | DDNRDRH | A008 | | C00018 | Fleming | Brisban | Brisban | Australia | 2 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | NHBGVFC | A005 | | C00021 | Jacks | Brisban | Brisban | Australia | 1 | 7000.00 | 7000.00 | 7000.00 | 7000.00 | WERTGDF | A005 | | C00019 | Yearannaidu | Chennai | Chennai | India | 1 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | ZZZZBFV | A010 | | C00005 | Sasikant | Mumbai | Mumbai | India | 1 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | 147-25896312 | A002 | | C00007 | Ramanathan | Chennai | Chennai | India | 1 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | GHRDWSD | A010 | | C00022 | Avinash | Mumbai | Mumbai | India | 2 | 7000.00 | 11000.00 | 9000.00 | 9000.00 | 113-12345678 | A002 | | C00004 | Winston | Brisban | Brisban | Australia | 1 | 5000.00 | 8000.00 | 7000.00 | 6000.00 | AAAAAAA | A005 | | C00023 | Karl | London | London | UK | 0 | 4000.00 | 6000.00 | 7000.00 | 3000.00 | AAAABAA | A006 | | C00006 | Shilton | Torento | Torento | Canada | 1 | 10000.00 | 7000.00 | 6000.00 | 11000.00 | DDDDDDD | A004 | | C00010 | Charles | Hampshair | Hampshair | UK | 3 | 6000.00 | 4000.00 | 5000.00 | 5000.00 | MMMMMMM | A009 | | C00017 | Srinivas | Bangalore | Bangalore | India | 2 | 8000.00 | 4000.00 | 3000.00 | 9000.00 | AAAAAAB | A007 | | C00012 | Steven | San Jose | San Jose | USA | 1 | 5000.00 | 7000.00 | 9000.00 | 3000.00 | KRFYGJK | A012 | | C00008 | Karolina | Torento | Torento | Canada | 1 | 7000.00 | 7000.00 | 9000.00 | 5000.00 | HJKORED | A004 | | C00003 | Martin | Torento | Torento | Canada | 2 | 8000.00 | 7000.00 | 7000.00 | 8000.00 | MJYURFD | A004 | | C00009 | Ramesh | Mumbai | Mumbai | India | 3 | 8000.00 | 7000.00 | 3000.00 | 12000.00 | Phone No | A002 | | C00014 | Rangarappa | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | AAAATGF | A001 | | C00016 | Venkatpati | Bangalore | Bangalore | India | 2 | 8000.00 | 11000.00 | 7000.00 | 12000.00 | JRTVFDD | A007 | | C00011 | Sundariya | Chennai | Chennai | India | 3 | 7000.00 | 11000.00 | 7000.00 | 11000.00 | PPHGRTS | A010 | +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
Suppose, there is a FOREIGN KEY CONSTRAINT named 'fk_ag_code' for the column 'agent_code' of the 'customer1' table
To modify the FOREIGN KEY CONSTRAINT named 'fk_ag_code', the following SQL statements can be used :
SQL Code:
ALTER TABLE customer1
-- Drop the foreign key constraint named 'fk_ag_code' from the 'customer1' table
DROP CONSTRAINT fk_ag_code;
Explanation:
- ALTER TABLE customer1: Begins the SQL statement to alter the structure of the existing table named 'customer1'.
- DROP CONSTRAINT fk_ag_code;: Specifies the action to drop the constraint named 'fk_ag_code' from the 'customer1' table. This constraint was previously defined as a foreign key constraint.
SQL Code:
ALTER TABLE customer1
-- Add a foreign key constraint named 'fk_ag_code' to the 'agent_code' column in the 'customer1' table
ADD CONSTRAINT fk_ag_code
-- Specify the foreign key constraint, referencing the 'agent_code' column in the 'agents' table
FOREIGN KEY (agent_code)
REFERENCES agents(agent_code);
Explanation:
- ALTER TABLE customer1: Initiates an alteration on the structure of the existing table named 'customer1'.
- ADD CONSTRAINT fk_ag_code: Specifies the action to add a new constraint to the 'customer1' table. The name 'fk_ag_code' is assigned to the foreign key constraint.
- FOREIGN KEY (agent_code): Defines the foreign key constraint on the 'agent_code' column in the 'customer1' table.
- REFERENCES agents(agent_code): Specifies that the foreign key constraint references the 'agent_code' column in the 'agents' table. This constraint ensures that the values in the 'agent_code' column of the 'customer1' table must exist in the 'agent_code' column of the 'agents' table.
Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: Constraints
Next: SELECT Statement