Easily find issues by searching: #<Issue ID>
Example: #1832
Easily find members by searching in: <username>, <first name> and <last name>.
Example: Search smith, will return results smith and adamsmith
| Dev |
|
| QA |
|
| Priority | Major |
| Complexity | Unknown |
| Component | Tools - Compare - Schema |
| Version | 14.0 |
Using compare schema on a table with an unnamed check constraint shows differences even though I choose to ignore constraints differences. This problem appears on Sql Server, haven't tested in other databases.
Steps to reproduce:
Create this table in two different databases:
create table AAA_TEST (
A_FIELD char(1) not null default '0' CHECK ([A_FIELD]='1' OR [A_FIELD]='0')
)
Invoke schema compare for the table: compare shows a difference because in one db the check constraint is named DF__AAA_TEST__A_FIEL__2513F6FA and on the other it's named DF__AAA_TEST__A_FIEL__660CC658.
|
|
242 KB
|
|
163 KB
Hi,
In SQL Server, when a default value is specified for a column, SQL Server generates a SQL DEFAULT Constraint. This is different from a check constraint. Also, when specifying a default value, SQL Server allows the user to name this SQL Default Constraint. If no name is specified, SQL Server auto generates the name for the SQL Default Constraint
Our ADS GUI does allow users to specify a default value but does not allow users to specify the name of the SQL Default Constraint. Hence, it is auto-generated.
In your scenario, ADS is highlighting the differences between the default contraint names across the two databases. One option is to use the ALTER syntax to change the name of the default constraint to be the same across the 2 databases. Another option, is to script table creation and specify the default constraint name inside the script.
Let me know if neither of the options are viable for your use case.
Reference article: http://msdn.microsoft.com/en-us/library/aa175912(v=sql.80).aspx
Yes. I know all about default constraint names. This issue is about a flaw in schema compare that has no way of ignoring differences in constraint names.
Your suggestion that tables be altered to add constraints names makes no sense: this can mean doing thousands of changes to a database schema (in this case when I was only comparing about 50 tables, a fraction of the tables in the database it meant doing more than 600 changes to the schema) not to say anything about comparing production databases which can't be changed that easily.
I would suggest adding an option "Ignore differences in constraint names" to schema compare and changing the tool to do the comparison accordingly.
In my case, schema compare was about useless, because all the tables have check constraints and schema compare showed all tables as different, when there where only a few "real" differences between the tables.
Yes. I know all about default constraint names. This issue is about a flaw in schema compare that has no way of ignoring differences in constraint names.
Your suggestion that tables be altered to add constraints names makes no sense: this can mean doing thousands of changes to a database schema (in this case when I was only comparing about 50 tables, a fraction of the tables in the database it meant doing more than 600 changes to the schema) not to say anything about comparing production databases which can't be changed that easily.
I would suggest adding an option "Ignore differences in constraint names" to schema compare and changing the tool to do the comparison accordingly.
In my case, schema compare was about useless, because all the tables have check constraints and schema compare showed all tables as different, when there where only a few "real" differences between the tables.
Hi,
When ADS does a schema compare, it first extracts the relevant object information and then invokes a text diff routine to determine what has changed. The text diff routine is not aware of any SQL specific syntax -- it just simply does a text compare.
To address your use case and to still be able to use our diff routine as described above, I'll add an enhancement request as follows: Add a new schema compare option to "not generate constraint names for default values".
If this option is checked, the constraint names for default values will not be generated but the actual default value will still be generated and, hence, compared. I believe this will address your use case. Let me know if ti doesn't.
Hi,
When ADS does a schema compare, it first extracts the relevant object information and then invokes a text diff routine to determine what has changed. The text diff routine is not aware of any SQL specific syntax -- it just simply does a text compare.
To address your use case and to still be able to use our diff routine as described above, I'll add an enhancement request as follows: Add a new schema compare option to "not generate constraint names for default values".
If this option is checked, the constraint names for default values will not be generated but the actual default value will still be generated and, hence, compared. I believe this will address your use case. Let me know if ti doesn't.
I don't know, because "not generate constraint names for default values" is unclear: reading the text I would understand the option only applies to constraints on default column values (" a_field varchar(128) default 'abcd' ")
If the option was "Ignore system assigned constraint names" (or some similar text) I would agree that this addresses my case.
I don't know, because "not generate constraint names for default values" is unclear: reading the text I would understand the option only applies to constraints on default column values (" a_field varchar(128) default 'abcd' ")
If the option was "Ignore system assigned constraint names" (or some similar text) I would agree that this addresses my case.
And while we are discussing this, a case could be made for *another* option: "Ignore all constraint names". I could just be checking that the foreign keys are the same in the two tables, even if by mistake some have different constraint names.
And while we are discussing this, a case could be made for *another* option: "Ignore all constraint names". I could just be checking that the foreign keys are the same in the two tables, even if by mistake some have different constraint names.
We are investigating various options and should have more concrete proposals early next week.
We are investigating various options and should have more concrete proposals early next week.
We will go ahead with the "Ignore all constrain names" option. This requires a fair amount of work on our part so I will change the target release to ADS v14. However, it might get pushed to ADS v15 depending upon the overall complexity.
The problem with "Ignore system assigned constraint names" is that we don't always know what is a system constraint name. For instance, in the case of constraint names with default values in SQL Server, SQL Server does not tell us whether the constraint name is system generated or user generated.
We will go ahead with the "Ignore all constrain names" option. This requires a fair amount of work on our part so I will change the target release to ADS v14. However, it might get pushed to ADS v15 depending upon the overall complexity.
The problem with "Ignore system assigned constraint names" is that we don't always know what is a system constraint name. For instance, in the case of constraint names with default values in SQL Server, SQL Server does not tell us whether the constraint name is system generated or user generated.
complete... needs regression testing for all databases
complete... needs regression testing for all databases
Ivan,
You will also need to enhance our aquascript api to support this new option. Please take a look at: http://docs.aquafold.com/ads/12.0/openapi/com/aquafold/openapi/compare/AQCompareOptions.html
Ivan,
You will also need to enhance our aquascript api to support this new option. Please take a look at: http://docs.aquafold.com/ads/12.0/openapi/com/aquafold/openapi/compare/AQCompareOptions.html
Issue #8465 |
| Closed |
| Fixed |
| Resolved |
Completion |
| No due date |
| No fixed build |
| No time estimate |
1 issue link |
relates to #8653
Issue #8653Enable Ignore case doesnt work with Include Table Constraints Names option |
Hi,
In SQL Server, when a default value is specified for a column, SQL Server generates a SQL DEFAULT Constraint. This is different from a check constraint. Also, when specifying a default value, SQL Server allows the user to name this SQL Default Constraint. If no name is specified, SQL Server auto generates the name for the SQL Default Constraint
Our ADS GUI does allow users to specify a default value but does not allow users to specify the name of the SQL Default Constraint. Hence, it is auto-generated.
In your scenario, ADS is highlighting the differences between the default contraint names across the two databases. One option is to use the ALTER syntax to change the name of the default constraint to be the same across the 2 databases. Another option, is to script table creation and specify the default constraint name inside the script.
Let me know if neither of the options are viable for your use case.
Reference article: http://msdn.microsoft.com/en-us/library/aa175912(v=sql.80).aspx