Menu
I am building a .NET disconnected client-server application that uses Entity Framework 5 (EF5) to generate a SQL Server CE 4.0 database from POCOs. The application allows the user to perform a bulk copy of data from the network SQL Server into the client's SQL Server CE database. This is very (VERY) slow, due to the constraints and indexes created by EF5. Temporarily dropping the constraints and indexes will reduce the 30-minute wait to 1 minute or less.
Before starting the bulk copy, the application executes queries to drop the constraints and indexes from the SQL Server CE tables. However, the commands fail, because EF5 created constraint names include the table schema name, dot, and table name. The dot in the constraint name is causing the drop command to fail, due to a parsing issue.
For example, POCO Customer creates table dbo.Customer with the primary key constraint PK_dbo.Customer_Id. The database performs as expected.
However, upon executing non-query:
ALTER TABLE Customer DROP CONSTRAINT PK_dbo.Customer;
SQL Server Compact ADO.NET Data Provider returns an error:
There was an error parsing the query.
[ Token line number = 1, Token line offset = 57, Token in error = . ]
Of course, using a secondary DataContext object that does not have foreign keys generate the database without the constraints, and then add them later works; but, that requires maintaining two DataContext objects and hopefully not forgetting to keep both updated. Therefore, I am looking for one of two solutions:
Compose the DROP statement in such a way that the . character is parsed
Prevent EF5 from using the . character in the constraint and index names
Thank you in advance for your help!
966
Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.
Use the view table_constraints in the information_schema schema. The column table_name gives you the name of the table in which the constraint is defined, and the column constraint_name contains the name of the constraint.
The Unique Constraint ensures the uniqueness of all values and no duplicate values are entered in a column of a table. The unique constraints can be created using CREATE or ALTER TABLE T-SQL. Add unique constraints after columns definitions in the CREATE TABLE statement.
Wrap that bad boy in a []. It tells the parser that everything inside is the key name.
ALTER TABLE Customer DROP CONSTRAINT [PK_dbo.Customer];
Should run fine. Personally I just wrap every identifier in brackets to avoid this exact issue. So I would write this query like this.
ALTER TABLE [Customer] DROP CONSTRAINT [PK_dbo.Customer];
I think it's more readable that way because you can instantly see identifiers.
140
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
