0

I created an OBJECT type and a VARRAY:

CREATE TYPE termek_adat IS OBJECT(
termek_id number(3), termek_db number(3));
/
CREATE type TERMEK_INF is VARRAY(10000) OF termek_adat;

I created a table:

CREATE TABLE Elad(
elad_id number(3),
termek_i TERMEK_INF constraint elad_ref_term_fk references termek(termek_id),
faktura_id number(3),
datum timestamp,
constraint e_pk_key primary key(elad_id)
);

there is a VARRAY termek_i. I want to use TERMEK_ADAT objects termek_id field as foreign key to this table:

CREATE TABLE Termek
  (termek_id NUMBER(3) CONSTRAINT term_term_id_pk PRIMARY KEY,
  megnevezes VARCHAR2(50),
  termek_kod NUMBER(15),
  termek_ar NUMBER(5),
  db_uzlet NUMBER(3),
  db_raktar NUMBER(3),
  szallito_id NUMBER(3) CONSTRAINT term_term_fk REFERENCES Szallitok(szallito_id) );

I dont know how i can realize that. Thanks for hep.

user1839790
  • 59
  • 5

1 Answers1

0

You cannot establish such a constraint.

The solution is to normalize your schema, and have a termek_adat table, which is the same as your current type, but with a foreign-key back to its parent Elad row:

CREATE TABLE termek_adat (
  elad_id number(3) references Elad(elad_id),
  termek_id number(3) references Termek(termek_id),
  termek_db number(3),
  primary key (elad_id, termek_id)
);

Basically in SQL and the relational world, you should reverse the normal parent-child relationships of the Object-Oriented world, where the parent has a list of its children, and instead in Relational the child references the parent. By trying to store the VARRAY (the children) in Elad (the parent), you are running into the referential integrity issue you are asking about.

Yes, you will have to join to get the adat's of a given Elad, but that's OK, and the SQL way. --DD

PS: Note the composite primary key on termek_adat, which references both the parent Elad, and the termek_id, since it probably doesn't make sense to have several times the same termek_id for a given Elad.

ddevienne
  • 1,802
  • 2
  • 17
  • 28