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 jb...@apache.org on 2005/03/08 22:23:04 UTC
svn commit: r156570 - in incubator/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: jboynes
Date: Tue Mar 8 13:22:59 2005
New Revision: 156570
URL: http://svn.apache.org/viewcvs?view=rev&rev=156570
Log:
Fix for DERBY-158 PRIMARY KEY does not imply NOT NULL
Columns specified in a PRIMARY KEY constraint in CREATE TABLE
are implicitly set to NOT NULL per the SQL spec.
If a PRIMARY KEY constraint is added using ALTER TABLE then all
columns must also be NOT NULL. Similarly, columns
specified in a UNIQUE constraint must still be explictly NOT NULL.
Modified:
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/nulls.out
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/primarykey.out
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/nulls.sql
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/primarykey.sql
Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java?view=diff&r1=156569&r2=156570
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java (original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java Tue Mar 8 13:22:59 2005
@@ -33,7 +33,6 @@
import org.apache.derby.iapi.sql.dictionary.DataDictionary;
import org.apache.derby.iapi.sql.dictionary.SchemaDescriptor;
import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
-import org.apache.derby.iapi.sql.dictionary.ColumnDescriptor;
import org.apache.derby.iapi.sql.depend.DependencyManager;
import org.apache.derby.iapi.sql.depend.ProviderInfo;
@@ -47,6 +46,7 @@
import org.apache.derby.impl.sql.execute.IndexConstantAction;
import org.apache.derby.iapi.sql.dictionary.ConstraintDescriptorList;
+import org.apache.derby.iapi.sql.dictionary.ColumnDescriptor;
import org.apache.derby.catalog.UUID;
@@ -62,8 +62,8 @@
public class TableElementList extends QueryTreeNodeVector
{
- int numColumns;
- TableDescriptor td;
+ private int numColumns;
+ private TableDescriptor td;
/**
* Add a TableElementNode to this TableElementList
@@ -173,7 +173,7 @@
checkForDuplicateColumns(ddlStmt, columnHT, cdn.getColumnName());
cdn.checkUserType(td);
cdn.bindAndValidateDefault(dd, td);
-
+
cdn.validateAutoincrement(dd, td, tableType);
if (tableElement instanceof ModifyColumnNode)
@@ -262,7 +262,7 @@
String dropSchemaName = cdn.getDropSchemaName();
- SchemaDescriptor sd = dropSchemaName == null ? td.getSchemaDescriptor() :
+ SchemaDescriptor sd = dropSchemaName == null ? td.getSchemaDescriptor() :
getSchemaDescriptor(dropSchemaName);
ConstraintDescriptor cd =
@@ -280,22 +280,33 @@
}
}
- /* For primary/unique/unique keys, verify that the constraint's column
- * list contains valid columns and does not contain any duplicates
- * (Also, all columns in a primary key will be set to non-null,
- but only in Cloudscape mode. SQL and DB2 require explict NOT NULL.
- */
- if (cdn.hasPrimaryKeyConstraint() ||
- cdn.hasForeignKeyConstraint() ||
- cdn.hasUniqueKeyConstraint())
- {
- verifyUniqueColumnList(ddlStmt, cdn);
- /* Raise error if primary or unique key columns can be nullable. */
- if (cdn.hasPrimaryKeyConstraint() || cdn.hasUniqueKeyConstraint())
- {
- setColumnListToNotNull(cdn, td);
- }
- }
+ if (cdn.hasPrimaryKeyConstraint())
+ {
+ // for PRIMARY KEY, check that columns are unique
+ verifyUniqueColumnList(ddlStmt, cdn);
+
+ if (td == null)
+ {
+ // in CREATE TABLE so set PRIMARY KEY columns to NOT NULL
+ setColumnListToNotNull(cdn);
+ }
+ else
+ {
+ // in ALTER TABLE so raise error if any columns are nullable
+ checkForNullColumns(cdn, td);
+ }
+ }
+ else if (cdn.hasUniqueKeyConstraint())
+ {
+ // for UNIQUE, check that columns are unique and NOT NULL
+ verifyUniqueColumnList(ddlStmt, cdn);
+ checkForNullColumns(cdn, td);
+ }
+ else if (cdn.hasForeignKeyConstraint())
+ {
+ // for FOREIGN KEY, check that columns are unique
+ verifyUniqueColumnList(ddlStmt, cdn);
+ }
}
/* Can have only one autoincrement column in DB2 mode */
@@ -303,8 +314,8 @@
throw StandardException.newException(SQLState.LANG_MULTIPLE_AUTOINCREMENT_COLUMNS);
}
-
- /**
+
+ /**
* Count the number of constraints of the specified type.
*
* @param constraintType The constraint type to search for.
@@ -872,62 +883,78 @@
}
/**
- * Set all columns in that appear in a primary/unique key constraint in a create
- * table statement to NOT NULL in Cloudscape mode and raises an error in DB2 mode.
+ * Set all columns in that appear in a PRIMARY KEY constraint in a CREATE TABLE statement to NOT NULL.
*
- * @param cdn The ConstraintDefinitionNode
- * @param td TableDescriptor for the table
+ * @param cdn The ConstraintDefinitionNode for a PRIMARY KEY constraint
*/
- private void setColumnListToNotNull(ConstraintDefinitionNode cdn, TableDescriptor td)
- throws StandardException
+ private void setColumnListToNotNull(ConstraintDefinitionNode cdn)
{
ResultColumnList rcl = cdn.getColumnList();
int rclSize = rcl.size();
for (int index = 0; index < rclSize; index++)
{
String colName = ((ResultColumn) rcl.elementAt(index)).getName();
-
- /* For ALTER TABLE ADD CONSTRAINT, make sure columns are not nullable for
- * primary and unique constraints.
- */
- if (td != null && cdn instanceof ConstraintDefinitionNode)
- {
- ColumnDescriptor cd = td.getColumnDescriptor(colName);
- if (cd != null && cd.getType().isNullable())
- throw StandardException.newException(SQLState.LANG_DB2_ADD_UNIQUE_OR_PRIMARY_KEY_ON_NULL_COLS, colName);
- }
-
- setColumnToNotNull(colName);
- }
+ DataTypeDescriptor dtd = getColumnDataTypeDescriptor(colName);
+ dtd.setNullability(false);
+ }
}
- /**
- * Set a column that appears in a primary/unique key constraint in
- * a create table statement to NOT NULL (but only in Cloudscape mode).
- *
- * @param colName The column name
- */
- private void setColumnToNotNull(String colName) throws StandardException
- {
- int size = size();
-
- for (int index = 0; index < size; index++)
- {
- TableElementNode tableElement = (TableElementNode) elementAt(index);
-
- if (tableElement instanceof ColumnDefinitionNode)
- {
- ColumnDefinitionNode cdn = (ColumnDefinitionNode) tableElement;
- if (colName.equals(cdn.getColumnName()))
- {
- DataTypeDescriptor dtd = cdn.getDataTypeServices();
- if (dtd.isNullable())
- throw StandardException.newException(SQLState.LANG_DB2_ADD_UNIQUE_OR_PRIMARY_KEY_ON_NULL_COLS, colName);
- }
- }
- }
- }
+ private void checkForNullColumns(ConstraintDefinitionNode cdn, TableDescriptor td) throws StandardException
+ {
+ ResultColumnList rcl = cdn.getColumnList();
+ int rclSize = rcl.size();
+ for (int index = 0; index < rclSize; index++)
+ {
+ String colName = ((ResultColumn) rcl.elementAt(index)).getName();
+ DataTypeDescriptor dtd;
+ if (td == null)
+ {
+ dtd = getColumnDataTypeDescriptor(colName);
+ }
+ else
+ {
+ dtd = getColumnDataTypeDescriptor(colName, td);
+ }
+ // todo dtd may be null if the column does not exist, we should check that first
+ if (dtd != null && dtd.isNullable())
+ {
+ throw StandardException.newException(SQLState.LANG_DB2_ADD_UNIQUE_OR_PRIMARY_KEY_ON_NULL_COLS, colName);
+ }
+ }
+ }
+
+ private DataTypeDescriptor getColumnDataTypeDescriptor(String colName)
+ {
+ int size = size();
+
+ for (int index = 0; index < size; index++)
+ {
+ TableElementNode tableElement = (TableElementNode) elementAt(index);
+
+ if (tableElement instanceof ColumnDefinitionNode)
+ {
+ ColumnDefinitionNode cdn = (ColumnDefinitionNode) tableElement;
+ if (colName.equals(cdn.getColumnName()))
+ {
+ return cdn.getDataTypeServices();
+ }
+ }
+ }
+ return null;
+ }
+
+ private DataTypeDescriptor getColumnDataTypeDescriptor(String colName, TableDescriptor td)
+ {
+ // check existing columns
+ ColumnDescriptor cd = td.getColumnDescriptor(colName);
+ if (cd != null)
+ {
+ return cd.getType();
+ }
+ // check for new columns
+ return getColumnDataTypeDescriptor(colName);
+ }
/**
* Determine whether or not the parameter matches a column name in this list.
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out?view=diff&r1=156569&r2=156570
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out Tue Mar 8 13:22:59 2005
@@ -90,13 +90,13 @@
ERROR 42X01: Syntax error: Encountered "STATEMENT" at line 1, column 6.
ij> -- Primary key constraint, DB2 requires NOT null on the columns.
create table customer (id int primary key, name char(100));
-ERROR 42831: 'ID' cannot be a column of a primary key or unique key because it can contain null values.
+0 rows inserted/updated/deleted
ij> drop table customer;
-ERROR 42Y55: 'DROP TABLE' cannot be performed on 'CUSTOMER' because it does not exist.
+0 rows inserted/updated/deleted
ij> create table customer (id int NOT NULL, id2 int, name char(100), primary key (id, id2));
-ERROR 42831: 'ID2' cannot be a column of a primary key or unique key because it can contain null values.
+0 rows inserted/updated/deleted
ij> drop table customer;
-ERROR 42Y55: 'DROP TABLE' cannot be performed on 'CUSTOMER' because it does not exist.
+0 rows inserted/updated/deleted
ij> -- Unique key constraint, DB2 requires NOT null on the columns.
create table customer (id int unique, name char(100));
ERROR 42831: 'ID' cannot be a column of a primary key or unique key because it can contain null values.
@@ -1308,13 +1308,6 @@
ij> drop table likeable;
0 rows inserted/updated/deleted
ij> drop table likes;
-0 rows inserted/updated/deleted
-ij> -- no schema names in constraint names (beetle 5143)
-CREATE TABLE S5143.T5143_1 (C1 int CONSTRAINT S5143.CPK1 PRIMARY KEY);
-ERROR 42831: 'C1' cannot be a column of a primary key or unique key because it can contain null values.
-ij> CREATE TABLE S5143.T5143_2 (C1 int, C2 int, CONSTRAINT S5143.CPK1 PRIMARY KEY(C1,C2));
-ERROR 42831: 'C1' cannot be a column of a primary key or unique key because it can contain null values.
-ij> CREATE TABLE S5143.T5143_3 (C1 int, C2 int, CONSTRAINT S5143.C3 CHECK(C1 > C2));
0 rows inserted/updated/deleted
ij> -- READ ONLY not allowed in "FOR" clause of a select.
create table roTable (i int);
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/nulls.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/nulls.out?view=diff&r1=156569&r2=156570
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/nulls.out (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/nulls.out Tue Mar 8 13:22:59 2005
@@ -85,10 +85,6 @@
-- give error
create table a1(ac1 int null, ac2 int not null, primary key(ac1,ac2));
ERROR 42X01: Syntax error: Encountered "null" at line 3, column 25.
-ij> -- should fail because
--- not null must explicitly be specified for columns that have primary keys
-create table a1(ac1 int, ac2 int not null, primary key(ac1,ac2));
-ERROR 42831: 'AC1' cannot be a column of a primary key or unique key because it can contain null values.
ij> -- say null twice should fail
create table a2(ac1 int null null);
ERROR 42X01: Syntax error: Encountered "null" at line 2, column 25.
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/primarykey.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/primarykey.out?view=diff&r1=156569&r2=156570
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/primarykey.out (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/primarykey.out Tue Mar 8 13:22:59 2005
@@ -59,19 +59,24 @@
ERROR 42X01: Syntax error: Encountered "null" at line 2, column 26.
ij> create table neg2(c1 int null, c2 int, constraint asdf primary key(c1, c2));
ERROR 42X01: Syntax error: Encountered "null" at line 1, column 26.
-ij> -- verify that you can not create a primary key column with default null
--- a primary key column can only be create if the column is explicitly not null
-create table neg1 (c1 int default null primary key);
+ij> -- test that a unique key can be not be explicitly nullable
+create table neg1(c1 int unique);
ERROR 42831: 'C1' cannot be a column of a primary key or unique key because it can contain null values.
-ij> create table neg1 (c1 int default null, c2 int not null, primary key(c2, c1));
+ij> create table neg1(c1 int, c2 int, constraint asdf unique(c1));
ERROR 42831: 'C1' cannot be a column of a primary key or unique key because it can contain null values.
-ij> -- test that a unique key can be not be explicitly nullable
-create table neg1(c1 int null unique);
-ERROR 42X01: Syntax error: Encountered "null" at line 2, column 26.
-ij> create table neg1(c1 int null, c2 int, constraint asdf unique(c1));
-ERROR 42X01: Syntax error: Encountered "null" at line 1, column 26.
ij> -- positive tests
--- verify that a unique key can not contain nulls
+-- verify that a primary key implies not null
+create table pos1 (c1 int primary key);
+0 rows inserted/updated/deleted
+ij> insert into pos1(c1) values(1);
+1 row inserted/updated/deleted
+ij> insert into pos1(c1) values(1);
+ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'xxxxGENERATED-IDxxxx' defined on 'POS1'.
+ij> insert into pos1(c1) values(null);
+ERROR 23502: Column 'C1' cannot accept a NULL value.
+ij> drop table pos1;
+0 rows inserted/updated/deleted
+ij> -- verify that a unique key can not contain nulls
create table pos1 (c1 int not null unique, c2 int);
0 rows inserted/updated/deleted
ij> insert into pos1 (c1) values(null);
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql?view=diff&r1=156569&r2=156570
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql Tue Mar 8 13:22:59 2005
@@ -872,12 +872,6 @@
drop table likeable;
drop table likes;
--- no schema names in constraint names (beetle 5143)
-CREATE TABLE S5143.T5143_1 (C1 int CONSTRAINT S5143.CPK1 PRIMARY KEY);
-CREATE TABLE S5143.T5143_2 (C1 int, C2 int, CONSTRAINT S5143.CPK1 PRIMARY KEY(C1,C2));
-CREATE TABLE S5143.T5143_3 (C1 int, C2 int, CONSTRAINT S5143.C3 CHECK(C1 > C2));
-
-
-- READ ONLY not allowed in "FOR" clause of a select.
create table roTable (i int);
insert into roTable values (8);
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/nulls.sql
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/nulls.sql?view=diff&r1=156569&r2=156570
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/nulls.sql (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/nulls.sql Tue Mar 8 13:22:59 2005
@@ -80,10 +80,6 @@
-- give error
create table a1(ac1 int null, ac2 int not null, primary key(ac1,ac2));
--- should fail because
--- not null must explicitly be specified for columns that have primary keys
-create table a1(ac1 int, ac2 int not null, primary key(ac1,ac2));
-
-- say null twice should fail
create table a2(ac1 int null null);
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/primarykey.sql
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/primarykey.sql?view=diff&r1=156569&r2=156570
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/primarykey.sql (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/primarykey.sql Tue Mar 8 13:22:59 2005
@@ -49,18 +49,20 @@
create table neg2(c1 int null constraint asdf primary key);
create table neg2(c1 int null, c2 int, constraint asdf primary key(c1, c2));
--- verify that you can not create a primary key column with default null
--- a primary key column can only be create if the column is explicitly not null
-create table neg1 (c1 int default null primary key);
-create table neg1 (c1 int default null, c2 int not null, primary key(c2, c1));
-
-- test that a unique key can be not be explicitly nullable
-create table neg1(c1 int null unique);
-create table neg1(c1 int null, c2 int, constraint asdf unique(c1));
+create table neg1(c1 int unique);
+create table neg1(c1 int, c2 int, constraint asdf unique(c1));
-- positive tests
+
+-- verify that a primary key implies not null
+create table pos1 (c1 int primary key);
+insert into pos1(c1) values(1);
+insert into pos1(c1) values(1);
+insert into pos1(c1) values(null);
+drop table pos1;
-- verify that a unique key can not contain nulls
create table pos1 (c1 int not null unique, c2 int);