Changing nullability of column fails

[ztminhas]

Good Afternoon,

I am trying to run the following DDL statement to change two columns from NULL to NOT NULL. The code runs, but the columns

• TBL_W_WELL.ACCESS_NUM
• TBL_W_WELL.ANLS_DATE
  are not changed from NULL to NOT NULL

executeDDL("EXECUTE BLOCK AS BEGIN IF (EXISTS( SELECT 0 FROM RDB$RELATION_CONSTRAINTS WHERE RDB$CONSTRAINT_NAME = 'PK_WATER_LEVEL' )) THEN EXECUTE STATEMENT 'ALTER TABLE WATER_LEVEL DROP CONSTRAINT PK_WATER_LEVEL'; END")
executeDDL("ALTER TABLE TBL_W_WELL DROP ACCESS_NUM")
executeDDL("ALTER TABLE TBL_W_WELL DROP ANLS_DATE ")
executeDDL("ALTER TABLE TBL_W_WELL ADD ACCESS_NUMINTEGER")
executeDDL("ALTER TABLE TBL_W_WELL ADD ANLS_DATE TIMESTAMP")	
executeDDL("ALTER TABLE TBL_W_WELL ADD CONSTRAINT PK_TBL_W_WELL PRIMARY KEY (ID)")	
---------
executeDDL("ALTER TABLE TBL_W_WELL ALTER ACCESS_NUM SET NOT NULL")
executeDDL("ALTER TABLE TBL_W_WELL ALTER ANLS_DATE SET NOT NULL")


Sub executeDDL(ddl As String)
Dim locStmt As Object
REM locCon is made in a seperate Sub - this is a connection to the embedded firebird database.
	locStmt= locCon.createStatement()
	locStmt.execute(ddl)
	closeDBObject(locStmt)	
End Sub

Sub closeDBObject(dbObject As Object)
	dbObject.close()
	dbObject = Nothing
End Sub

[Villeroy]

If the column in question has Null values, you can’t set it to NOT NULL. Either you delete the Null records or you fill them with an UPDATE statement.

[ztminhas]

Hello

The use case that I am implementing is that the columns are not modified until all the data are filled or the table is empty.

[RobertG]

Might be I doesn’t understand, but in row 2 and 3 you drop the same fields you will later set to NOT NULL.

[ztminhas]

That is correct. When I manually set them to NOT NULL, for some reason they do not get changed to NULL through code. So the alternative was to drop the column and re-add it.