该列列表没有唯一键或主键?

我已经输入了父目录的代码,但是当我尝试运行sql命令以生成子目录时,出现了匹配错误:“此列列表没有匹配的唯一键或主键”

父目录的生成如下:

CREATE TABLE dabel3_RetailCenter (store_id CHAR (4) NOT NULL, store_type VARCHAR2 (15) NOT NULL, store_st_num CHAR (4) NOT NULL,
                                  store_st_name VARCHAR2 (50) NOT NULL, store_city VARCHAR2 (15) NOT NULL, store_state VARCHAR2 (12) NOT NULL, store_ZIP CHAR (5),
                                  CONSTRAINT dabel3_store_id_pk PRIMARY KEY (store_id));
CREATE TABLE dabel3_Trans_Type (TM_ID CHAR (2) NOT NULL, TM_Type VARCHAR2 (15) NOT NULL, CONSTRAINT dabel3_Trans_type_TM_ID_pk PRIMARY KEY (TM_ID), 
                                CONSTRAINT TM_Type_UNQ UNIQUE (TM_TYPE));
CREATE TABLE dabel3_Trans_Mode (TM_ID CHAR (2) NOT NULL, TM_Route VARCHAR2 (15) NOT NULL, TM_Sched_Num CHAR (3) NOT NULL,
                                CONSTRAINT dabel3_Trans_mode_pk PRIMARY KEY (TM_ID, TM_Sched_Num), 
                                CONSTRAINT dabel3_trans_mode_TM_ID_fk FOREIGN KEY (TM_ID) REFERENCES dabel3_Trans_Type (TM_ID));

但是,子目录不会生成。关于我在做什么错的任何想法吗?

CREATE TABLE dabel3_Shipment (pkg_track_num VARCHAR2 (15) NOT NULL, pkg_weight CHAR (7) NOT NULL, pkg_dimensions VARCHAR2 (20), pkg_insurance VARCHAR2 (50), 
                              pkg_dest_city VARCHAR2 (20) NOT NULL, pkg_dest_state CHAR (2) NOT NULL, pkg_dest_ZIP CHAR (5) NOT NULL, pkg_due_date DATE NOT NULL,
                              store_id CHAR (4) NOT NULL, TM_ID CHAR (2) NOT NULL, TM_Sched_Num CHAR (3) NOT NULL, 
                              CONSTRAINT dabel3_pkg_track_num_pk PRIMARY KEY (pkg_track_num), 
                              CONSTRAINT dabel3_shipment_store_id_fk FOREIGN KEY (store_id) REFERENCES dabel3_RetailCenter (store_id),
                              CONSTRAINT dabel3_shipment_TM_ID_fk FOREIGN KEY (TM_ID) REFERENCES dabel3_Trans_Type (TM_ID),
                              CONSTRAINT dabel3_shipment_TM_Sched_Num_fk FOREIGN KEY (TM_Sched_Num) REFERENCES dabel3_Trans_Mode (TM_Sched_Num));
评论
  • 风煜祺
    风煜祺 回复

    The last table creation of dabel3_Shipment is wrong at:

    CONSTRAINT dabel3_shipment_TM_Sched_Num_fk 
    FOREIGN KEY (TM_Sched_Num) 
    REFERENCES dabel3_Trans_Mode (TM_Sched_Num) -- NOT a key
    

    The problem is that dabel3_Trans_Mode's column TM_Sched_Num is NOT a key. In order to establish a foreign key, it must point to a key in the remote table.

    The referenced table has a composite key (TM_ID, TM_Sched_Num). You are trying to use part of the key, and that's not a key. Use it whole, both columns, and problem solved.

    例如,您可以这样做:

    CONSTRAINT dabel3_shipment_TM_Sched_Num_fk 
    FOREIGN KEY (TM_ID, TM_Sched_Num) 
    REFERENCES dabel3_Trans_Mode (TM_ID, TM_Sched_Num)