Toad World® Forums

Problems With Generated SQL On Default Constraints


#1

Hi,

If I set a default value for an attribute in the model, I have two issues that occur in the generated SQL.

  1. The SQL contains the default constraint creation as a separate statement as well as in the create table definition which leads to a “constraint already exists” error when run:-

CREATE TABLE mytable

(id int not null,

somefield bit CONSTRAINT [myconstraint] DEFAULT ((0)) NOT NULL,

… the rest)

This works fine and creates the constraint myconstraint, but then there is another statement generated after the create table:-

ALTER TABLE mytable ADD CONSTRAINT myconstraint DEFAULT ((0)) FOR somefield

As the constraint is already there, this errors.

  1. If an existing table is being updated and thus the data is being preserved, the drop constraint statements for the original table only include drops for the FK constraints and do not have drops for the default constraints. So when the temp table create is run which contains the default constraint, another error occurs saying constraint already exists.

I have been through the settings as best I can to see if I can control this, but have found nothing so far.

Is it a bug or is it something in configuration I can fix or workaround?

Cheers
Dan


#2

Hi,

what is your target database platform, please? And can you attach sample model? The same constraint should not be generated twice, of course.

Thanks,

Vaclav


#3

Hi Vaclav,

Apologies, I knew I forgot to mention something! It’s sql server 2008r2.

I’ll send over a sample model now, just need to knock one up.

Cheers
Dan

From: Vaclav Frolik [mailto:bounce-Vaclav_Frolik@toadworld.com]
Sent: 18 July 2014 09:33
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 Vaclav Frolik

Hi,

what is your target database platform, please? And can you attach sample model? The same constraint should not be generated twice, of course.

Thanks,

Vaclav

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.


#4

Hi Vaclav,

I have attached a sample model with two tables. I just went through the steps to confirm the order to reproduce:-

  •      Make sure the option to preserve data using temporary tables is on for Microsoft SQL Server in Options->Model->Physical Model
    
  •      Generate the initial ddl script from the model and use it to create the new db in sql server 2008
    
  •      Make a small change to the model (I changed the channel column short_name from 256 to 50 and the removed the short_ from the name (to leave just “name”)
    
  •      Do a Synchronize->Generate Change script against the newly created db using all the defaults in the wizard
    

I have also attached the resulting sql from my test – in it you can see that there is no drop statement for the default constraint before the creation of the temporary table and there is also a create default constraint statement after the temp table rename.

Cheers
Dan

From: Vaclav Frolik [mailto:bounce-Vaclav_Frolik@toadworld.com]
Sent: 18 July 2014 09:33
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 Vaclav Frolik

Hi,

what is your target database platform, please? And can you attach sample model? The same constraint should not be generated twice, of course.

Thanks,

Vaclav

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.


#5

I only see the alter.txt in the post, not the model file - did the txp attachment not make it?


#6

Still hasn’t appeared so I have renamed the extension and resending.

From: Vaclav Frolik [mailto:bounce-Vaclav_Frolik@toadworld.com]
Sent: 18 July 2014 09:33
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 Vaclav Frolik

Hi,

what is your target database platform, please? And can you attach sample model? The same constraint should not be generated twice, of course.

Thanks,

Vaclav

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.


#7

Hi, thanks for your replies. Please send the models to modeling@quest.com. I will get them from the email box. Thank you,

Vaclav


#8

Hi, I was able to reproduce it. Thank you. New change request was added to our system. TDM-705. We will try to fix that as soon as possible.

Regards,

Vaclav


#9

That’s great, thanks Vaclav!

Cheers

Dan

From: Vaclav Frolik [mailto:bounce-Vaclav_Frolik@toadworld.com]
Sent: 18 July 2014 13:01
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 Vaclav Frolik

Hi, I was able to reproduce it. Thank you. New change request was added to our system. TDM-705. We will try to fix that as soon as possible.

Regards,

Vaclav

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.


#10

Hi,

the bug has been fixed. Next commercial version (should be relased in two months) will work properly.

Regards,

Vaclav & TDM team


#11

Hi Vaclav,

That’s great news , thanks! Do you know if there are there any workarounds I can do in the meantime?

Cheers

Dan

From: Vaclav Frolik [mailto:bounce-Vaclav_Frolik@toadworld.com]
Sent: 21 July 2014 12:53
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 Vaclav Frolik

Hi,

the bug has been fixed. Next commercial version (should be relased in two months) will work properly.

Regards,

Vaclav & TDM team

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.


#12

Hi,

unfortunately there is no workaround.

Regards,

Vaclav


#13

Hi,

The new version worked fine until I added a new mandatory default constraint, but then there was another problem.

The script now adds in the default constraint after the insert statement so any mandatory new defaults will be violated due to null insert.

I think it needs to keep existing constraints as separate create statements to avoid conflict on creating duplicate name (as the recent fix has provided), but then I think that it needs to make new constraints part of table create statement to ensure default is applied during existing data insert. Or something similar to take into account new and old constraints need managing slightly differently.

Thanks


#14

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


#15

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

  •      Script temp table create
    

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)

  •      Insert data from original table to temp table
    

o The new field gets its value from the default and therefore does not cause any “inserting null into not null field” issues

  •      Drop original table
    
  •      Rename temp table to original table name
    
  •      Add default constraints to renamed tabled
    

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.


#16

Hello again Dan,

First, thank you for the detailed problem description, it helped us to understand your issue completely.

The different order of generation of the existing and the new default constraints seems to a problem, we are grateful that you have told us it doesn’t work in your situation. As for your solution, it seems to be a good one and we might eventually implement the same one in TDM, once we test it thoroughly and find no other issues.

Also, if you do not name your new default constraint, it will be automatically created in the Create Table section of the temporary table. But I suppose you want to specifically name your constraints. We will try to resolve the issue as soon as we can.

Once again, thank you for reaching out!

Regards,

Lukas


#17

Hi Lukas,

No problem at all! Looking forward to the next release J

Cheers

Dan

From: Lukas Knapek [mailto:bounce-LukasKnapek@toadworld.com]
Sent: 21 April 2015 09:42
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 again Dan,

First, thank you for the detailed problem description, it helped us to understand your issue completely.

The different order of generation of the existing and the new default constraints seems to a problem, we are grateful that you have told us it doesn’t work in your situation. As for your solution, it seems to be a good one and we might eventually implement the same one in TDM, once we test it thoroughly and find no other issues.

Also, if you do not name your new default constraint, it will be automatically created in the Create Table section of the temporary table. But I suppose you want to specifically name your constraints. We will try to resolve the issue as soon as we can.

Once again, thank you for reaching out!

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.