SQL DROP to remove table

DROP query used used to delete a table or a column or some other properties associated with the table.
To remove all records from a table use truncate sql command.

Deleting a Table

SQL DROP command to remove content table inside the database.
DROP TABLE content

Deleting a Column

Our column name here is dt .
ALTER TABLE `content`   DROP `dt`;


SQL delete table command by using DROP query and before deleting checking if TABLE exists

Checking before deleting

We can check whether table is there or not before giving any delete command. Without ensuring the presence of table ( non existence table ) delete command will generate an error message.
DROP TABLE IF EXISTS `content`;

Deleting multiple tables

We can use drop command to delete more than one table. Here is the command to remove 4 tables.
DROP TABLE `content`, `content_admin`, `content_cat`, `content_cmt_post`;
The above command will delete four tables.

Dropping a unique constraints

We can use DROP sql command to remove unique constraints associated to any column, here is an example.
ALTER TABLE 'content_cat' DROP INDEX  'cat_id'
The above command will remove the unique index associated with cat_id field of content_cat table
We can also use DROP command to delete a complete database. Here is the sample code
DROP DATABASE TEST

Difference Between DROP, TRUNCATE, and DELETE

  • DROP: Removes an entire table structure along with its data irreversibly (in most cases).
  • TRUNCATE: Quickly removes all rows from a table, more efficient than DELETE for clearing a table, but less flexible. Blank table with structure remains.
  • DELETE: Deletes rows specified by a WHERE clause, allowing for selective removal of data, but is slower for large datasets due to logging.

General Permissions Overview

  • Ownership: Usually, the owner of the table (the user who created it) has the inherent right to drop the table.
  • DBA Privileges: Database Administrators (DBAs) or users with equivalent privileges can drop any table in the database.
  • Explicit Permissions: A user needs specific permissions or roles granted explicitly to drop tables. For example, in some DBMS, the DROP privilege on the table is required.

DROP TABLE with CASCADE Constraints in SQL

The DROP TABLE statement is used in SQL to remove a table definition and all associated data, indexes, triggers, constraints, and permission specifications from the database. When you add CASCADE CONSTRAINTS (or a similar option, depending on the SQL dialect) to a DROP TABLE statement, it instructs the database to automatically drop any objects that depend on the table being dropped, such as foreign keys or other dependent objects.

Usage

The exact syntax for using DROP TABLE with cascade constraints can vary between different database management systems (DBMS). Here’s a general look at how it might appear:
DROP TABLE table_name CASCADE CONSTRAINTS;
This command does the following:
  • Drops the table: The specified table is removed from the database.
  • Cascades the effect: Any constraints that depend on the table, especially foreign key constraints in other tables that reference the dropped table, are also automatically dropped.

Recovery operations

Flashback Technology (Oracle)

Oracle databases offer Flashback Technology, which allows you to query past states of the database and can be used to recover from accidental data modifications or deletions, including dropped tables. This requires that Flashback Technology was enabled before the table was dropped.

Point-In-Time Recovery (PITR)

Some databases support PITR, allowing you to restore the database to a specific moment in time before the `DROP TABLE` command was executed. This method is useful if the exact time of the drop is known and the database supports PITR.

PHP Code to Drop table

MySQLi database connection string
<?Php
require "config.php";// Database connection

$query="DROP TABLE dt_tb ";
if($connection->query($query)){
  echo "Table deleted.";
}else{
echo $connection->error;
}
?>

PDO Database connection is available at config.php file.
require "config.php"; // database connection 
$sql=$dbo->prepare("DROP TABLE  student_del ");

$sql->execute();
After the query execution we will add message saying success or print error message in case of failure.
<?Php
require "config.php"; // database connection 
$sql=$dbo->prepare("DROP TABLE  student_del ");

if($sql->execute()){
echo " Table deleted ";
}else{
print_r($sql->errorInfo()); 
}
?>
SQL References How to delete Records in different tables

Questions


Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer