search

coldbox-modules / qb

Fluent Query Builder for CFML
https://qb.ortusbooks.com/
MIT License
40 stars 35 forks source link

Dropping Indexes in SQL Server uses a different syntax than DROP CONSTRAINT #212

Open elpete opened 2 years ago

elpete commented 2 years ago 
DROP INDEX table_name.index_name;
jclausen commented 2 years ago

This affects any migrations for SQL server which require indices to be dropped on a column before removal. Because of this, I suggest adding a dropIndex method to the dialects, which will pass through to dropConstraint on all except SQL Server. The corresponding addIndex method can be added as well, which will allow more precision during schema creation. I can PR this if you are good with the above approach?

elpete commented 2 years ago

I'm fine with the method name as well, but I think I'd also like dropConstraint to use to correct syntax on SQL Server. We should be able to detect when we are passed a TableIndex and parse accordingly.

jclausen commented 2 years ago

The syntax it currently uses is correct for what SQL server considers to be a constraint ( e.g. Foreign Keys, Unique index ). The problem is that SQL Server does not see a simple non-clustered index on a column as a "constraint". To me it makes more sense to separate those methods, rather than change the method signature, since SQL Server sees a constraint and an index as different.

elpete commented 2 years ago

The reason I'd like both to work is for engine portability. I don't want to have to change my qb code if I switch from MySQL to SQL Server.