db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jboy...@apache.org
Subject 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/
Date Tue, 08 Mar 2005 21:23:04 GMT
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);



Mime
View raw message