ALTER COLUMN to add a NOT NULL constraint

[Former Member]

Hi Team,

Logically its a very simple thing that I intend to achieve. Syntactically, I must be goofing it up. Request your help for the same.

Scenario: I have created  600 tables in SAP HANA. Now I would need to apply Primary Key and NOT NULL constraints on the columns of these tables. I am good with the Primary Key.

For applying NOT NULL constraint, I am using the following statement - ALTER TABLE "<table_name>" ALTER ("<column_name>" NOT NULL). This does not work since the datatype for the column name is missing. Hence, the statement ALTER TABLE "<table_name>" ALTER ("<column_name>" VARCHAR (256) NOT NULL) works.

Problem/Issue: I have 600 tables already created (with no data) and does not make sense to open each table and note the datatype for a particular column and use it in my ALTER TABLE query to apply NOT NULL constraint.

What do I want to finally achieve: Mass application of NOT NULL constraint on columns of already created tables (with no data, of course)

Thank you in advance.

Regards,

KG

[lbreddemann · Accepted solution]

Hi there,

unfortunately depending on the actual data type of the columns the ALTER TABLE statement needs to include data type length, scale or nothing of that.

The following script takes care of most data types and also creates the reverse commands to undo the changes:

-- MASS change of NOT NULL COLUMNS

-- set to NOT NULL - character data type, double, decimal fixed - need the length but not the scale

       select 'NOT_NULL' as action, 'alter table "'|| schema_name ||'"."'|| table_name ||'" alter ("'||column_name ||'" '||data_type_name ||' (' || length ||') NOT NULL) ;' as SQLCMD

       from table_columns

       where is_nullable = 'TRUE'

       and schema_name ='LARS'

       and data_type_name in ('VARCHAR', 'NVARCHAR', 'DOUBLE')

    and scale is NULL

UNION ALL

-- set to NOT NULL - DECIMAL (FLOATING POINT)- needs length and scale

       select 'NOT_NULL' as action, 'alter table "'|| schema_name ||'"."'|| table_name ||'" alter ("'||column_name ||'" '||data_type_name ||' (' || length ||','|| scale ||') NOT NULL) ;' as SQLCMD

       from table_columns

       where is_nullable = 'TRUE'

       and schema_name ='LARS'

       and data_type_name in ('DECIMAL' )

       and scale is not null

UNION ALL

-- set to NOT NULL -  DATE | TIME | SECONDDATE | TIMESTAMP | TINYINT | SMALLINT | INTEGER - don't need length  or scale

select 'NOT_NULL' as action, 'alter table "'|| schema_name ||'"."'|| table_name ||'" alter ("'||column_name ||'" '||data_type_name ||' NOT NULL) ;' as SQLCMD

from table_columns

where is_nullable = 'TRUE'

and schema_name ='LARS'

and data_type_name in ('DATE', 'TIME', 'SECONDDATE', 'TIMESTAMP', 'TINYINT', 'SMALLINT', 'INTEGER' )

and scale is not null

-- divider

UNION ALL

select '-', lpad ('-', 40, '-') as divider from dummy

-- UNDO ACTION:

-- set to null again - character data type, double, decimal fixed - need the length but not the scale

UNION ALL

       select 'NULL' as action, 'alter table "'|| schema_name ||'"."'|| table_name ||'" alter ("'||column_name ||'" '||data_type_name ||' (' || length ||') NULL) ;' as SQLCMD

       from table_columns

       where is_nullable = 'TRUE'

       and schema_name ='LARS'

       and data_type_name in ('VARCHAR', 'NVARCHAR', 'DOUBLE' )

and scale is NULL

UNION ALL

-- set to null again -  DECIMAL (FLOATING POINT) - needs length and scale

       select 'NULL' as action, 'alter table "'|| schema_name ||'"."'|| table_name ||'" alter ("'||column_name ||'" '||data_type_name ||' (' || length ||','|| scale ||') NULL) ;' as SQLCMD

       from table_columns

       where is_nullable = 'TRUE'

       and schema_name ='LARS'

       and data_type_name in ('DECIMAL' )

       and scale is not null

UNION ALL

-- set to null again -  DATE | TIME | SECONDDATE | TIMESTAMP | TINYINT | SMALLINT | INTEGER - don't need length  or scale

select 'NULL' as action, 'alter table "'|| schema_name ||'"."'|| table_name ||'" alter ("'||column_name ||'" '||data_type_name ||' NULL) ;' as SQLCMD

from table_columns

where is_nullable = 'TRUE'

and schema_name ='LARS'

and data_type_name in ('DATE', 'TIME', 'SECONDDATE', 'TIMESTAMP', 'TINYINT', 'SMALLINT', 'INTEGER' )

and scale is not null

To use it, make sure to replace 'LARS' with your schema name .

In order to get it into a file you might use hdbsql and use the -o <outputfile> switch to redirect the output into a file.

- Lars

[Former Member · Accepted solution]

Whoa!!! Thanks Lars....I am short of time and I have found a workaround to get it done using the previous SQL statement. Hope it works!

[former_member184768 · Accepted solution]

Now this is what separates a "GURU" like Lars from newbies like me 🙂

Really AWESOME..

Regards,

Ravi

[lbreddemann · Accepted solution]

Ravindra, you got around the main conceptual hurdle, using SQL to generate SQL commands, and that's what was the difficult thing here.

The rest is 'just' implementation .

- Lars

[Former Member · Accepted solution]

Lars,

This is great stuff!!  If there is a way to use SQL to generate the complete DDL for a table, that would be awesome.  In my example, I need to add columns to a table, then "reset" the table for the next use of the table to it's base structure before adding other columns.  Since table creation also involves things like keys, identity, etc....getting the DDL, like we get from the Studio, what be needed.  Any ideas?

-Andy

[Former Member · Accepted solution]

found this:

call get_object_definition('<schema>', '<table>');

[lbreddemann · Accepted solution]

nothing like some working google skills, hm?

[former_member184768]

How about the following:

select

'ALTER TABLE "' || table_name || '" ALTER COLUMN "' || column_name || '" '|| DATA_TYPE_NAME || '(' || length || ') NOT NULL;'

from

table_columns where column_name = '<your column name>';

This will generate a script which you can use to alter the NULL constraint.

You can modify the where condition to suit your needs.

Regards,

Ravi

[Former Member]

Hi Ravi,

Thanks for the turnaround. That will still be a mundane task. But I can use the same query to work out something in the system. Can you please tell me how do I export the contents of the query (all result rows) into a CSV file. EXPORT does not work.

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE_NAME, LENGTH FROM TABLE_COLUMNS WHERE SCHEMA_NAME = 'MY_SCHEMA'.

Problem is I have 8000 rows for the selection. And unable to figure out the way to copy the result set.

Regards,

KG

[Former Member]

Got done with Preferences and increasing the value for the Result Set. But curious to know what if my result set goes into lakhs of rows. How do we export?