one to many unique "set" oracle db

Question

I am not sure how to create a one to many relationship, but restrict the many items as a "set" to each unique primary key.

DB: Oracle 11g

Example:

PK Table:

CUST(PK)
100
200

Valid FK Table Data:

CUST(FK) | ITEM
100 | 101
100 | 102
200 | 101
200 | 102

Invalid FK Table Data:

CUST(FK) | ITEM
100 | 101
100 | 101
200 | 104
200 | 104

Any suggestions how to setup such a relationship? I'd like to limit the uniqueness so it is not possible to add a value to the FK table that violates the above "set" uniqueness.

Can this be done purely on the Oracle DB end, or must I enforce this from the accessing Java code?

score 1 · Accepted Answer · answered Mar 06 '13 at 04:31

1

Just create a unique constraint with two columns: CUST and ITEM, similar to:

ALTER TABLE secondtable
ADD CONSTRAINT custItem UNIQUE (CUST, ITEM)

Create this constraint in addition to your Foreign key

answered Mar 06 '13 at 04:31

cha

10,301
1
18
26

That did it! Clearly I know nothing about databases :) – etech Mar 06 '13 at 23:01

score 1 · Answer 2 · answered Mar 06 '13 at 05:24

1

this might help you..create your tables following way....

create table cust_id 
           (cus_id number primary key) 
                  tablespace ts1;
  create table Valid_FK_Tabl
            (cus_id number,item number,constraints pk1 primary key(cus_id,item))
                  tablespace ts1 ;
  alter table Valid_FK_Tabl 
             add constraints fk1 foreign key(cus_id) 
                    references schema2.cust_id(cus_id);

answered Mar 06 '13 at 05:24

Thiyagu ATR

2,224
7
30
44

Thanks, this works for creating new tables, but since I already had them, I used cha's alter table method. – etech Mar 06 '13 at 23:07

one to many unique "set" oracle db

2 Answers2