DROP

Using the DROP command we can delete the database or any objects associated with it.

1. DROP DATABASE

Using the following command we can delete our entire database. Before running this command, you should be aware that it will delete your entire database including all tables, data, indexes, and other elements, so make sure you're certain before proceeding.

1DROP DATABASE musicstore;

2. DROP TABLE

Similar to dropping a database, when we drop a table everything will be deleted.

1DROP TABLE albums;

3. DROP VIEW

And to drop a view.

1DROP VIEW DE_view;

3. DROP COLUMN

Let's say we want to delete a specific column. In this case we use the ALTER TABLE command in conjunction with the DROP command. As the owner of the music store, you determine that the column year_published is no longer necessary in this table and decide to remove it.

1ALTER TABLE albums
2DROP COLUMN year_published;

4. DROP CONSTRAINT

Another useful feature of this command is the ability to remove constraints. Below we will go through the syntax for removing all constraints with the command DROP.

1-- Drop the Primary Key
2ALTER TABLE albums
3DROP PRIMARY KEY;
4
5-- Drop the Foreign Key
6ALTER TABLE albums
7DROP FOREIGN KEY FK_AlbumOrder;
8
9-- Drop a Unique constraint 
10ALTER TABLE albums
11DROP INDEX UC_Album;
12
13-- Drop the Check constraint
14ALTER TABLE albums
15DROP CONTRAINT CHK_AlbumPrice;
16
17-- Drop the Default constraint
18ALTER TABLE abums
19ALTER genre DROP DEFAULT;

5. DROP INDEX

Imagine we no longer have as much albums in our table therefore there is no real need for the index we created in the first part of this tutorial. We can remove the index using the DROP command.

1ALTER TABLE album
2DROP INDEX index_album;