In this short article I show quick syntax for dropping tables and deleting rows from database. This has been tested on Microsoft SQL Server so this might differ if you are using Oracles or MySQL database management systems (DBMS).
Drop
Dropping tables is most common when building up your database and setting test environments. The syntax is easy to learn, but there are some catches. First if you have used foreign keys there are actual order how to drop tables. Syntax for dropping table is
DROP TABLE table_name;
Delete
Deleting or should I say removing particular rows from database can also be easy to learn. The syntax isn’t hard but once again there needs to be some more logic used here also. Here is an example: In your database you use id-tags in order to identify certain rows from each other. It happens that the name of the two items is the same but the id-tags aren’t and you wan’t to remove one of them. Then you might already made sql-command
DELETE FROM Item WHERE name='Wooden chair';
What happens now is that all the items that have name Wooden chair from table Item will be removed, but this isn’t what you wanted. You wanted only to remove one item not two items. Much more efficient way is to remove item from table via its own id-tag. So you might want to use statment
DELETE FROM Item WHERE id=54;
Overview
Deleting and dropping are powerful commands and they should be used with care. When making these kind of statements one should thing more that are you doing the correct way it.
– Tuomas Törmä
Sources
- Introduction to Database Systems lectures At Haaga-Helia autumn 2013 teacher Tanja Bergius.
Tuomas Törmä 