Search code examples
sqlassociationsone-to-manyvarray

Reference to array of foreign keys

Let's say I have a table T - its every row has a key (assume TID column). Now I have a second table A where for each row I'd like to have a list of values from TID. How could I achive this?

I thought about creating VARRAY of values from T and putting it in A but I somehow feel it's not a right way to go...

Solution

  • If one row in T can belong to more than one row in A, the normal way is to create a link table:

    create table A_TO_T 
    (
    TID foreign key references T(TID), 
    AID foreign key references A(AID),
    primary key (TID, AID)
    );

    A link table is also called a junction or cross-reference table.

    If one row in T can only belong to one row in A, you can add a foreign key to the T table:

    alter table T add
    AID foreign key references A(AID);