[#DERBY-1489] Provide ALTER TABLE DROP COLUMN functionality

Derby

Provide ALTER TABLE DROP COLUMN functionality

Created: 08/Jul/06 11:16 PM Updated: 01/Jul/09 12:34 AM

Return to search

Component/s:

Documentation, SQL

Affects Version/s:

10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6

Fix Version/s:

10.3.1.4

Time Tracking:

Not Specified

File Attachments:

File Name	Date Attached	Attached By	Size
drop_column_v5_grant_tests.diff	2006-09-10 08:11 PM	Bryan Pendleton	30 kB
drop_column_v6_grant_not_implemented.diff	2006-09-26 02:19 PM	Bryan Pendleton	36 kB
drop_column_v7_suggestions_from_mamta.diff	2006-10-01 03:49 PM	Bryan Pendleton	23 kB
drop_column_v8_restored_tests.diff	2006-10-01 04:18 PM	Bryan Pendleton	60 kB
dropColumn_2.diff	2006-07-08 11:20 PM	Bryan Pendleton	27 kB
dropColumn_v3_view_drop.diff	2006-08-26 03:38 PM	Bryan Pendleton	30 kB
dropColumn_v4_grant_tests.diff	2006-09-05 03:27 AM	Bryan Pendleton	31 kB

Issue Links:

Blocker

This issue is blocked by:

~~DERBY-1754~~ sqlAuthorization mode causes Null Pointer Exception during ALTER TABLE

~~DERBY-1583~~ With grant revoke enabled, defining a trigger whose actions updates a table (from different schema) results in NPE

~~DERBY-1847~~ SELECT statement asserts with XJ001 when attempted to select a newly added column in SQL authorization mode

Reference

This issue relates to:

DERBY-3940 Dropping a column does not drop triggers which mention that column

~~DERBY-1909~~ ALTER TABLE DROP COLUMN needs to update GRANTed column privileges

~~DERBY-726~~ How can i drop one column?

~~DERBY-396~~ Support for ALTER STATEMENT to DROP , MODIFY, RENAME a COLUMN

~~DERBY-165~~ Droppping columns with ALTER TABLE errors out

This issue is related to:

~~DERBY-1854~~ SYSCS_COMPRESS_TABLE corrupts table with a single column which is both a primary key and a foreign key

~~DERBY-1847~~ SELECT statement asserts with XJ001 when attempted to select a newly added column in SQL authorization mode

dependent

This issue is depended upon by:

~~DERBY-1926~~ Provide documentation for ALTER TABLE DROP COLUMN

Urgency:	Normal
Resolution Date:	05/Oct/06 11:15 PM

Description

« Hide

Provide a way to drop a column from an existing table. Possible syntax would be:

ALTER TABLE tablename DROP COLUMN columnname CASCADE / RESTRICT;

Feature should properly handle columns which are used in constraints, views, triggers, indexes, etc.

All

Comments

Work Log

Change History

Subversion Commits

Sort Order:

Repository	Revision	Date	User	Message
ASF	#453420	Thu Oct 05 23:11:32 UTC 2006	bpendleton	~~DERBY-1489~~: Provide ALTER TABLE DROP COLUMN functionality This patch provides support for ALTER TABLE t DROP COLUMN c. The patch modifies the SQL parser so that it supports statements of the form: ALTER TABLE t DROP [COLUMN] c [CASCADE\|RESTRICT] If you don't specify CASCADE or RESTRICT, the default is CASCADE. If you specify RESTRICT, then the column drop will be rejected if it would cause a dependent view, trigger, check constraint, unique constraint, foreign key constraint, or primary key constraint to become invalid. Currently, column privileges are not properly adjusted when dropping a column. This is bug ~~DERBY-1909~~, and for now we simply reject DROP COLUMN if it is specified when sqlAuthorization is true. When ~~DERBY-1909~~ is fixed, the tests in altertableDropColumn.sql should be merged into altertable.sql, and altertableDropColumn.sql (and .out) should be removed. This new feature is currently undocumented. ~~DERBY-1926~~ tracks the documentation changes necessary to document this feature. The execution logic for ALTER TABLE DROP COLUMN is in AlterTableConstantAction, and was not substantially modified by this change. The primary changes to that existing code were: - to hook RESTRICT processing up to the dependency manager so that dependent view processing was sensitive to whether the user had specified CASCADE or RESTRICT - to reread the table descriptor from the catalogs after dropping all the dependent schema objects and before compressing the table, so that the proper scheman information was used during the compress.
				Files Changed
				MODIFY /db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/depend/BasicDependencyManager.java MODIFY /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql ADD /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertableDropColumn.sql MODIFY /db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java MODIFY /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out MODIFY /db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java MODIFY /db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java MODIFY /db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj MODIFY /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall MODIFY /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out MODIFY /db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ViewDescriptor.java MODIFY /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out ADD /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertableDropColumn.out MODIFY /db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/depend/DependencyManager.java MODIFY /db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java MODIFY /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql MODIFY /db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java