Hi Lukas,
Apologies for the delay in responding, I was actually on holiday all last week J
The data doesn’t have any values at all (let alone null) because this scenario is about adding a new field to the table that has a default constraint (so that when the old data is added back in, the new field gets its value from the default and is therefore populated automatically during that insert).
I will try to clarify my meaning with a list of events in what I think is the order required in the change script:-
Scenario – change to a table with existing default constraint on one of the columns
-
Script temp table create with no default constraints
-
Insert data from original table to temp table
-
Drop original table
-
Rename temp table to original table name
-
Add default constraints to renamed tabled
This is the scenario that works fine now with the fix that you made earlier in this thread as it has removed the old problem of creating a temp table with the same default constraint name (thereby causing a duplicate constraint name violation as the original table still has its constraint of the same name in existence).
Scenario – change to a table by adding a new mandatory field that has a default constraint
o with NO default constraints on existing fields
o but WITH a default constraint on the new mandatory field (the field is new so there cannot be a constraint naming conflict as in the above scenario)
o The new field gets its value from the default and therefore does not cause any “inserting null into not null field” issues
o For the remaining existing fields
This is the scenario that I currently get “inserting null into not null field” issue.
By treating existing constraints and new constraints differently as above, both the “duplicate name” and “not null insert violation” issues can be removed.
I have been amending my generated scripts like this (i.e. removing the separate new constraint statement and adding it to the temp table create part) and it has been working fine J
Let me know if I should send snippets of these scripts to you for illustration, I can knock them up fairly quickly from existing stuff I think.
Cheers
Dan
From: Lukas Knapek [mailto:bounce-LukasKnapek@toadworld.com]
Sent: 13 April 2015 14:56
To: tdm@toadworld.com
Subject: RE: [Toad Data Modeler - Discussion Forum] Problems With Generated SQL On Default Constraints
RE: Problems With Generated SQL On Default Constraints
Reply by Lukas Knapek
Hello Dan,
Does your data contain any Null values? If yes, you would probably receive an error during inserting them into a table with not null constraint even if the default constraint was created before Insert Into statement. Inserting Null values into your table should cause an error regardless of the order of your constraints.
Or perhaps we didn’t understand your situation correctly? In that case, could you please clarify the current order of the generated items, the error you receive and your desired result?
Regards,
Lukas
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Modeler - General notifications altogether.
Toad Data Modeler - Discussion Forum
Flag this post as spam/abuse.