ORA-02291: integrity constraint () violated - parent key not found

Question

I am trying to create session for admin and user both tables are associated to login table with the same foreign key - but when I create the seesion for admin I'm getting error PL/SQL: ORA-02291: integrity constraint (login_fk_user) violated - parent key not found, in my other question in this link, I did not explain in the best way, but got some ideas on using (party-role-relationship model) and this code below his more related to the question link that has been resolve

create table admin and primary key

/* table admin*/
create table admin (id_admin number(10) not null, 
                    admin_name varchar(30) not null);
/* primary key */
alter table admin add constraint admin_pk primary key (id_admin);

create table users and primary key

/* table user*/
create table users (id_user number(10) not null, 
                    user_name varchar(30) not null);
/* primary key */
alter table users add constraint user_pk primary key (id_user);

create table login and primary key and foreign key

/* table login*/
create table login(id_login number(10) not null, 
                   id_admin_user_login number(10) not null, 
                   email_login varchar(20) not null, 
                   password_login varchar(10) not null);
/* primary key */
alter table login add constraint login_pk primary key (id_login);

/* foreign key reference to admin*/
alter table login add constraint login_fk_admin foreign key (id_admin_user_login) 
reference admin(id_admin);

 /* foreign key reference to user*/
alter table login add constraint login_fk_user foreign key (id_admin_user_login) 
reference users(id_user);

Procedure to create session for admin with table admin/login:

PROCEDURE create_admin_session( p_name   IN VARCHAR2,
                                p_email  IN VARCHAR2,
                                p_pass   IN VARCHAR2,
                                p_error  OUT NUMBER,
                                p_msg_error OUT VARCHAR2)
IS
BEGIN
p_error := 0;
INSERT ALL
INTO admin (id_admin, admin_name) VALUES(seq_admin.NEXTVAL, p_name)

INTO login(id_login, id_admin_user_login, email_login, pass_login)
VALUES(seq_login.NEXTVAL, seq_admin.CURRVAL, p_email, p_pass)

SELECT * FROM DUAL;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
p_error     := 1;
p_msg_error := 'Error!'||SQLERRM;
END create_admin_session;

Procedure to create session for user with table users/login:

PROCEDURE create_user_session( p_name   IN VARCHAR2,
                               p_email  IN VARCHAR2,
                               p_pass   IN VARCHAR2,
                               p_error  OUT NUMBER,
                               p_msg_error OUT VARCHAR2)
IS
BEGIN
p_error := 0;
INSERT ALL
INTO users (id_user, user_name) VALUES(seq_user.NEXTVAL, p_name)

INTO login(id_login, id_admin_user_login, email_login, pass_login)
VALUES(seq_login.NEXTVAL, seq_user.CURRVAL, p_email, p_pass)

SELECT * FROM DUAL;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
p_error     := 1;
p_msg_error := 'Error!'||SQLERRM;
END create_user_session;

when I create the admin session, I received a error ORA-02291: integrity constraint (login_fk_user) violated - parent key not found

What am I doing wrong?

Answer 1

In table login, I add two id not null one for user id_user_login number(10) null and second for admin id_admin_login number(10) null

/* table login*/
create table login(id_login number(10) not null, 
                   id_admin_login number(10) null, 
                   id_user_login number(10) null, 
                   email_login varchar(20) not null, 
                   password_login varchar(10) not null);
/* primary key */
alter table login add constraint login_pk primary key (id_login);

/* foreign key reference to admin*/
alter table login add constraint login_fk_admin foreign key (id_admin_login) 
reference admin(id_admin) ON DELETE CASCADE;

 /* foreign key reference to user*/
alter table login add constraint login_fk_user foreign key (id_user_login) 
reference users(id_user) ON DELETE CASCADE;

Procedure to create session for admin with table admin/login:

PROCEDURE create_admin_session( p_name   IN VARCHAR2,
                                p_email  IN VARCHAR2,
                                p_pass   IN VARCHAR2,
                                p_error  OUT NUMBER,
                                p_msg_error OUT VARCHAR2)
IS
BEGIN
p_error := 0;
INSERT ALL
INTO admin (id_admin, admin_name) VALUES(seq_admin.NEXTVAL, p_name)

INTO login(id_login, id_admin_login, id_user_login, email_login, pass_login)
VALUES(seq_login.NEXTVAL, seq_admin.CURRVAL, NULL, p_email, p_pass)

SELECT * FROM DUAL;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
p_error     := 1;
p_msg_error := 'Error!'||SQLERRM;
END create_admin_session;

Procedure to create session for user with table users/login:

PROCEDURE create_user_session( p_name   IN VARCHAR2,
                               p_email  IN VARCHAR2,
                               p_pass   IN VARCHAR2,
                               p_error  OUT NUMBER,
                               p_msg_error OUT VARCHAR2)
IS
BEGIN
p_error := 0;
INSERT ALL
INTO users (id_user, user_name) VALUES(seq_user.NEXTVAL, p_name)

INTO login(id_login, id_admin_login, id_user_login, email_login, pass_login)
VALUES(seq_login.NEXTVAL, NULL, seq_user.CURRVAL, p_email, p_pass)

SELECT * FROM DUAL;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
p_error     := 1;
p_msg_error := 'Error!'||SQLERRM;
END create_user_session;

with this resolution, everything works in perfection and if I need to drop user/loginis worked like I want