Shulou(Shulou.com)06/01 Report--

View the structure of the two tables

SQL > describe student

Name Type Nullable Default Comments

--

ID NUMBER (10)

TEACHER_ID NUMBER (10) Y

STUDENT_NAME VARCHAR2 (20) Y'mm-hmm'

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

View basic information about two table constraints

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

View the constraints to establish those column structures

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

An error was reported when associating the id numbers of two tables (foreign key associated primary key)

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: the unique keyword or primary key of this column list does not match

Solution idea

1. Check whether the data type of binding primary key is different from that of foreign key (whether the data is the same).

2. Check whether there are other constraints on the primary key (foreign keys can only be bound to the primary key), whether there are other detection constraints, and so on.

3, carefully observe carefully, whether the state of the key value is turned on!

Summary of this topic

Because the primary key constraint of the first table is turned off (the constraint status is marked in red), the foreign key is connected normally and minor errors determine success or failure.

Resolution steps

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

Connection successful!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.