Jira issue originally created by user flip101:
When running: php app/console doctrine:schema:update --force
An exception occurred while executing 'ALTER TABLE tableName ALTER COLUMN id INT IDENTITY NOT NULL':
SQLSTATE: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'IDENTITY'.
According to this stackoverflow http://stackoverflow.com/a/1049305/1833322 MSSQL does not support this query.
Comment created by @deeky666:
Obviously SQL Server doesn't support this. So what solution would you expect here? IMO such a scenario should be avoided as there is no reasonable way to deal with changing identity columns. I guess most people are not even aware of this. The solutions available are very risky (creating new schema and then migrating data for example) and can take very long to be finished and is extremly error prone. I don't know if we should support one of those workarounds.
Comment created by flip101:
The bug is about the SQL statement that does not work.
1. One solution could be to prevent generating this statement and issue a warning instead. (not preferred)
Two other solutions are mentioned in that Stackoverflow post I mentioned, namely:
2. Create a new table with identity & drop the existing table
3. Create a new column with identity & drop the existing column
That is just the way things have to be done in SQL Server (unfortunately), so it wouldn't be a workaround. I think this is a very reasonable way and in fact similar solutions to similar problems have already been implemented into Doctrine. See http://www.doctrine-project.org/blog/doctrine-2-4-released.html "ALTER TABLE support for SQLite (by hason) by creating new tables, moving all the data and then renaming."
Comment created by @beberlei:
[flip101] not many people actually use Sqlite in production, its more of a "play database" with Doctrine, wheras SQL Server carries much more weigh. I don't want to implement this kind of workaround, [deeky] what do you think is safe here?
Not to argue .. but it's not a workaround
Really .. if it is ever implemented it would have to be in a few steps because SQL Server does not accept this one-line command like that. So basically when you don't want this solution it means that will never be implemented.
Talking about safe .. mind that the safety should also be ensured in other area's like 1. don't run --force on a production database 2. make backups. So when regarding the safety it's a very narrow area to take a look at. The operation could be setup in such a way that if the new table/column could not be created then the old one doesn't get deleted. That way you have always the safety like a database transaction.
I guess no point in making a Proof of Concept, because the general outline of the problem is actually not that complicated ..
Thx guys for the input on this issue.
Benjamin Eberlei we could of course use one of the approaches Flip mentioned, but the question is what to do in case something goes wrong during one of the steps required to alter an IDENTITY column. If it is acceptable that there exist "dead" tables/columns in case something goes wrong, we could try implementing that.