If all columns that make up an index are dropped, the index is dropped as well. If columns are dropped from a table, the columns are also removed from any index of which they are a part.
Restrictions by RDBMSĪlthough the basic DROP COLUMN syntax is pretty similar across most of the major RDBMSs, each RDBMS tends to have its own restrictions for when a column will or won’t be dropped. If your intention is to drop the table, use DROP TABLE. MySQL also refuses to drop the last column in a table. In this case, none of the three columns were actually dropped.Įither way, even if I did drop the other two, SQL Server would refuse to drop the last one. However, c3 would have been the last one remaining if the other two had been dropped.
Note that despite the wording of this error message, c3 wasn’t the only remaining column. A table must have at least one data column. Result: Msg 4923, Level 16, State 1, Line 1ĪLTER TABLE DROP COLUMN failed because 'c3' is the only data column in table 't1'. 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.
String colsToRemove) throws else if (defaultValueType = Cursor.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. I've wrote a Java implementation based on the Sqlite's recommended way to do this: private void dropColumn(SQLiteDatabase db,
0 kommentar(er)
