Re: Can I define a VARRAY as a foreign key?

Sybrand Bakker wrote:
>
> On 14 Jul 2002 21:19:59 -0700, danesh583_at_yahoo.com (Gita Danesh)
> wrote:
>
> >I have a varray(10) of number and I want its elements to be foreign
> >key.I tried it in Sql plus but I got error.If anyone knows how to do
> >it ,I would be greatful to share it with me.
>
> As a VARRAY is not a single column, it is NOT possible. Think about
> it, how would it be?
>
> Hth
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address

Hi Gita,
I hope you don't mind, but I will try to rephrase your question, so as to make it more clear. Hopefully I have correctly understood it.

Let's say you define a database table thus:

create table TABLE_A (
  ID NUMBER PRIMARY KEY
)

Now you define the following database type:

create type FK_VA as varray(10) of NUMBER;

Now let's say you define a second table as follows:

create table TABLE_B (
  FKS FK_VA
)

Now if I understand you correctly, Gita, you want to define some database constraint on the FKS column in TABLE_B which would allow only existing ID values from TABLE_A to be stored in the (VARRAY) elements of FKS, correct?

If you have understood my interpretation (of your question), and if it is correct, then I seriously doubt whether you can define such a database constraint -- but I admit, I haven't investigated the possibility at all, I'm only going on what I already know.

You may be able to define a set of triggers on TABLE_B that would enforce such a constraint -- I don't know (and I haven't investigated that possibility either -- it's just a suggestion).

Hope this has helped you.

Good Luck,
Avi. Received on Mon Jul 15 2002 - 06:17:44 CDT