Here are some examples of dropping or deleting a table in Oracle SQL. Oracle does not include this as part of the DROP TABLE keyword. If you are deleting a table, but want to make sure that nothing relies on it before it is deleted, the RESTRICT clause will prevent the deletion from occurring if there are any PostgreSQL objects that depend on the table: DROP TABLE table_name RESTRICT įollowing from the above pets/owners example, an attempt to delete the owners table while the pets table exists and references it with a foreign key would result in the attempt to delete the owners table being denied. Some databases support the SQL drop table if exists feature: MySQL, SQL Server, and PostgreSQL. In summary, to drop a constraint in PostgreSQL, you can use the ALTER TABLE command followed by the DROP CONSTRAINT clause, specifying the name of the constraint that you want to drop. DROP TABLE table_name CASCADE įor example, if you had a table called pets that references a table called owners, dropping the owners table with the CASCADE statement would also delete the pets table.ĭoing this is quite dangerous, and generally not advised – deleting data should be done carefully and methodically in production so that data intended to be kept is not lost. It is possible to delete a table, as well as all tables that reference it using foreign keys. This can be avoided by only dropping the table if it exists: DROP TABLE IF EXISTS table_name If the WHERE clause is absent, the effect is to delete all rows in the table. If you try to delete a table that does not exist, you’ll get an error. DELETE deletes rows that satisfy the WHERE clause from the specified table. Multiple tables can be dropped in a single command by listing them: DROP TABLE table_1, table_2 Ĭhecking if the Table Exists Before Deleting The table and all data in it will be deleted at the execution of the command. The syntax is as follows: DROP TABLE table_name The DROP TABLE statement is used in PostgreSQL to delete tables. How to Drop a PostgreSQL Table in a Database using DROP TABLE Deleting cannot be undone (unless you have a backup to restore). Next, make sure you’re logged in as the default postgresql admin user, or a user with permission to delete the table in question.Īnd finally, make sure you’re sure of the name of both the table and the database that it’s in before you delete it, and that there’s nothing you need to keep in it, and that no other databases, table relationships, or systems are relying on it. There are a few things you should check before you try and destroy a table in PostgreSQL, especially if you’re working on a production database that is serving users.įirst and most important, take a backup of your PostgreSQL server – just in case you delete the wrong table, or change your mind later. This short tutorial will show you how to completely delete/destroy/drop a table in PostgreSQL using the DROP TABLE statement, and provide examples.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |