RE: Unique value

What I have understood is that against each ID in table A there are several Sub-Names in table B.
Your data in table A is:
1, AAAA and in table B is

1*, BBBB
1*, CCCC
1*, DDDD
(* AAAA has an ID 1 which is there in table B)

You want that the any of above 3 record should not be possible to insert in table B

In that case you need to define your Primary Key on ID and Sub-Name

ALTER TABLE B ADD PRIMARY KEY (ID, SubName);

HTH! Aleem

 -----Original Message-----

Sent:	Monday, September 02, 2002 1:13 PM
To:	Multiple recipients of list ORACLE-L
Subject:	Re: Unique value

> sultan wrote:
>
> Hi friends,
>
>
> I have a tables A and B
>
> DESC A
> ------------
> ID NUMBER
> NAME VARHAR2(10)
>
> DESC B
> -----------
> ID NUMBER
> SUBNAME VARCHAR2(20);
>
>
>
> In the above table B.ID is the foreign key to A.ID.
>
> How can I create unique value for B.SUBNAME based on A.NAME.
>
> Means , A.NAME has value 'AAAA" and
> B.SUBNAME has values 'BBBB', 'CCCC';
> when I insert value again in B.SUBNAME as 'BBBB' it
> should not agree for the combination value of A.NAME('AAAA'),
> but 'BBBB' can agree when I enter value for A.NAME
> other than 'AAAA'
>
> Please give some ideas,
>
> Thanks in advance
>
> syed.

Unique index on (id, subname), that's what concatenated indices are for.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Abdul Aleem
  INET: dmit_at_beaconhouse.edu.pk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).