This is an extension of the SQL standard (which doesn’t allow zero-column tables).įor example, in PostgreSQL, I used the following statement to drop the last remaining column in the table ALTER TABLE t1īut in SQL Server, if I do the same: ALTER TABLE t1 Some RDBMs allow you to drop the last column in the table, therefore leaving an empty table with no columns. Note that this syntax is an extension to SQL, and it doesn’t conform to the SQL standard of only having one DROP clause per ALTER TABLE statement. In other RDBMSs (such as MySQL and PostgreSQL), you would need to rewrite DROP COLUMN for each column: ALTER TABLE t1 In SQL Server, you can simply list each column, separated by a comma: ALTER TABLE t1 Some RDBMs allow you to drop multiple columns within a single ALTER TABLE statement. Oracle accepts a CASCADE CONSTRAINTS clause which drops all foreign key constraints that refer to the primary and unique keys defined on the dropped columns as well as all multicolumn constraints defined on the dropped columns. Both keywords are able to be used in MariaDB but they don’t have any effect. In this case, the column was dropped and I got a message explaining that the view called vproducts was also dropped.ĬASCADE and RESTRICT are supported in PostgreSQL, but not in SQL Server or MySQL. Result: NOTICE: drop cascades to view vproducts Here’s what happens if I change the previous example to CASCADE: ALTER TABLE Products Using the CASCADE option will cause any dependent objects to be dropped. Here’s the error I get in PostgreSQL when trying to drop a table that’s referenced by a view: cannot drop column productdescription of table products because other objects depend on it Cascade the Change When using the above statement, if the column has any dependencies, the drop operation will fail, and you’ll get an error. RESTRICT is typically the default behavior, so if you don’t specify any of these arguments, the DBMS will refuse to drop the column if there are any dependent objects. The benefit of doing this is that you won’t get an error if the column doesn’t exist.ĭROP COLUMN IF EXISTS ProductDescription Restrict the Changeĭepending on your RDBMS, you may be able to use the CASCADE and RESTRICT arguments to specify what to do if the column has any dependencies, such as foreign keys or views. The IF EXISTS Argumentĭepending on your RDBMS, you may be able to use the IF EXISTS argument, which conditionally drops the column only if it already exists. This removes the ProductDescription column from the Products table. Some RDBMSs also accept optional CASCADE and RESTRICT arguments, which specify what to do if the column has any dependencies. Some RDBMSs accept an optional IF EXISTS argument which means that it won’t return an error if the column doesn’t exist. The syntax goes like this: ALTER TABLE table_name That removes the column and all its data. In SQL, if you want to remove a column from a table, you need to use the ALTER TABLE statement with the DROP COLUMN clause.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |