查看两个表的结构
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)
ORA-02270: 此列列表的唯一关键字或主键不匹配
解决思路
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
关联成功!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。