Toad World® Forums

Alter a full table with TOAD Data Modeler (Please Help)


As most of you may know, when you are going to add a mandatory(not null) column to an existing table, the table must be empty in order to be able to alter it.

Another Modeling tool called E/R Studio during the comparison detects if the table is empty or not, if not it renames the existing table and create a new one with the new column included and then the generated script move the data from the renamed table to the new one. When I did the compare in Toad Data modeler the result was not the same,it only gave me the alter statement. Statement that is not possible to be executed by the DBMS given that the table needs to be empty to make the alter possible. Do you know if Toad Data modeler handles in certain way this particular case or is a feature that Toad needs to include?

Message was edited by: coqui


That sounds like a lot of work. I would do the following:

Add the field as nullable
Enter the data for the field from wherever
Make the field NOT NULL



ThanksReedda for your approach, but when you are working with a data warehouse where tables has millions of records this procedure could be a little tedious, i would like to know if Toad Data Modeler can handle this situation; a more automated form.



I don’t know what you target database is, but AFAIK in Oracle, you should be able to execute simple alter statement - the only condition is that there cannot be NULL value in your records. If there is NULL value, the alter statement execution will fail, but the same will happen when you try to move data from temporary table to your newly created table with Not Null column.

Back to your question: You can put something to After Script (table properties dialog) in your original model (commented line, for example) and put something different to After Script in your modified model (modified commented line) and then do the comparison. Toad Data Modeler will generate temporary table and commands for moving data from original table to temp table, deletion of original table and renaming of temp table to orig name.

But again, if there is a null value in your table, the data migration will fail.

Note: It is not recommended to use TDM for generation of alter scripts for production databases with millions of records. TDM is mainly for dev purposes.



Message was edited by: vaclav