0

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,

  • Can you add DATADICT table schema? – McNets Mar 7 '18 at 15:18
  • @McNets will add that tomorrow, for now in short: normal field with no key on field TXTID, also fixed the error, was copying it from the wrong place, sorry – S.Schmitt Mar 7 '18 at 15:37
  • Is TXTID unique on DATADICT table? Even if it is not key on this table. – McNets Mar 7 '18 at 15:39
  • No, it is not unique. Multiple DATADICT Rows can have the same TXTID – S.Schmitt Mar 7 '18 at 15:40
  • 1
    You need REFRESH ON COMMIT for this. – ypercubeᵀᴹ Mar 7 '18 at 15:53
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.

  • Isn't being in a view essentially a separate entity? – ypercubeᵀᴹ Mar 7 '18 at 15:54
  • @ypercubeᵀᴹ it is of course, but it enforces integrity in the wrong direction so to speak -- children (translations) now determine what parent should be. – mustaccio Mar 7 '18 at 15:57
  • Yeah i thought I can do it without adding a new table... but it seems impossbile.. – S.Schmitt Mar 7 '18 at 16:15
  • With a materialized view you are adding another table as well, albeit one with maintenance overhead and various restrictions. – mustaccio Mar 7 '18 at 16:35
  • Yeah you are right with this. I will add a "Master TXTID" Table. Thank you very much! – S.Schmitt Mar 8 '18 at 6:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.