错误号:3780 引用列“%s"和外键约束“%s"中的引用列“%s"不兼容 [英] Error number: 3780 Referencing column '%s' and referenced column '%s' in foreign key constraint '%s' are incompatible

问题描述

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；SQLSTATE: HY000

Error number: 3780; Symbol: ER_FK_INCOMPATIBLE_COLUMNS; SQLSTATE: HY000

消息:错误代码:3780.在外键约束eval_fact_table_ibfk_3"中引用列Geography_ID"和引用列Geography_ID"不兼容.

Message:Error Code: 3780. Referencing column 'Geography_ID' and referenced column 'Geography_ID' in foreign key constraint 'eval_fact_table_ibfk_3' are incompatible.

推荐答案

错误 引用列 'Geography_ID' 和引用列 'Geography_ID' 在外键约束 'eval_fact_table_ibfk_3' 不兼容.

很清楚，列不兼容:

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,

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

Make them of same type or remove foreign key constraint.

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

You can read more about foreign key constraints in documentation, most interesting part is

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

Corresponding columns in the foreign key and the referenced key must have similar data types.

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

That is not true in your case : varchar(25) vs. int(11)

这篇关于错误号:3780 引用列“%s"和外键约束“%s"中的引用列“%s"不兼容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！