MSSQL Incorrect syntax near the keyword 'CONSTRAINT' for enabling synchronize option

[Acetylen3]

Issue Description

Using MSSQL Server 2016.
For MSSQL, it generated incorrect alter table SQL for enabling synchronize option.
Looks like the SQL generator try to ALTER the Constraint, however, Constraint can only be dropped and added.

Expected Behavior

If the database is empty, which means synchronize for the first time, such query error will NOT appear.

Actual Behavior

query failed: ALTER TABLE "data_table_grant_permissions_script" ALTER COLUMN "environment" nvarchar(255) CONSTRAINT CHK_83191b32c3acc65e3b8735d35a_ENUM CHECK(environment IN ('production','development'))
error: { RequestError: Incorrect syntax near the keyword 'CONSTRAINT'.
    at handleError (C:\Users\sck433\Documents\repository\aida-eform-api-svc\node_modules\mssql\lib\tedious\request.js:374:15)
    at Connection.emit (events.js:198:13)
    at Connection.emit (C:\Users\sck433\Documents\repository\aida-eform-api-svc\node_modules\tedious\lib\connection.js:1055:18)
    at Parser.tokenStreamParser.on.token (C:\Users\sck433\Documents\repository\aida-eform-api-svc\node_modules\tedious\lib\connection.js:1160:12)
    at Parser.emit (events.js:198:13)
    at Readable.Parser.parser.on.token (C:\Users\sck433\Documents\repository\aida-eform-api-svc\node_modules\tedious\lib\token\token-stream-parser.js:27:14)      
    at Readable.emit (events.js:198:13)
    at addChunk (C:\Users\sck433\Documents\repository\aida-eform-api-svc\node_modules\tedious\node_modules\readable-stream\lib\_stream_readable.js:298:12)        
    at readableAddChunk (C:\Users\sck433\Documents\repository\aida-eform-api-svc\node_modules\tedious\node_modules\readable-stream\lib\_stream_readable.js:280:11)
    at Readable.push (C:\Users\sck433\Documents\repository\aida-eform-api-svc\node_modules\tedious\node_modules\readable-stream\lib\_stream_readable.js:241:10)   
  code: 'EREQUEST',
  originalError:
   { Error: Incorrect syntax near the keyword 'CONSTRAINT'.
       at handleError (C:\Users\sck433\Documents\repository\aida-eform-api-svc\node_modules\mssql\lib\tedious\request.js:372:19)
       at Connection.emit (events.js:198:13)
       at Connection.emit (C:\Users\sck433\Documents\repository\aida-eform-api-svc\node_modules\tedious\lib\connection.js:1055:18)
       at Parser.tokenStreamParser.on.token (C:\Users\sck433\Documents\repository\aida-eform-api-svc\node_modules\tedious\lib\connection.js:1160:12)
       at Parser.emit (events.js:198:13)
       at Readable.Parser.parser.on.token (C:\Users\sck433\Documents\repository\aida-eform-api-svc\node_modules\tedious\lib\token\token-stream-parser.js:27:14)
       at Readable.emit (events.js:198:13)
       at addChunk (C:\Users\sck433\Documents\repository\aida-eform-api-svc\node_modules\tedious\node_modules\readable-stream\lib\_stream_readable.js:298:12)
       at readableAddChunk (C:\Users\sck433\Documents\repository\aida-eform-api-svc\node_modules\tedious\node_modules\readable-stream\lib\_stream_readable.js:280:11)
       at Readable.push (C:\Users\sck433\Documents\repository\aida-eform-api-svc\node_modules\tedious\node_modules\readable-stream\lib\_stream_readable.js:241:10)
     info:
      ErrorMessageToken {
        name: 'ERROR',
        event: 'errorMessage',
        number: 156,
        state: 1,
        class: 15,
        message: 'Incorrect syntax near the keyword \'CONSTRAINT\'.',
        serverName: 'DC6HAHLDB01\\HAHL_DC6_MP1',
        procName: '',
        lineNumber: 1 } },
  name: 'RequestError',
  number: 156,
  lineNumber: 1,
  state: 1,
  class: 15,
  serverName: 'DC6HAHLDB01\\HAHL_DC6_MP1',
  procName: '',
  precedingErrors: [] }

export enum environment {
    PROD = "production",
    DEV = "development"
}

@Entity({ name: "data_table_grant_permissions_script" })
export class TableGrantPermissionsScriptCatalogEntity extends BaseEntity {

    @PrimaryGeneratedColumn('uuid', { name: "script_id" })
    id: string;

    @Column("simple-enum", { enum: formAction, nullable: false })
    action: string;

    @Column("simple-enum", { enum: dataPlatform, nullable: false })
    dataPlatform: string;

    @Column("simple-enum", { enum: environment, default: null })
    environment: string;

    @Column("simple-enum", { enum: dataPlatformEnv, default: null })
    dataPlatformEnv: string;

    @CreateDateColumn({ name: "create_time" })
    createTime: Date;

    @UpdateDateColumn({ name: "update_time" })
    updateTime: Date;

    @Column({ name: "updated_by", nullable: false })
    updatedBy: string

    @Column("text", { default: null })
    script1: string

    @Column("text", { default: null })
    script2: string

    @Column("text", { default: null })
    script3: string

    @Column("simple-enum", { name: "database_type", enum: dataBaseType, default: dataBaseType.default })
    databaseType: string;

    @Column({ name: "database_name", default: null })
    databaseName: string;
}

Steps to Reproduce

1. npm run start:dev (nest start --watch)
2. wait for Module initialized
3. Above query failed shows

My Environment

Dependency	Version
Operating System	Windows 10
Node.js version	v10.23.0
Typescript version	v4.0.3
TypeORM version	v0.2.34
MSSQL Server 2016	v13.0.5026.0

Additional Context

Relevant Database Driver(s)

• aurora-data-api
• aurora-data-api-pg
• better-sqlite3
• cockroachdb
• cordova
• expo
• mongodb
• mysql
• nativescript
• oracle
• postgres
• react-native
• sap
• sqlite
• sqlite-abstract
• sqljs
• [ x] sqlserver

Are you willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time, and I know how to start.
• Yes, I have the time, but I don't know how to start. I would need guidance.
• No, I don't have the time, although I believe I could do it if I had the time...
• [ x] No, I don't have the time and I wouldn't even know how to start.

[imnotjames]

So the only way to make this work would be to drop the column and re-add it?

If not, what is the expected SQL?

[chris-digitise]

To fix this, the appropriate script would be

ALTER TABLE dbo.MyTable
DROP CONSTRAINT CHK_randomname

then

ALTER TABLE dbo.MyTable WITH NOCHECK ADD CONSTRAINT
CHK_randomname CHECK  add_the_check_condition_here