CODEWITHSUNDEEP
oracle-databaseoracle11gforeign-keysprimary-keycomposite-primary-key
user5505661
user5505661

Reputation: 69

Primary key with two columns in Oracle?

I have tables with emp1 and emp2

emp1:

emp_1 | emp_2
1     | 2
3     | 4
5     | 6

emp2:

emp
1
2
3
6

I tried to set primary key to table emp1 and foreign key to emp2.

My code:

For primary key:

alter table emp1 add primary key(emp_1,emp_2);

For foreign key:

alter table emp2
add foreign key (emp)
references a_t1(emp_1,emp_2);

Error:

Error report -
SQL Error: ORA-02256: number of referencing columns must match referenced       columns
02256. 00000 -  "number of referencing columns must match referenced columns"
*Cause:    The number of columns in the foreign-key referencing list is not
       equal to the number of columns in the referenced list.
*Action:   Make sure that the referencing columns match the referenced
       columns.

Kindly help me to solve this error and set the primary key.

Upvotes: 0

Views: 18118

Answers (1)

MT0
MT0

Reputation: 167981

The only way I can think to do it is a nasty hack involving a materialized view. It would be better to fix your data so that you don't have the primary key spread across two columns.

CREATE TABLE EMP1 (
  EMP_1 INT UNIQUE,
  EMP_2 INT UNIQUE,
  PRIMARY KEY ( EMP_1,EMP_2 )
);

CREATE MATERIALIZED VIEW LOG ON EMP1
   WITH SEQUENCE, ROWID(EMP_1, EMP_2)
   INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW EMP1_MV
   BUILD IMMEDIATE
   REFRESH FAST ON COMMIT
   AS SELECT EMP_1 AS EMP
      FROM   EMP1
      UNION ALL
      SELECT EMP_2
      FROM   EMP1;

ALTER TABLE EMP1_MV ADD CONSTRAINT EMP1_MV__PK PRIMARY KEY ( EMP );

CREATE TABLE EMP2 (
  EMP INT PRIMARY KEY REFERENCES EMP1_MV( EMP )
);

Upvotes: 1

Related Questions