TABLE1 里有字段 ( A1, B1, C1, D1, E1, F1, G1, H1, I1) PK ( A1, B1, C1, D1, E1, F1)TABLE2 里有字段 ( A2, B2, C2, D2, E2, J, K, L, M,) PK ( A2, B2, C2, D2, E2)现在建立 TABLE2 的外键 ALTER TABLE TABLE2 ADD CONSTRAINT FK_TABLE2
FOREIGN KEY ( A2, B2, C2, D2, E2 )
REFERENCES TABLE1 ( A1, B1, C1, D1, E1);报错:
ORA-02270: no matching unique or primary key for this column-list
FOREIGN KEY ( A2, B2, C2, D2, E2 )
REFERENCES TABLE1 ( A1, B1, C1, D1, E1);报错:
ORA-02270: no matching unique or primary key for this column-list
PK ( A1, B1, C1, D1, E1, F1)
但是又会报这个错误
ORA-02256: number of referencing columns must match referenced columns
(
select A2, B2, C2, D2, E2
from table2 ) t2;select count(1) from
(
select distinct A2, B2, C2, D2, E2
from table2 ) t2;select count(1) from
(
select A1, B1, C1, D1, E1, F1
from table1 ) t1;select count(1) from
(
select distinct A1, B1, C1, D1, E1, F1
from table1 ) t1;
-- 也就是说在TABLE1表中 ( A1, B1, C1, D1, E1)
-- 存储重复的记录行必须删除!
FOREIGN KEY ( A2, B2, C2, D2, E2)
REFERENCES TABLE1( A1, B1, C1, D1, E1); CREATE TABLE "SCOTT"."TABLE1"
( "A1" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"B1" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"C1" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"D1" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"E1" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"F1" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"G1" VARCHAR2(20 BYTE),
"H1" VARCHAR2(20 BYTE),
"I1" VARCHAR2(20 BYTE),
CONSTRAINT "TABLE1_PK" PRIMARY KEY ("A1", "B1", "C1", "D1", "E1")
TABLESPACE "USERS"
)TABLESPACE "USERS" ; CREATE TABLE "SCOTT"."TABLE2"
( "A2" VARCHAR2(20 BYTE),
"B2" VARCHAR2(20 BYTE),
"C2" VARCHAR2(20 BYTE),
"D2" VARCHAR2(20 BYTE),
"E2" VARCHAR2(20 BYTE),
"J" VARCHAR2(20 BYTE),
"K" VARCHAR2(20 BYTE),
"L" VARCHAR2(20 BYTE),
"M" VARCHAR2(20 BYTE),
CONSTRAINT "FK_TABLE2" FOREIGN KEY ("A2", "B2", "C2", "D2", "E2")
REFERENCES "SCOTT"."TABLE1" ("A1", "B1", "C1", "D1", "E1")
)TABLESPACE "USERS" ;
-- TABLE2是外键的话,可以重复!
-- 但前提是:所有的外键行引用,在TABLE1中必须存在!-- 不知道你能不能明白!-- 楼主:先把“主”、“外”键的定义给整明白吧!.................................
FOREIGN KEY ( A2, B2, C2, D2, E2 )
REFERENCES TABLE1 ( A1, B1, C1, D1, E1);--此处你只用了5个列