All Forums Database
taruntrehan 43 posts Joined 10/12
08 Jun 2013
Creating a Soft RI

Hi All,


I have to implement a soft ri in one of my project.


Following are the tables in PER_DATA:


person_mst with columns

p_num bigint

p_dt date

p_status char(1)


composite key is defined on columns p_num and p_dt

person_mst was created by user xyz.



p_num bigint

p_dt date

p_car_num varchar(100)


person_car_dtl was created by user abc.

I need to define a soft ri in person_car_dtl table and trying to execute the following query for it:

alter table PER_DATA.person_car_dtl 
add constraint FK_pers_car_chk foreign key (p_num,p_dt) 
references with no check option 



I am getting the following error :


ALTER TABLE Failed. 5315:  The user does not have REFERENCES access to PER_DATA.person_mst.p_num.


Please suggest the resolution for this.

Regards, Tarun Trehan
M.Saeed Khurram 544 posts Joined 09/12
10 Jun 2013

To create references to existing tables, a REFERENCES privilege is needed.
use one of the following:
GRANT REFERENCES ON person_mst TO xyz;  /* On all columns */
GRANT REFERENCES (p_num,p_dt) ON person_mst TO xyz; /*On P_NUM,P_DT Only */
GRANT REFERENCES (ALL BUT p_num) ON person_mst  TO xyz;  /* On all but P_NUM */
Hope this will work for you!


taruntrehan 43 posts Joined 10/12
26 Jun 2013

Too late to respond though; but Thanks for the inputs...

Tarun Trehan

gopinadhs 18 posts Joined 10/11
08 Aug 2014

how to implement SOFT RI on application side(BTEQ)..

Raja_KT 1246 posts Joined 07/09
08 Aug 2014

I am not sure about your real question. But when you deal with DDL. In bteq, too you create table . 

Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.