Single

ORA-02270: 此列列表的唯一关键字或主键不匹配一些思路

ORA-02270: 此列列表的唯一关键字或主键不匹配一些思路

查看两个表的结构

SQL> describe student;

Name Type Nullable Default Comments

———— ———— ——– ——- ——–

ID NUMBER(10)

TEACHER_ID NUMBER(10) Y

STUDENT_NAME VARCHAR2(20) Y ‘嗯嗯嗯’

SAL NUMBER(20) Y

SEX VARCHAR2(2) Y

SQL> describe teacher;

Name Type Nullable Default Comments

———— ———— ——– ——- ——–

ID NUMBER(10) Y

TEACHER_NAME VARCHAR2(20) Y

查看两个表约束基本信息

SQL> select table_name,constraint_name,constraint_type,status from user_constraints where table_name=’TEACHER’;

TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE STATUS

—————————— —————————— ————— ——–

TEACHER TEACHER2 P DISABLED

TEACHER TEACHER C ENABLED

TEACHER UQ_NAME U ENABLED

SQL> select table_name,constraint_name,constraint_type,status from user_constraints where table_name=’STUDENT’;

TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE STATUS

—————————— —————————— ————— ——–

STUDENT CHK_STUDENT C ENABLED

STUDENT PK_STUDENT P ENABLED

查看约束建立那些列结构

SQL> select table_name,constraint_name,column_name from user_cons_columns where table_name=’TEACHER’;

TABLE_NAME CONSTRAINT_NAME COLUMN_NAME

—————————— —————————— ————————————————

TEACHER UQ_NAME TEACHER_NAME

TEACHER TEACHER2 ID

TEACHER TEACHER ID

SQL> select table_name,constraint_name,column_name from user_cons_columns where table_name=’STUDENT’;

TABLE_NAME CONSTRAINT_NAME COLUMN_NAME

—————————— —————————— ————————————————

STUDENT PK_STUDENT ID

STUDENT CHK_STUDENT SAL

STUDENT CHK_STUDENT SEX

关联两个表的id号的时候报错

SQL> alter table student add constraint fk_student foreign key (TEACHER_ID) references teacher (ID);

alter table student add constraint fk_student foreign key (TEACHER_ID) references teacher (ID)

解决思路

1、查看绑定主键与外键数据类型不同(数据是否相同)

2、查看主键是否还有其他约束(外键貌似只能绑定主键),是否还有其他的检测约束等等

3、认真观察仔细,键值的状态是否开启!

本题总结

因为第一张表主键约束被关闭,所以导致外键正常连接,小错误决成败。

解决步骤

SQL> alter table teacher modify constraint TEACHER2 ENABLE;

Table altered

SQL> alter table student add constraint fk_student foreign key (TEACHER_ID) references teacher (ID);

Table altered

关联成功!

:http://www.linuxidc.com/Linux/2018-01/150058.htm

暂无评论

发表评论