Creating a Soft RI

Database
Enthusiast

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.

Tags (3)
4 REPLIES
Enthusiast

Re: Creating a Soft RI

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
Enthusiast

Re: Creating a Soft RI

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

Enthusiast

Re: Creating a Soft RI

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

Enthusiast

Re: Creating a Soft RI

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