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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).