bug/feature: schema updates don't actually work in practice

[mindplay]

If you have any data in your tables, the generated schema updates do not actually work - if you apply any of the generated constraints when you generate/update your schema, a subsequent update on the table will often violate those constraints.

I don't know T-SQL or SQL Server intimately, but my guess is, there is probably some way to temporarily disable specific constraints? If not, the generated schema updates would need to remove the constraints, apply the updates, and then apply the constraints again.

Or perhaps there is some way to temporarily turn off all constraint checks while performing updates? If so, the generated schema updates should include those statements.

I have only attempted to perform small schema updates on individual tables so far - for the most part, I edit the generated schema updates by hand, or in some cases, simply perform the schema updates entirely by hand using SSMS, for this reason and various reasons mentioned here.

But long-term, probably within 1-2 months, I need to be able to generate working schema migration scripts.

On a related note, do you have a public view of your bug-tracker anywhere? I have so many open "issues" here on the forum - I would like to know what your priorities are, and to verify that these issues are being tracked and not just drowning out in this forum...

[mindplay]

For the record, the following statements should help with the constraint violations:

Code: Select all

ALTER TABLE foo NOCHECK CONSTRAINT ALL

Code: Select all

ALTER TABLE foo CHECK CONSTRAINT ALL

I suppose you would have to loop over all the tables and disable all the constraints, then make all the required changes to all the tables, then loop over all the tables in the schema and enable the constraints again.

Disabling constraints and making updates on a table-by-table basis probably wouldn't work? (or maybe it would?? I'm not sure.)

Microsoft posted some information and some lumpy-looking scripts here:

http://msdn.microsoft.com/en-us/magazine/cc163442.aspx

I would not recommend putting an entire "program" like that in every generated script - it's probably more "surgical" to just generate the statements you need, explicitly, since the schema is well-known to your tool already, without having to dynamically discover what needs to be turned off/on...

[Shalex]

I suppose you would have to loop over all the tables and disable all the constraints, then make all the required changes to all the tables, then loop over all the tables in the schema and enable the constraints again.

If a database is updated in the way that is not allowed by a particular constaint, you will get the corresponding error message when try to turn the constraint on back.

Constraint violation occurs when you try to add the NOT NULL column to the table in which there are some records. In this case you should construct the update script in the following way:
- add a nullable column to your table (DDL can be generated by Entity Developer)
- write the DML script for initializing your added column (should be done manually because it would not be correct if Entity Developer inserts some random values to the column automatically)
- make your nullable column NOT NULL (DDL can be generated by Entity Developer)

On a related note, do you have a public view of your bug-tracker anywhere? I have so many open "issues" here on the forum - I would like to know what your priorities are, and to verify that these issues are being tracked and not just drowning out in this forum...

We are planning to implement a public bug tracking system but there is no timeframe at the moment. You can check the status of any opened request by posting in the corresponding thread at the forum or by sending an e-mail.