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.

 

person_car_dtl

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 
PER_DATA.person_mst(p_num,p_dt);

 

 

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 http://allzhere.in
M.Saeed Khurram 544 posts Joined 09/12
10 Jun 2013

Hi,
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!
 

Khurram

taruntrehan 43 posts Joined 10/12
26 Jun 2013

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

Regards,
Tarun Trehan
http://allzhere.in

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: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.