Блог

По какой-то причине у меня получилось несколько внешних ключей. Это странно, потому что позже я пытаюсь использовать в recipeID качестве внешнего ключа для левого соединения. Для этой операции sequelize выбирает использование RecipeURL , которое нигде не определено в схеме ниже.

 const Recipe = sequelize.define('Recipe', {
    URL: {
        type: DataTypes.STRING(512),
        allowNull: false,
        unique: true,
        primaryKey: true
    },
    contentID: {
        type: DataTypes.UUID,
        allowNull: true
    },
    source: {
        type: DataTypes.STRING,
        allowNull: false
    },
    title: {
        type: DataTypes.STRING,
        allowNull: true
    },
    isRecipe: {
        type: DataTypes.BOOLEAN,
        allowNull: true,
        defaultValue: null
    },
    ContentsURL: {
        type: DataTypes.STRING(512),
        allowNull: true
    },
    ScreenshotURL: {
        type: DataTypes.STRING(512),
        allowNull: true
    },
});

const Comment = sequelize.define('Comment', {
    ID: {
        type: DataTypes.STRING,
        primaryKey: true,
        allowNull: false
    },
    text: {
        type: DataTypes.TEXT,
        allowNull: false
    },
    name: {
        type: DataTypes.TEXT,
        allowNull: true
    },
    date: {
        type: DataTypes.DATE,
        allowNull: true
    }
});


Recipe.hasMany(Comment, { as: "comments" });
Comment.belongsTo(Recipe, {
    foreignKey: "recipeID",
    as: "recipe",
});

(async () => {
    await sequelize.sync({alter: true, force: true})
    process.exit(1)
})();

  

Запуск его:

 $ node db.js
Executing (default): DROP TABLE IF EXISTS `Comments`;
Executing (default): DROP TABLE IF EXISTS `Recipes`;
Executing (default): DROP TABLE IF EXISTS `Recipes`;
Executing (default): CREATE TABLE IF NOT EXISTS `Recipes` (`URL` VARCHAR(512) NOT NULL UNIQUE , `contentID` CHAR(36) BINARY, `source` VARCHAR(255) NOT NULL, `title` VARCHAR(255), `isRecipe` TINYINT(1) DEFAULT NULL, `ContentsURL` VARCHAR(512), `ScreenshotURL` VARCHAR(512), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, PRIMARY KEY (`URL`)) ENGINE=InnoDB;
Executing (default): SHOW FULL COLUMNS FROM `Recipes`;
Executing (default): SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'Recipes' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='recipe' AND REFERENCED_TABLE_NAME IS NOT NULL;
Executing (default): ALTER TABLE `Recipes` CHANGE `contentID` `contentID` CHAR(36) BINARY;
Executing (default): ALTER TABLE `Recipes` CHANGE `source` `source` VARCHAR(255) NOT NULL;
Executing (default): ALTER TABLE `Recipes` CHANGE `title` `title` VARCHAR(255);
Executing (default): ALTER TABLE `Recipes` CHANGE `isRecipe` `isRecipe` TINYINT(1) DEFAULT NULL;
Executing (default): ALTER TABLE `Recipes` CHANGE `ContentsURL` `ContentsURL` VARCHAR(512);
Executing (default): ALTER TABLE `Recipes` CHANGE `ScreenshotURL` `ScreenshotURL` VARCHAR(512);
Executing (default): ALTER TABLE `Recipes` CHANGE `createdAt` `createdAt` DATETIME NOT NULL;
Executing (default): ALTER TABLE `Recipes` CHANGE `updatedAt` `updatedAt` DATETIME NOT NULL;
Executing (default): SHOW INDEX FROM `Recipes`
Executing (default): DROP TABLE IF EXISTS `Comments`;
Executing (default): CREATE TABLE IF NOT EXISTS `Comments` (`ID` VARCHAR(255) NOT NULL , `text` TEXT NOT NULL, `name` TEXT, `date` DATETIME, `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `RecipeURL` VARCHAR(512), `recipeID` VARCHAR(512), PRIMARY KEY (`ID`), FOREIGN KEY (`RecipeURL`) REFERENCES `Recipes` (`URL`) ON DELETE SET NULL ON UPDATE CASCADE, FOREIGN KEY (`recipeID`) REFERENCES `Recipes` (`URL`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;
Executing (default): SHOW FULL COLUMNS FROM `Comments`;
Executing (default): SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'Comments' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='recipe' AND REFERENCED_TABLE_NAME IS NOT NULL;
Executing (default): ALTER TABLE `Comments` CHANGE `text` `text` TEXT NOT NULL;
Executing (default): ALTER TABLE `Comments` CHANGE `name` `name` TEXT;
Executing (default): ALTER TABLE `Comments` CHANGE `date` `date` DATETIME;
Executing (default): ALTER TABLE `Comments` CHANGE `createdAt` `createdAt` DATETIME NOT NULL;
Executing (default): ALTER TABLE `Comments` CHANGE `updatedAt` `updatedAt` DATETIME NOT NULL;
Executing (default): SELECT CONSTRAINT_CATALOG AS constraintCatalog, CONSTRAINT_NAME AS constraintName, CONSTRAINT_SCHEMA AS constraintSchema, CONSTRAINT_TYPE AS constraintType, TABLE_NAME AS tableName, TABLE_SCHEMA AS tableSchema from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE table_name='Comments' AND constraint_name = 'Comments_ibfk_1' AND TABLE_SCHEMA = 'recipe';
Executing (default): ALTER TABLE `Comments` DROP FOREIGN KEY `Comments_ibfk_1`;
Executing (default): SELECT CONSTRAINT_CATALOG AS constraintCatalog, CONSTRAINT_NAME AS constraintName, CONSTRAINT_SCHEMA AS constraintSchema, CONSTRAINT_TYPE AS constraintType, TABLE_NAME AS tableName, TABLE_SCHEMA AS tableSchema from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE table_name='Comments' AND constraint_name = 'Comments_ibfk_2' AND TABLE_SCHEMA = 'recipe';
Executing (default): ALTER TABLE `Comments` DROP FOREIGN KEY `Comments_ibfk_2`;
Executing (default): ALTER TABLE `Comments` ADD FOREIGN KEY (`RecipeURL`) REFERENCES `Recipes` (`URL`) ON DELETE SET NULL ON UPDATE CASCADE;
Executing (default): ALTER TABLE `Comments` ADD FOREIGN KEY (`recipeID`) REFERENCES `Recipes` (`URL`) ON DELETE SET NULL ON UPDATE CASCADE;

  

Посмотрите на последние две строки. Почему добавлены два внешних ключа? Я думаю, что это может быть проблема с кэшированием или что-то подобное. Так что, если бы я мог просто отредактировать файл внутренней схемы и прокомментировать эту ссылку RecipeURL, это, возможно, решило бы проблему.

Потому что вы объявили это так.

 Recipe.hasMany(Comment, { as: "comments" });
  

Здесь sequelize автоматически выбирает ваш первичный ключ в качестве внешнего ключа таблиц.
Ваш первичный ключ не является идентификатором. Ваш первичный ключ, который вы установили, является URL .

 Comment.belongsTo(Recipe, {
    foreignKey: "recipeID",
    as: "recipe",
});
  

В этом коде вы переопределяете внешний ключ по умолчанию. Вы вызываете это как recipeID .

укажите то же самое при Recipe ассоциации, и вы увидите только один внешний ключ.