The DROP TABLE statement. Removing tables
Before studying this topic, it is recommended that you familiarize yourself with the following topic:
Contents
- 1. The DROP TABLE statement. General concepts. Declaration syntax
- 2. Restrictions on using the DROP TABLE statement
- 3. An example of using the DROP TABLE statement. Dropping a table
- 4. Example of moving data from one table to another
- Related topics
Search other resources:
1. The DROP TABLE statement. General concepts. Declaration syntax
In T-SQL, the DROP TABLE statement is used to drop a table. The operator can delete from one to several tables. In addition to the tables themselves, all related to them are deleted:
- data;
- indices;
- triggers;
- restrictions;
- permission specifications.
In a simplified form, the syntax for using the DROP TABLE statement is as follows
DROP TABLE [Table_Name]
here
- Table_Name – the name of the table to be deleted.
It is possible that a table has already been dropped and DROP TABLE is called for it. In this case, the system will generate an error. To avoid the error, you need to use the statement form using the IF EXISTS keyword combination:
DROP TABLE IF EXISTS [Table_Name]
here
- Table_Name – the name of the table to be deleted.
In this case, when trying to delete a non-existent table, the T-SQL program will not generate an error and will continue its execution.
⇑
2. Restrictions on using the DROP TABLE statement
The following restrictions apply when using the DROP TABLE statement.
- The DROP TABLE statement cannot be used to drop a table referenced by a FOREIGN KEY constraint. In this case, you must first remove this constraint or delete the referenced table.
- If you need to delete two tables related by foreign and primary keys, then the table that refers to is deleted first (has a foreign key FOREIGN KEY).
- Deleting a table removes all rules or defaults, triggers, and constraints associated with that table. Recreating the table will require building new rules, triggers, constraints, and defaults.
⇑
3. An example of using the DROP TABLE statement. Dropping a table
The example uses the CREATE TABLE statement to first create a test table named MyTable that has the following fields:
- field A of type INT. This is the primary key. The field has a NOT NULL constraint;
- field B of type FLOAT;
- field C of type VARCHAR(20).
/* DROP TABLE statement - Deleting a table */ /* 1. Create table MyTable */ CREATE TABLE [MyTable] ( A INT NOT NULL PRIMARY KEY, B FLOAT, C VARCHAR(20), ); /* Result. ----------------------------- | A | B | C | ----------------------------- | INT | FLOAT | VARCHAR(20) | ----------------------------- */ /* 2. Delete table MyTable */ DROP TABLE IF EXISTS [MyTable]
⇑
4. Example of moving data from one table to another
Task.
Given a table Student containing fields and filled with some data. The table fields are as follows:
- ID_Student – counter field, unique identifier, primary key. The field has a NOT NULL constraint;
- Name – student’s name. The field has a NOT NULL constraint;
- Address – student’s address;
- BirthDate – date of birth;
- PhoneNumber – phone number. The field has a NOT NULL constraint.
It is necessary to implement copying data from the Student table to the Aspirant table. The Aspirant destination table has the following fields:
- ID_Aspirant – counter field, unique identifier, primary key. The field has a NOT NULL constraint;
- Name – the name of the postgraduate student;
- Address – address of student;
- BirthDate – date of birth;
- PhoneNumber – phone number.
After copying, the original Student table must be deleted.
Solution.
The sequence of actions when copying data is as follows:
- Create the Student table using the CREATE TABLE statement.
- Write data to the Student table. To do this, you can use the INSERT INTO + VALUES statement.
- Display the Student table for control (SELECT* statement).
- Create the Aspirant table with the CREATE TABLE statement.
- Copy the data from the Student table to the Aspirant table. Here the INSERT INTO+SELECT+FROM statement is used.
- Drop the Student table with the DROP TABLE statement.
- Display the Aspirant table as a result (SELECT* statement).
The text of the program in T-SQL is as follows
/* DROP TABLE statement - Deleting table */ /* 1. Create the Student table */ CREATE TABLE [Student] ( ID_Student INT NOT NULL PRIMARY KEY, [Name] VARCHAR(30) NOT NULL, [Address] VARCHAR(100), BirthDate DATE, PhoneNumber VARCHAR(20) NOT NULL ); /* Result. --------------------------------------------------------------------- | ID_Student | Name | Address | BirthDate | PhoneNumber | --------------------------------------------------------------------- | INT | VARCHAR(20) | VARCHAR(100) | DATE | VARCHAR(20) | --------------------------------------------------------------------- */ /* 2. Write data to the Student table */ INSERT INTO [Student] ( ID_Student, [Name], [Address], BirthDate, PhoneNumber ) VALUES ( 1, 'Ivanov I.I.', 'Kiev', '08.08.2000', '777-7777' ) INSERT INTO [Student] ( ID_Student, [Name], [Address], BirthDate, PhoneNumber ) VALUES ( 2, 'Petrenko P.P.', 'Lviv', '11.05.2001', '555-5555' ) INSERT INTO [Student] ( ID_Student, [Name], [Address], BirthDate, PhoneNumber ) VALUES ( 3, 'Sidorenko S.S.', 'London', '20.01.2037', '444-444' ) /* Result. --------------------------------------------------------------------- | ID_Student | Name | Address | BirthDate | PhoneNumber | --------------------------------------------------------------------- | 1 | Ivanov I.I. | Kiev | 08.08.2000| 777-7777 | | 2 |Petrenko P.P. | Lviv | 11.05.2001| 555-5555 | | 3 |Sidorenko S.S.| London | 20.01.2037| 444-4444 | --------------------------------------------------------------------- */ /* 3. Pull data from Student table for control */ SELECT * FROM [Student] /* 4. Create the Aspirant table */ CREATE TABLE [Aspirant] ( ID_Aspirant INT NOT NULL PRIMARY KEY, [Name] VARCHAR(30) NOT NULL, [Address] VARCHAR(100), BirthDate DATE, PhoneNumber VARCHAR(20) NOT NULL ) /* Result. --------------------------------------------------------------------- | ID_Aspirant | Name | Address | BirthDate | PhoneNumber | --------------------------------------------------------------------- | INT | VARCHAR(20) | VARCHAR(100) | DATE | VARCHAR(20) | --------------------------------------------------------------------- */ /* 5. Copy data from table Aspirant to table Student */ INSERT INTO [Aspirant] ( ID_Aspirant, [Name], [Address], BirthDate, PhoneNumber ) SELECT * FROM [Student] /* 6. Drop the Student table with the DROP TABLE statement */ DROP TABLE [Student] /* 7. Display the Aspirant table */ SELECT * FROM Aspirant /* Result. --------------------------------------------------------------------- | ID_Aspirant| Name | Address | BirthDate | PhoneNumber | --------------------------------------------------------------------- | 1 | Ivanov I.I. | Kiev | 08.08.2000| 777-7777 | | 2 |Petrenko P.P. | Lviv | 11.05.2001| 555-5555 | | 3 |Sidorenko S.S.| London | 20.01.2037| 444-4444 | --------------------------------------------------------------------- */
Figure 1 shows the result of running the program in Microsoft SQL Server Management Studio.
Figure 1. Transferring data from one table to another. Program result
⇑
Related topics
⇑