2 Effective SQL Queries to Drop or Modify a Table

SQL query saves your time if you write it correctly. Below are the two effective SQL queries to drop or modify a table.

Don’t Miss

  • Selectively you can fetch odd and even records from table. Here’s SQL query helpful.
  • SQL data types CHAR Vs VARCHAR the top differences.
  • Sub-queries helpful to fetch data from more than one table. Check out how to write complex SQL sub-query

Drop a table

The widely used SQL query to drop a table syntax is as below.

DROP TABLE [<Schema>.] <Table_Name> [CASCADE CONSTRAINTS];

Explanation

  • In place of TABLE_NAME, use your table name
  • In place of schema, use your schema
  • The Cascade constraints drop all referential integrity constraints that refer to primary and unique keys in the dropped table

Example

I am here deleting the HR_OPER table from the database. I used CASCADE constraints, which deletes all the constraints.

DROP TABLE HR_OPER CASCADE CONSTRAINTS;
Drop a table
Drop a table

Modify the table

The statement ALTER TABLE you can use to modify a table. Below are the possible ALTER TABLE operations you can apply to a Table.

Basically, the alter table modifies a table.

Syntax for alter table
Syntax to alter a table

Example

ALTER TABLE Student ADD StudHall# CHAR (4) ADD FOREIGN KEY (StudHall#) REFERENCES Hall (Hall#);

Rename and truncate

As part of modifying a table, you can rename an existing table. In addition to that, you can truncate all the records in a table. We will see with syntax how you can do those.

The syntax for renaming and truncating

Syntax for rename and truncate
The syntax to rename and truncate a table

Example

RENAME HR1 TO HR2;
TRUNCATE TABLE HR_OPER;

Related posts

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.