SQL错误-无法添加外键约束 [英] SQL error - Cannot add foreign key constraint

问题描述

我正在尝试使用Java(jdbc)在数据库中创建一些表.下面的代码"只有两个字符串，每个字符串包含一个SQL CREATE TABLE更新，一个字符串用于表"store_sales"，一个用于表"store_returns"我得到
java.sql.SQLException:无法添加外键约束

I am trying to create some tables in a database with java (jdbc). The "code" underneath is just two strings, each containing an SQL CREATE TABLE update, one for the table "store_sales" and one for the table "store_returns" I get
java.sql.SQLException: Cannot add foreign key constraint

当我尝试使store_returns.sr_ticket_number成为引用代码"最后一行中的store_sales.ss_ticket_number的外键时发生的

that happens when I try to make store_returns.sr_ticket_number a foreign key referencing store_sales.ss_ticket_number in the last line of the "code"

有什么想法吗?预先感谢.

Any ideas? Thanks in advance.

"CREATE TABLE IF NOT EXISTS store_sales ("
        + "ss_sold_date_sk int,"
        + "ss_sold_time_sk int,"
        + "ss_item_sk int not null,"
        + "ss_customer_sk int,"
        + "ss_cdemo_sk int,"
        + "ss_hdemo_sk int,"
        + "ss_addr_sk int,"
        + "ss_store_sk int,"
        + "ss_promo_sk int,"
        + "ss_ticket_number int not null,"
        + "primary key (ss_item_sk,ss_ticket_number),"
        + "foreign key (ss_sold_date_sk) references date_dim (d_date_sk),"
        + "foreign key (ss_sold_time_sk) references time_dim (t_time_sk),"
        + "foreign key (ss_item_sk) references item (i_item_sk),"
        + "foreign key (ss_customer_sk) references customer (c_customer_sk),"
        + "foreign key (ss_cdemo_sk) references customer_demographics (cd_demo_sk),"
        + "foreign key (ss_hdemo_sk) references household_demographics (hd_demo_sk),"
        + "foreign key (ss_addr_sk) references customer_address (ca_address_sk),"
        + "foreign key (ss_store_sk) references store (s_store_sk),"
        + "foreign key (ss_promo_sk) references promotion (p_promo_sk)"
        + ")"

        ,

        "CREATE TABLE IF NOT EXISTS store_returns ("
        + "sr_return_date_sk int,"
        + "sr_return_time_sk int,"
        + "sr_item_sk int not null,"
        + "sr_customer_sk int,"
        + "sr_cdemo_sk int,"
        + "sr_hdemo_sk int,"
        + "sr_addr_sk int,"
        + "sr_store_sk int,"
        + "sr_reason_sk int,"
        + "sr_ticket_number int not null,"
        + "primary key (sr_item_sk,sr_ticket_number),"
        + "foreign key (sr_return_date_sk) references date_dim (d_date_sk),"
        + "foreign key (sr_return_time_sk) references time_dim (t_time_sk),"
        + "foreign key (sr_item_sk) references item (i_item_sk),"
        + "foreign key (sr_customer_sk) references customer (c_customer_sk),"
        + "foreign key (sr_cdemo_sk) references customer_demographics (cd_demo_sk),"
        + "foreign key (sr_hdemo_sk) references household_demographics (hd_demo_sk),"
        + "foreign key (sr_addr_sk) references customer_address (ca_address_sk),"
        + "foreign key (sr_store_sk) references store (s_store_sk),"
        + "foreign key (sr_reason_sk) references reason (r_reason_sk),"
        + "foreign key (sr_ticket_number) references store_sales (ss_ticket_number)"
        + ")"

推荐答案

您不能向store_sales中的(ss_item_sk，ss_ticket_number)组合外键的一部分添加外键约束.对于外键约束，您必须解决引用表的主键的所有部分.

You cannot add a foreign key constraint to a part of a combined foreign key like (ss_item_sk,ss_ticket_number) in store_sales. For a foreign key constraint you have to address all parts of the primary key of the referenced table.

类似

foreign key (sr_item_sk, sr_ticket_number) references store_sales (ss_item_sk,ss_ticket_number)

应该工作

这篇关于SQL错误-无法添加外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！