You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by bp...@apache.org on 2006/08/26 21:42:03 UTC
svn commit: r437215 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/
shared/org/apache/derby/shared/common/reference/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTe...
Author: bpendleton
Date: Sat Aug 26 12:42:02 2006
New Revision: 437215
URL: http://svn.apache.org/viewvc?rev=437215&view=rev
Log:
DERBY-119: Add ALTER TABLE option to change column from NULL to NOT NULL
This change merges the db2AlterColumn and columnAlterClause routines in
the parser to provide a single ALTER TABLE ALTER COLUMN statement that
provides support for changing the NULL / NOT NULL constraint on a
column in a table.
I believe that the existing execution code in AlterTableConstantAction
is correct, and so this patch primarily involves parser changes to hook
up the compiler to the existing execution code.
The patch also modifies the constraint handling in ModifyColumnNode so
that a column cannot be changed to NULL-able if it is part of either a
primary key or a unique constraint. Prior to the patch, ModifyColumnNode
only checked for a PRIMARY KEY constraint, so this patch also adds a check
for a UNIQUE constraint. It also rewords the error message to describe the
new conditions which can cause the ALTER COLUMN to be rejected.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties
db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java?rev=437215&r1=437214&r2=437215&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java Sat Aug 26 12:42:02 2006
@@ -193,15 +193,17 @@
else
{
- // a column that is part of a primary key is being made
- // nullable; can't be done.
+ // a column that is part of a primary key or unique constraint
+ // is being made nullable; can't be done.
if ((getNodeType() ==
C_NodeTypes.MODIFY_COLUMN_CONSTRAINT_NODE) &&
- (existingConstraint.getConstraintType() ==
- DataDictionary.PRIMARYKEY_CONSTRAINT))
+ ((existingConstraint.getConstraintType() ==
+ DataDictionary.PRIMARYKEY_CONSTRAINT) ||
+ (existingConstraint.getConstraintType() ==
+ DataDictionary.UNIQUE_CONSTRAINT)))
{
throw StandardException.newException(
- SQLState.LANG_MODIFY_COLUMN_PKEY_CONSTRAINT, name);
+ SQLState.LANG_MODIFY_COLUMN_EXISTING_CONSTRAINT, name);
}
// unique key or primary key.
ConstraintDescriptorList
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=437215&r1=437214&r2=437215&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Sat Aug 26 12:42:02 2006
@@ -11791,22 +11791,10 @@
}
|
<ALTER> [ <COLUMN> ] columnName = identifier(Limits.MAX_IDENTIFIER_LENGTH, true)
- typeDescriptor = DB2AlterColumn(autoIncrementInfo)
+ tableElement = columnAlterClause(columnName)
{
changeType[0] = DDLStatementNode.MODIFY_TYPE;
- /* typeDescriptor is not null for MODIFY_COLUMN_TYPE_NODE */
- if (typeDescriptor != null)
- tableElementList.addTableElement((TableElementNode) nodeFactory.getNode(
- C_NodeTypes.MODIFY_COLUMN_TYPE_NODE,
- columnName, null,
- typeDescriptor, null,
- getContextManager()));
- else
- tableElementList.addTableElement((TableElementNode) nodeFactory.getNode(
- C_NodeTypes.MODIFY_COLUMN_DEFAULT_NODE,
- columnName,
- null, null, autoIncrementInfo,
- getContextManager()));
+ tableElementList.addTableElement(tableElement);
return lockGranularity;
}
|
@@ -11842,29 +11830,61 @@
}
}
-
-TableElementNode
-columnAlter() throws StandardException :
-{
- String columnName;
- TableElementNode tn;
-}
-{
- columnName = identifier(Limits.MAX_IDENTIFIER_LENGTH, true) tn = columnAlterClause(columnName)
- {
- return tn;
- }
-}
-
+/*
+ * Various variants of the ALTER TABLE ALTER COLUMN statement.
+ *
+ * By the type we get here, we've parsed
+ * ALTER TABLE tablename ALTER [COLUMN] columnname
+ * and here we parse the remainder of the ALTER COLUMN clause, one of:
+ * SET DATA TYPE data_type
+ * SET INCREMENT BY increment_value
+ * RESTART WITH increment_restart_value
+ * [WITH] DEFAULT default_value
+ * [NOT] NULL
+ */
TableElementNode
columnAlterClause(String columnName) throws StandardException :
{
ValueNode defaultNode;
- DataTypeDescriptor typeDescriptor;
long[] autoIncrementInfo = new long[4];
+ long autoIncrementIncrement = 1;
+ long autoIncrementRestartWith = 1;
+ DataTypeDescriptor typeDescriptor = null;
}
{
- // MODIFY column_name [WITH] DEFAULT <Value>
+ LOOKAHEAD( {getToken(2).kind == DATA} )
+ <SET> <DATA> <TYPE> typeDescriptor = dataTypeDDL()
+ {
+ return (TableElementNode) nodeFactory.getNode(
+ C_NodeTypes.MODIFY_COLUMN_TYPE_NODE,
+ columnName, null,
+ typeDescriptor, null,
+ getContextManager());
+ }
+|
+ LOOKAHEAD( {getToken(2).kind == INCREMENT} )
+ <SET> <INCREMENT> <BY> autoIncrementIncrement = exactNumber()
+ {
+ autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_INC_INDEX] = autoIncrementIncrement;
+ autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_CREATE_MODIFY] = ColumnDefinitionNode.MODIFY_AUTOINCREMENT_INC_VALUE;
+ return (TableElementNode) nodeFactory.getNode(
+ C_NodeTypes.MODIFY_COLUMN_DEFAULT_NODE,
+ columnName,
+ null, null, autoIncrementInfo,
+ getContextManager());
+ }
+|
+ <RESTART> <WITH> autoIncrementRestartWith = exactNumber()
+ {
+ autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_START_INDEX] = autoIncrementRestartWith;
+ autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_CREATE_MODIFY] = ColumnDefinitionNode.MODIFY_AUTOINCREMENT_RESTART_VALUE;
+ return (TableElementNode) nodeFactory.getNode(
+ C_NodeTypes.MODIFY_COLUMN_DEFAULT_NODE,
+ columnName,
+ null, null, autoIncrementInfo,
+ getContextManager());
+ }
+|
LOOKAHEAD( {getToken(1).kind == WITH || getToken(1).kind == _DEFAULT })
defaultNode = defaultClause(autoIncrementInfo, columnName)
{
@@ -11881,7 +11901,6 @@
getContextManager());
}
|
- // MODIFY column_name NULL
LOOKAHEAD ({getToken(1).kind == NULL })
<NULL>
{
@@ -11896,7 +11915,6 @@
getContextManager());
}
|
- // MODIFY column_name NOT NULL
LOOKAHEAD({getToken(1).kind == NOT})
<NOT> <NULL>
{
@@ -11909,49 +11927,6 @@
C_NodeTypes.MODIFY_COLUMN_CONSTRAINT_NOT_NULL_NODE,
columnName, null, null, null,
getContextManager());
- }
-|
- // MODIFY column_name varchar(64)
- typeDescriptor = dataTypeDDL()
- {
- return (TableElementNode) nodeFactory.getNode(
- C_NodeTypes.MODIFY_COLUMN_TYPE_NODE,
- columnName,
- null, typeDescriptor, null,
- getContextManager());
- }
-}
-
-/*
- * DB2 syntax for ALTER COLUMN
- */
-DataTypeDescriptor
-DB2AlterColumn(long[] autoIncrementInfo) throws StandardException :
-{
- long autoIncrementIncrement = 1;
- long autoIncrementRestartWith = 1;
- DataTypeDescriptor typeDescriptor = null;
-}
-{
- LOOKAHEAD( {getToken(2).kind == DATA} )
- <SET> <DATA> <TYPE> typeDescriptor = dataTypeDDL()
- {
- return typeDescriptor;
- }
-|
- LOOKAHEAD( {getToken(2).kind == INCREMENT} )
- <SET> <INCREMENT> <BY> autoIncrementIncrement = exactNumber()
- {
- autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_INC_INDEX] = autoIncrementIncrement;
- autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_CREATE_MODIFY] = ColumnDefinitionNode.MODIFY_AUTOINCREMENT_INC_VALUE;
- return typeDescriptor;
- }
-|
- <RESTART> <WITH> autoIncrementRestartWith = exactNumber()
- {
- autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_START_INDEX] = autoIncrementRestartWith;
- autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_CREATE_MODIFY] = ColumnDefinitionNode.MODIFY_AUTOINCREMENT_RESTART_VALUE;
- return typeDescriptor;
}
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties?rev=437215&r1=437214&r2=437215&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties Sat Aug 26 12:42:02 2006
@@ -720,7 +720,7 @@
42Z17=Invalid length specified for column ''{0}''. Length must be greater than the current column length.
42Z18=Column ''{0}'' is part of a foreign key constraint ''{1}''. To alter the length of this column, you should drop the constraint first, perform the ALTER TABLE, and then recreate the constraint.
42Z19=Column ''{0}'' is being referenced by at least one foreign key constraint ''{1}''. To alter the length of this column, you should drop referencing constraints, perform the ALTER TABLE and then recreate the constraints.
-42Z20=Column ''{0}'' cannot be made nullable. It is part of a primary key, which cannot have any nullable columns.
+42Z20=Column ''{0}'' cannot be made nullable. It is part of a primary key or unique constraint, which cannot have any nullable columns.
#####
# end of alter table modify constraints.
Modified: db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java?rev=437215&r1=437214&r2=437215&view=diff
==============================================================================
--- db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java (original)
+++ db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java Sat Aug 26 12:42:02 2006
@@ -939,7 +939,7 @@
String LANG_MODIFY_COLUMN_INVALID_LENGTH = "42Z17";
String LANG_MODIFY_COLUMN_FKEY_CONSTRAINT = "42Z18";
String LANG_MODIFY_COLUMN_REFERENCED = "42Z19";
- String LANG_MODIFY_COLUMN_PKEY_CONSTRAINT = "42Z20";
+ String LANG_MODIFY_COLUMN_EXISTING_CONSTRAINT = "42Z20";
String LANG_AI_INVALID_INCREMENT = "42Z21";
String LANG_AI_INVALID_TYPE = "42Z22";
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out?rev=437215&r1=437214&r2=437215&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out Sat Aug 26 12:42:02 2006
@@ -867,4 +867,63 @@
0 rows inserted/updated/deleted
ij> declare global temporary table session.logged(logged int) on commit delete rows not logged;
0 rows inserted/updated/deleted
+ij> -- tests for ALTER TABLE ALTER COLUMN [NOT] NULL
+create table atmcn_1 (a integer, b integer not null);
+0 rows inserted/updated/deleted
+ij> -- should fail because b cannot be null
+insert into atmcn_1 (a) values (1);
+ERROR 23502: Column 'B' cannot accept a NULL value.
+ij> insert into atmcn_1 values (1,1);
+1 row inserted/updated/deleted
+ij> select * from atmcn_1;
+A |B
+-----------------------
+1 |1
+ij> alter table atmcn_1 alter column a not null;
+0 rows inserted/updated/deleted
+ij> -- should fail because a cannot be null
+insert into atmcn_1 (b) values (2);
+ERROR 23502: Column 'A' cannot accept a NULL value.
+ij> insert into atmcn_1 values (2,2);
+1 row inserted/updated/deleted
+ij> select * from atmcn_1;
+A |B
+-----------------------
+1 |1
+2 |2
+ij> alter table atmcn_1 alter column b null;
+0 rows inserted/updated/deleted
+ij> insert into atmcn_1 (a) values (1);
+1 row inserted/updated/deleted
+ij> select * from atmcn_1;
+A |B
+-----------------------
+1 |1
+2 |2
+1 |NULL
+ij> -- Now that B has a null value, trying to modify it to NOT NULL should fail
+alter table atmcn_1 alter column b not null;
+ERROR X0Y80: ALTER table 'APP.ATMCN_1' failed. Null data found in column 'B'.
+ij> -- show that a column which is part of the PRIMARY KEY cannot be modified NULL
+create table atmcn_2 (a integer not null primary key, b integer not null);
+0 rows inserted/updated/deleted
+ij> alter table atmcn_2 alter column a null;
+ERROR 42Z20: Column 'A' cannot be made nullable. It is part of a primary key or unique constraint, which cannot have any nullable columns.
+ij> create table atmcn_3 (a integer not null, b integer not null);
+0 rows inserted/updated/deleted
+ij> alter table atmcn_3 add constraint atmcn_3_pk primary key(a, b);
+0 rows inserted/updated/deleted
+ij> alter table atmcn_3 alter column b null;
+ERROR 42Z20: Column 'B' cannot be made nullable. It is part of a primary key or unique constraint, which cannot have any nullable columns.
+ij> -- verify that the keyword "column" in the ALTER TABLE ... ALTER COLUMN ...
+-- statement is optional:
+create table atmcn_4 (a integer not null, b integer);
+0 rows inserted/updated/deleted
+ij> alter table atmcn_4 alter a null;
+0 rows inserted/updated/deleted
+ij> -- show that a column which has a UNIQUE constraint cannot be modified NULL:
+create table atmcn_5 (a integer not null, b integer not null unique);
+0 rows inserted/updated/deleted
+ij> alter table atmcn_5 alter column b null;
+ERROR 42Z20: Column 'B' cannot be made nullable. It is part of a primary key or unique constraint, which cannot have any nullable columns.
ij>
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql?rev=437215&r1=437214&r2=437215&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql Sat Aug 26 12:42:02 2006
@@ -552,3 +552,32 @@
drop table logged;
declare global temporary table session.logged(logged int) on commit delete rows not logged;
+-- tests for ALTER TABLE ALTER COLUMN [NOT] NULL
+create table atmcn_1 (a integer, b integer not null);
+-- should fail because b cannot be null
+insert into atmcn_1 (a) values (1);
+insert into atmcn_1 values (1,1);
+select * from atmcn_1;
+alter table atmcn_1 alter column a not null;
+-- should fail because a cannot be null
+insert into atmcn_1 (b) values (2);
+insert into atmcn_1 values (2,2);
+select * from atmcn_1;
+alter table atmcn_1 alter column b null;
+insert into atmcn_1 (a) values (1);
+select * from atmcn_1;
+-- Now that B has a null value, trying to modify it to NOT NULL should fail
+alter table atmcn_1 alter column b not null;
+-- show that a column which is part of the PRIMARY KEY cannot be modified NULL
+create table atmcn_2 (a integer not null primary key, b integer not null);
+alter table atmcn_2 alter column a null;
+create table atmcn_3 (a integer not null, b integer not null);
+alter table atmcn_3 add constraint atmcn_3_pk primary key(a, b);
+alter table atmcn_3 alter column b null;
+-- verify that the keyword "column" in the ALTER TABLE ... ALTER COLUMN ...
+-- statement is optional:
+create table atmcn_4 (a integer not null, b integer);
+alter table atmcn_4 alter a null;
+-- show that a column which has a UNIQUE constraint cannot be modified NULL:
+create table atmcn_5 (a integer not null, b integer not null unique);
+alter table atmcn_5 alter column b null;