SQL Server DROP TABLE
In SQL Server, the DROP TABLE statement is used to remove or delete permanently from the database. In SQL Server after performing the DROP Operation we cannot revoke the table or database again, because it is an irreversible action. The Drop Operation is different from the DELETE Command, In the DELETE command we can revoke the database or table after performing the DELETE Operation. Generally, we use the DROP TABLE command to delete or remove those tables that are useless.
Syntax:
DROP TABLE [IF EXISTS] table_name;
Explanation:
- DROP TABLE: The keyword used to delete a table from the database.
- IF EXISTS: This optional clause prevents an error from occurring if the table does not exist and If the table exists then it is dropped otherwise, nothing will happen.
- table_name: It is a table name that we want to drop from our database.
If we want to delete multiple tables at the same time then the below syntax would be follow.
DROP TABLE IF EXISTS table_name1, table_name2, table_name3;
DROP a Table That Doesn’t Exist
The following statement deletes the table named w3wiki.
DROP TABLE IF EXISTS w3wiki;
Explanation: In this example, this statement will delete the table named w3wiki, but if the table doesn’t exist, then it will not raise any error. because we used the IF EXISTS clause that ensures that the statement doesn’t fail in case the table doesn’t exist in the database and the statement will be executed successfully.
The Result Looks Like:
DROP a Single Table
Let’s create a new table named Student.
CREATE TABLE Student (
StdtId INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Dob DATE
);
Now, to delete the table Student, we will write the following statement.
DROP TABLE Student;
Explanation: This statement will delete the Student table from the database. It will delete all the records stored in the Student table.
DROP a Table With FOREIGN KEY Constraint
Now, we will create two tables named Student and Course in the University schema. where the student table has a FOREIGN KEY constraint referencing the CourseId column in the Course table.
CREATE SCHEMA University;
GO
CREATE TABLE University. Course
(
CourseId INT PRIMARY KEY,
CourseName VARCHAR(100)
);
CREATE TABLE University. Student
(
StudentId INT PRIMARY KEY IDENTITY (1,1),
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Age INT NOT NULL,
Address NVARCHAR(255) NOT NULL,
CourseId INT NOT NULL,
FOREIGN KEY (CourseId) REFERENCES University.Course (CourseId)
);
Now Let’s try to drop the Course table from the University schema.
DROP TABLE University.Course;
The Result Looks Like:
Explanation: In SQL Server, it is not allowed to delete a table that is referenced by foreign key constraints. If we want to remove this type of table, we have two options. either we have to delete foreign key constraints, or we have to delete referencing table first. In our example, we have to either remove the foreign key constraints from the table or we have to remove the University.Student table first before removing the University.Course table.
DROP TABLE University.Student;
DROP TABLE University.Course;
Explanation: In the above statement, we drop the referencing Student table first and then the Course table. So, it will be executed successfully.
The Result Looks Like:
After Command runConclusion
In conclusion, the DROP TABLE statement in SQL Server is a powerful command. It lets us fully erase a table with all its data from the database. Remember we should always use this statement with caution, as we can’t get a dropped table back. Before executing the DROP TABLE statement, we must confirm that we have the needed permissions. Also, we should check the existence of the table to prevent errors.