2

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,

9
  • Can you add DATADICT table schema?
    – McNets
    Commented Mar 7, 2018 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
    Commented Mar 7, 2018 at 15:37
  • Is TXTID unique on DATADICT table? Even if it is not key on this table.
    – McNets
    Commented Mar 7, 2018 at 15:39
  • No, it is not unique. Multiple DATADICT Rows can have the same TXTID
    – S.Schmitt
    Commented Mar 7, 2018 at 15:40
  • 1
    You need REFRESH ON COMMIT for this. Commented Mar 7, 2018 at 15:53

1 Answer 1

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.

5
  • Isn't being in a view essentially a separate entity? Commented Mar 7, 2018 at 15:54
  • 1
    @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
    Commented Mar 7, 2018 at 15:57
  • Yeah i thought I can do it without adding a new table... but it seems impossbile..
    – S.Schmitt
    Commented Mar 7, 2018 at 16:15
  • With a materialized view you are adding another table as well, albeit one with maintenance overhead and various restrictions.
    – mustaccio
    Commented Mar 7, 2018 at 16:35
  • Yeah you are right with this. I will add a "Master TXTID" Table. Thank you very much!
    – S.Schmitt
    Commented Mar 8, 2018 at 6:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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