Correção em foreign key

rtenorioh · Oct 16, 2024 · fc4e30f · fc4e30f
1 parent 679c0d2
commit fc4e30f
Show file tree

Hide file tree

Showing 2 changed files with 30 additions and 26 deletions.
diff --git a/backend/src/database/migrations/20240921112934-alter-userId-foreign-key-on-tickets.ts b/backend/src/database/migrations/20240921112934-alter-userId-foreign-key-on-tickets.ts
@@ -2,13 +2,13 @@ import { QueryInterface } from "sequelize";
 
 module.exports = {
   up: async (queryInterface: QueryInterface) => {
-    await queryInterface.removeConstraint("tickets", "tickets_ibfk_2");
+    await queryInterface.removeConstraint("Tickets", "Tickets_ibfk_2");
 
-    await queryInterface.addConstraint("tickets", ["userId"], {
+    await queryInterface.addConstraint("Tickets", ["userId"], {
       type: "foreign key",
-      name: "tickets_ibfk_2",
+      name: "Tickets_ibfk_2",
       references: {
-        table: "users",
+        table: "Users",
         field: "id"
       },
       onDelete: "CASCADE",
@@ -17,13 +17,13 @@ module.exports = {
   },
 
   down: async (queryInterface: QueryInterface) => {
-    await queryInterface.removeConstraint("tickets", "tickets_ibfk_2");
+    await queryInterface.removeConstraint("Tickets", "Tickets_ibfk_2");
 
-    await queryInterface.addConstraint("tickets", ["userId"], {
+    await queryInterface.addConstraint("Tickets", ["userId"], {
       type: "foreign key",
-      name: "tickets_ibfk_2",
+      name: "Tickets_ibfk_2",
       references: {
-        table: "users",
+        table: "Users",
         field: "id"
       },
       onDelete: "SET NULL",

diff --git a/backend/src/database/migrations/20240921153422-alter-queueId-foreign-key-on-tickets.ts b/backend/src/database/migrations/20240921153422-alter-queueId-foreign-key-on-tickets.ts
@@ -2,41 +2,45 @@ import { QueryInterface } from "sequelize";
 
 export default {
   up: async (queryInterface: QueryInterface) => {
-    // Remover a constraint antiga
-    await queryInterface.removeConstraint(
-      "tickets",
-      "Tickets_queueId_foreign_idx"
-    );
+    const [results]: any = await queryInterface.sequelize.query(`
+      SELECT CONSTRAINT_NAME
+      FROM information_schema.KEY_COLUMN_USAGE
+      WHERE TABLE_NAME = 'Tickets' AND COLUMN_NAME = 'queueId';
+    `);
+
+    if (results.length > 0) {
+      const constraintName = results[0].CONSTRAINT_NAME;
+      if (constraintName) {
+        await queryInterface.removeConstraint("Tickets", constraintName);
+      }
+    }
 
-    // Adicionar a nova constraint com SET NULL e CASCADE
-    await queryInterface.addConstraint("tickets", ["queueId"], {
+    await queryInterface.addConstraint("Tickets", ["queueId"], {
       type: "foreign key",
-      name: "Tickets_queueId_foreign_idx",
+      name: "Tickets_queueId_custom_foreign",
       references: {
-        table: "queues",
+        table: "Queues",
         field: "id"
       },
-      onDelete: "SET NULL", // Ao deletar, coloca NULL no campo queueId
-      onUpdate: "CASCADE" // Ao atualizar o id na tabela queues, atualiza o queueId em tickets
+      onDelete: "SET NULL",
+      onUpdate: "CASCADE"
     });
   },
 
   down: async (queryInterface: QueryInterface) => {
-    // Remover a constraint recém adicionada
     await queryInterface.removeConstraint(
-      "tickets",
-      "Tickets_queueId_foreign_idx"
+      "Tickets",
+      "Tickets_queueId_custom_foreign"
     );
 
-    // Restaurar a constraint antiga
-    await queryInterface.addConstraint("tickets", ["queueId"], {
+    await queryInterface.addConstraint("Tickets", ["queueId"], {
       type: "foreign key",
       name: "Tickets_queueId_foreign_idx",
       references: {
-        table: "queues",
+        table: "Queues",
         field: "id"
       },
-      onDelete: "RESTRICT",
+      onDelete: "SET NULL",
       onUpdate: "CASCADE"
     });
   }