Baibs
Baibs

Reputation: 33

Table2 can't accept references from the table 1 - SQL Oracle

Can't figure this out. Why the table2 can't accept references from the table 1? Help please..

--table1--
CREATE TABLE table1(
t1_1 NUMBER(6) NOT NULL PRIMARY KEY,
t1_2 VARCHAR2(20) NOT NULL,
t1_3 NUMBER(5,2) NOT NULL);

--table2--
CREATE TABLE table2(
t2_1 NUMBER(6) NOT NULL PRIMARY KEY,
t1_1 REFERENCES table1(t1_1),
t1_3 REFERENCES table1(t1_3),
t2_datum DATE NOT NULL);

ORA-02270: no matching unique or primary key for this column-list

Upvotes: 0

Views: 136

Answers (1)

If your business logic approve then make column t1_3 of table1 unique. Your problem will be resolved.

CREATE TABLE table1(
t1_1 NUMBER(6) NOT NULL PRIMARY KEY,
t1_2 VARCHAR2(20) NOT NULL,
t1_3 NUMBER(5,2) NOT NULL unique);

--table2--
CREATE TABLE table2(
t2_1 NUMBER(6) NOT NULL PRIMARY KEY,
t1_1 REFERENCES table1(t1_1),
t1_3 REFERENCES table1(t1_3),
t2_datum DATE NOT NULL);

If t1_3 can't be made unique then you can create composite unique key constraint on t1_1 and t1_3 then create composite foreign key as below:

CREATE TABLE table1(
t1_1 NUMBER(6) NOT NULL PRIMARY KEY,
t1_2 VARCHAR2(20) NOT NULL,
t1_3 NUMBER(5,2) NOT NULL ,
constraint uk_1_3 unique (t1_1,t1_3));

--table2--
CREATE TABLE table2(
t2_1 NUMBER(6) NOT NULL PRIMARY KEY,
t1_1 REFERENCES table1(t1_1),
t1_3,
t2_datum DATE NOT NULL,
constraint fk_1_3 FOREIGN KEY(t1_1,t1_3) 
references table1(t1_1,t1_3));

Upvotes: 1

Related Questions