Oracle - Foreign Key on non Unique - is materialized view the answer?

Question

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?

Kind regards,

Answer 1

If the concept identified by TXT_ID can be referenced by some other entity independently of the text language, then it deserves to be a separate entity represented by a separate table, as mentioned by RDFozz in a comment. Text translations will then become its children, as will DATADICT.