I am trying to use TypeORM with NestJS to make a migration in AzureSQL but the query failed in alter table to create FK. Below are the entities and errors:
query failed: ALTER TABLE "procedure" ADD CONSTRAINT "FK_f4621b8148f6622456a554adcc6" FOREIGN KEY ("patientId") REFERENCES "patient"("id") ON DELETE NO ACTION
ON UPDATE NO ACTION
error: QueryFailedError: Error: Could not create constraint or index. See previous errors.
[Nest] 15636 - 22/03/2024, 10:56:12 ERROR [TypeOrmModule] Unable to connect to the database. Retrying (8)...
QueryFailedError: Error: Could not create constraint or index. See previous errors.
at C:\Users\david\Documents\clinica-back\src\driver\sqlserver\SqlServerQueryRunner.ts:282:30
at C:\Users\david\Documents\clinica-back\node_modules\mssql\lib\base\request.js:440:25
at Request.userCallback (C:\Users\david\Documents\clinica-back\node_modules\mssql\lib\tedious\request.js:492:15)
at Request.callback (C:\Users\david\Documents\clinica-back\node_modules\tedious\src\request.ts:379:14)
at Parser.onEndOfMessage (C:\Users\david\Documents\clinica-back\node_modules\tedious\src\connection.ts:3713:22)
at Object.onceWrapper (node:events:628:28)
at Parser.emit (node:events:514:28)
at Readable.<anonymous> (C:\Users\david\Documents\clinica-back\node_modules\tedious\src\token\token-stream-parser.ts:30:12)
at Readable.emit (node:events:514:28)
at endReadableNT (node:internal/streams/readable:1359:12)
Patient Entity
@Entity()
export class Patient{
@PrimaryGeneratedColumn()
id: number
@Column({type: 'varchar', length: 11, nullable: false, unique: true})
cpf: string;
@Column({type: 'varchar', length: 200, nullable: false})
name: string;
@OneToMany(()=> Procedure, procedure=>procedure.patient, {
nullable: false,
cascade: true,
onDelete: 'CASCADE'} )
procedures: Procedure[]
}
Procedure Entity
@Entity()
export class Procedure{
@PrimaryGeneratedColumn()
id: number
@Column({type: 'varchar', nullable: false})
name: string;
@ManyToOne(()=>Patient, (patient)=> patient.procedures,
{ nullable: false, onDelete: 'NO ACTION', onUpdate: 'NO ACTION'})
patient: Patient;
}
one (patient) to many (procedures) relationship;
OBS: I tried the same context with MSSQL in a local machine and it works!
I finally found the problem. When I received the connection information, I was not informed of the db, so I tried on the master, as my user does not have permission to create a foreign key.