we have the following table to store texts in different languages:
CREATE TABLE TXT ( TXTID VARCHAR2(10 BYTE) NOT NULL , LANGUAGE VARCHAR2(2 BYTE) DEFAULT 'DE' NOT NULL , STEXT VARCHAR2(10 BYTE) , LTEXT VARCHAR2(4000 BYTE) , LABELTEXT VARCHAR2(20 BYTE) , CONSTRAINT TXT_PK PRIMARY KEY ( TXTID , LANGUAGE ) )
So you can have multiple language with one txtid. Now I want to have the TXTID as a foreign key on another table. First I created a view with
select distinct txtid from txtid
which of course doesn't work because you can't really use the constraints on the view. So I heard of the materialized view:
CREATE MATERIALIZED VIEW mv_txtid_txt REFRESH ON DEMAND AS SELECT distinct txtid from txt alter table mv_txtid_txt add CONSTRAINT TXT_PK PRIMARY KEY (TXTID) ENABLE
So far so fine, not perfect but I thought it would work... adding the foreign key to a table:
ALTER TABLE DATADICT ADD CONSTRAINT DATADICT_FK2 FOREIGN KEY(TXTID) REFERENCES mv_txtid_txt(TXTID) ENABLE
But I get the: ORA-02270: no matching unique or primary key for this column-list
How to get the not unique TXT.TXTID as a foreign key? Do I really must use triggers on the tables to check the foreign key manually?
REFRESH ON COMMITfor this.