ALTER TABLE IF EXISTS customersĭROP COLUMN IF EXISTS customer_unique_id Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) Drop Column From Parent TableĪs you can see here, we got an error along with the error detail saying that column can not be dropped because there is a field on another table that depends on the column we are trying to delete. Therefore, we will try to delete the ‘customer_unique_id’ from the ‘customers’ table. Here, the customer_unique_id column of the ‘customers’ table is a dependency for the ‘order’ table. Let’s create another table called ‘orders’ and refer it to the ‘customers’ table.įOREIGN KEY (customer_unique_id) REFERENCES customers(customer_unique_id)Īs you can see, we have created the foreign key constraint on the customer_unique_id column. We already have a table called customers. What will happen? Will the PostgreSQL allows us to do so? Let’s see. ![]() Now think about a situation where the parent table has a column that is referenced by the child table and you want to delete that column. ALTER TABLE IF EXISTS customersĭROP COLUMN customer_data Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) Drop A Single ColumnĪs you can see in the output above, our column has been deleted successfully! Let’s delete it using the DROP COLUMN statement. Now, we no more need the customer_data column. Here, we have created a table ‘customers’ with few fields. ) Code language: PostgreSQL SQL dialect and PL/pgSQL ( pgsql ) Let’s now create a table and then demonstrate the examples of dropping the columns. Let’s now take some examples so that you would understand the usage of the DROP COLUMN statement along with the RESTRICT and CASCADE clauses. CASCADE clause will drop the column even if there is some dependency present such as key relation or a view.RESTRICT clause will restrict the deletion of the column if there is any dependency present.This clause benefits you when you are trying to drop multiple columns and some of them don’t exist. IF EXISTS clause with the column name will prevent the error if the column doesn’t exist.DROP COLUMN statement will delete the column name you specified.IF EXISTS clause prevents the error if the table name that you specified doesn’t exit. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |