时间戳

首页 > 解决方案 > 错误号: 3780 外键约束 '%s' 中引用列 '%s' 和引用列 '%s' 不兼容

mysql - 错误号: 3780 外键约束 '%s' 中引用列 '%s' 和引用列 '%s' 不兼容

问题描述

DROP DATABASE IF EXISTS ProviderPatients;
CREATE DATABASE ProviderPatients;
USE ProviderPatients;

CREATE TABLE IF NOT EXISTS Date_Dim (
    Date_ID  integer not null,
    Date_ date,
    Full_Date_Des varchar(25) not null,
    Day_Of_Week int(11) not null,
    Calender_Year int(11) not null,
    Weekday_Indicator int(11) not null,
    primary key(Date_ID));

CREATE TABLE IF NOT EXISTS Insurer_DIM (
    Insurer_ID  int(11) not null,
    Insurer_Name varchar(25) not null,
    Line_Of_Buissness varchar(25) not null,
    primary key(Insurer_ID));

CREATE TABLE IF NOT EXISTS Member_DIM (
    Member_ID   int(11) not null,
    Member_Name varchar(25) not null,
    Age         int(11) not null,
    Ethnicity   varchar(25) not null,
    Health_Condition varchar(25) not null,
    primary key(Member_ID));

CREATE TABLE IF NOT EXISTS Geography_Dim (
    Geography_ID varchar(25) not null,
    Country     varchar(25) not null,
    State       varchar(10) not null,
    State_Code  int(11) not null,
    County_Code int(11) not null,
    primary key(Geography_ID));

CREATE TABLE Provider_Dim (
    Provider_ID int(11) not null, 
    Provider_Name VARCHAR(45) NOT NULL, 
    Gender Varchar(25) Not Null,
    NPI     Varchar(25) Not Null,
    Credential Varchar(25) Not Null, 
PRIMARY KEY(Provider_ID));

CREATE TABLE Eval_Fact_Table(
Date_ID int(11) not null,
Member_ID int(11) not null,
Provider_ID int(11) not null,
Insurer_ID int(11) not null,
Geography_ID int(11) not null,
Num_Visits int(11) not null,
Eval_Costint int(11) not null,
Eval_Start date not null,
Eval_End date not null, 
FOREIGN KEY (Date_ID) References Date_Dim (Date_Id) on delete restrict,
FOREIGN KEY (Member_ID) References Member_Dim (Member_ID) on delete restrict,
FOREIGN KEY (Geography_ID) References Geography_Dim (Geography_ID) on delete restrict,
FOREIGN KEY (Provider_ID) References Proveider_Dim (Provider_ID) on delete restrict,
FOREIGN KEY (Insurer_ID) References Insurer_Dim (Insurer_ID)on delete restrict);

错误号：3780；符号：ER_FK_INCOMPATIBLE_COLUMNS；SQL状态：HY000

消息：错误代码：3780。在外键约束“eval_fact_table_ibfk_3”中引用列“Geography_ID”和引用列“Geography_ID”不兼容。

标签： mysqlerror-code

解决方案

错误Referencing column 'Geography_ID' and referenced column 'Geography_ID' in foreign key constraint 'eval_fact_table_ibfk_3' are incompatible.

很清楚，列不兼容：

CREATE TABLE IF NOT EXISTS Geography_Dim (
    Geography_ID varchar(25) not null,

CREATE TABLE Eval_Fact_Table(
... truncated
    Geography_ID int(11) not null,

使它们具有相同的类型或删除外键约束。

您可以在文档中阅读有关外键约束的更多信息，最有趣的部分是

外键和引用键中的对应列必须具有相似的数据类型。

在你的情况下这不是真的：varchar(25)vs。int(11)

推荐阅读