describe student;Name Type Nullable Default Comments ..."/>
您好,登錄后才能下訂單哦!
查看兩個(gè)表的結(jié)構(gòu)
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
查看兩個(gè)表約束基本信息
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
查看約束建立那些列結(jié)構(gòu)
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
關(guān)聯(lián)兩個(gè)表的id號(hào)的時(shí)候報(bào)錯(cuò)(外鍵關(guān)聯(lián)主鍵)
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: 此列列表的唯一關(guān)鍵字或主鍵不匹配
解決思路
1、查看綁定主鍵與外鍵數(shù)據(jù)類型不同(數(shù)據(jù)是否相同)
2、查看主鍵是否還有其他約束(外鍵貌似只能綁定主鍵),是否還有其他的檢測約束等等
3、認(rèn)真觀察仔細(xì),鍵值的狀態(tài)是否開啟!
本題總結(jié)
因?yàn)榈谝粡埍碇麈I約束被關(guān)閉(約束狀態(tài)紅色標(biāo)記),所以導(dǎo)致外鍵正常連接,小錯(cuò)誤決成敗。
解決步驟
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
關(guān)聯(lián)成功!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。