Oracle SQL errors ORA-02291 and ORA-00001
DrUiD
Posted messages
7
Registration date
Status
Member
-
slimane_gh -
slimane_gh -
Bonjour :)
I have a SQL activity to present for my BTS, which is about car rental... I have looked over my code but I don't understand what is wrong, I keep getting these 2 errors when filling the tables:
Here is my code:
I execute this first part as system:
Here I disconnect and use the boss account to create and fill the tables and create the roles:
The problem occurs when I do the inserts, here in bold... Here is the log after the inserts:
Here I hope to find the solution...
I have a SQL activity to present for my BTS, which is about car rental... I have looked over my code but I don't understand what is wrong, I keep getting these 2 errors when filling the tables:
ORA-02291: integrity constraint (BOSS.SYS_C002060) violation - parent key not found ERROR at line 1: ORA-00001: unique constraint (BOSS.SYS_C002036) violation
Here is my code:
I execute this first part as system:
create tablespace CAR datafile 'd:\BTS\CAR.ora' size 50 M; drop profile patron CASCADE; drop profile secretaire CASCADE; drop profile chauffeur CASCADE; create profile patron limit idle_time 2 failed_login_attempts 2 sessions_per_user unlimited; create profile secretaire limit idle_time 2 failed_login_attempts 2 password_lock_time 3 sessions_per_user 2 connect_time 60; create profile chauffeur limit idle_time 2 failed_login_attempts 2 password_lock_time 3 sessions_per_user 1 connect_time 60; drop user BOSS CASCADE; drop user SEC_1; drop user SEC_2; drop user BLANC; drop user NOIR; drop user VERT; drop user JAUNE; drop user BLEU; create user BOSS identified by chef default tablespace CAR quota 10M on CAR profile patron; create user SEC_1 identified by secret1 default tablespace CAR quota 10M on CAR profile secretaire; create user SEC_2 identified by secret2 default tablespace CAR quota 10M on CAR profile secretaire; create user BLANC identified by chauf1 default tablespace CAR quota 1M on CAR profile chauffeur; create user NOIR identified by chauf2 default tablespace CAR quota 1M on CAR profile chauffeur; create user VERT identified by chauf3 default tablespace CAR quota 1M on CAR profile chauffeur; create user JAUNE identified by chauf4 default tablespace CAR quota 1M on CAR profile chauffeur;
Here I disconnect and use the boss account to create and fill the tables and create the roles:
drop table course; drop table affectation; drop table driver; drop table voiture; drop table type; create table driver(nochauf number(5) primary key check (nochauf between 1 and 99999), nom varchar2(35) not null, adresse varchar2(35) not null, sal number(8) not null check (sal between 1 and 30000), comm number(8), embauche date not null); create table type(code_type varchar2(3) primary key, nbplace number(3) not null, design varchar2(50)); create table course(nocourse varchar2(6) primary key, vildep varchar2(30) not null, vilar varchar2(30) not null, dep_h number(2) not null check (dep_h between 0 and 23), dep_mn number(2) not null check (dep_mn between 0 and 59), ar_h number(2) not null check (ar_h between 0 and 23), ar_mn number(2) not null check (ar_mn between 0 and 59), j_ar number(1) check (j_ar in (0,1))); [g]insert into course values('AF1881','PARIS','CARCASSONNE',9,0,15,30,0); insert into course values('AF1882','CARCASSONNE','NANTES',17,30,22,30,0); insert into course values('AF1883','PARIS','CERGY',7,0,8,0,0); insert into course values('AF1884','PARIS','QUIMPER',7,0,12,30,0); insert into course values('AF1885','PARIS','LE MANS',7,0,10,0,0); insert into course values('AF1886','LE MANS','PARIS',16,0,19,0,0); insert into course values('AF1887','PARIS','ROUEN',7,0,8,30,0); insert into driver values(81333,'FEDOI','PARIS',24000,0,'01/03/1992'); insert into driver values(81337,'DUVAL','CERGY',18000,5580,'12/03/1992'); insert into driver values(81338,'MARTIN','ORLY',15000,1600,'03/05/1992'); insert into driver values(81353,'ANDRE','IVRY',22000,0,'01/06/1996'); insert into driver values(81433,'BERGER','PALAISEAU',14000,7000,'02/03/1992'); insert into driver values(81393,'COLLET','PARIS',7000,21000,'01/05/1993'); insert into driver values(81833,'PIC','MASSY',25000,0,'01/03/1992'); insert into type values('v6D',3,'Diesel confort'); insert into type values('v6E',3,'Essence confort'); insert into type values('v1D',3,'Diesel économique'); insert into type values('f1D',7,'Monospace'); create table voiture(nuvoit number(2) primary key check (nuvoit between 1 and 99), annserv number(4) not null check (annserv between 1980 and 2040), modvoit varchar2(50), kmcompt number(8), code_type varchar2(3), foreign key(code_type) references type); insert into voiture values(1,1988,'MERCEDES E220',223000,'v6d'); insert into voiture values(02,1990,'MERCEDES slk',13000,'v6e'); insert into voiture values(03,1995,'RENAULT LAGUNA',123000,'v1d'); insert into voiture values(04,1997,'RENAULT MEGANE',223000,'v6d'); insert into voiture values(05,1998,'VOLKSWAGEN CHARAL',323000,'f1d'); create table affectation(nocourse varchar2(6) not null, date_course date not null, nbpass number(3) not null, nuvoit number(2), nochauf number(5), foreign key(nuvoit) references voiture, foreign key(nochauf) references driver, primary key (nocourse,date_course)); insert into affectation values('AF1881','01/01/1999',1,01,81333); insert into affectation values('AF1881','01/01/1999',2,01,81333); insert into affectation values('AF1881','01/01/1999',3,02,81337); insert into affectation values('AF1881','01/01/1999',2,03,81353); insert into affectation values('AF1881','01/01/1999',1,01,81433);[/g] drop role chauffeur; drop role secretaire; create role secretaire; create role chauffeur; grant select, delete, update on boss.course to secretaire; grant select, delete, update on boss.affectation to secretaire; grant select, delete, update on boss.driver to secretaire; grant select, delete, update on boss.voiture to secretaire; grant select, delete, update on boss.type to secretaire; grant select on boss.course to chauffeur; grant select on boss.affectation to chauffeur; grant select on boss.driver to chauffeur; grant select on boss.voiture to chauffeur; grant select on boss.type to chauffeur; The problem occurs when I do the inserts, here in bold... Here is the log after the inserts:
insert into voiture values(1,1988,'MERCEDES E220',223000,'v6d') * ERROR at line 1: ORA-02291: integrity constraint (BOSS.SYS_C002182) violation - parent key not found insert into voiture values(02,1990,'MERCEDES slk',13000,'v6e') * ERROR at line 1: ORA-02291: integrity constraint (BOSS.SYS_C002182) violation - parent key not found insert into voiture values(03,1995,'RENAULT LAGUNA',123000,'v1d') * ERROR at line 1: ORA-02291: integrity constraint (BOSS.SYS_C002182) violation - parent key not found insert into voiture values(04,1997,'RENAULT MEGANE',223000,'v6d') * ERROR at line 1: ORA-02291: integrity constraint (BOSS.SYS_C002182) violation - parent key not found insert into voiture values(05,1998,'VOLKSWAGEN CHARAL',323000,'f1d') * ERROR at line 1: ORA-02291: integrity constraint (BOSS.SYS_C002182) violation - parent key not found Table created. insert into affectation values('AF1881','01/01/1999',1,01,81333) * ERROR at line 1: ORA-02291: integrity constraint (BOSS.SYS_C002187) violation - parent key not found insert into affectation values('AF1881','01/01/1999',2,01,81333) * ERROR at line 1: ORA-02291: integrity constraint (BOSS.SYS_C002187) violation - parent key not found insert into affectation values('AF1881','01/01/1999',3,02,81337) * ERROR at line 1: ORA-02291: integrity constraint (BOSS.SYS_C002187) violation - parent key not found insert into affectation values('AF1881','01/01/1999',2,03,81353) * ERROR at line 1: ORA-02291: integrity constraint (BOSS.SYS_C002187) violation - parent key not found insert into affectation values('AF1881','01/01/1999',1,01,81433) * ERROR at line 1: ORA-02291: integrity constraint (BOSS.SYS_C002187) violation - parent key not found Here I hope to find the solution...
13 answers
Hi,
It's normal that there's an error: when inserting values into the table, don't forget that Oracle databases are case-sensitive.
So, you're inserting data into the 'type' table. The first insertion contains, for example, 'v6D'.
When you insert fields into 'car', there is a foreign key. The problem is that you enter 'v6d' in 'car' while it is 'v6D' in 'type'. Therefore, there's logically a problem with a non-existent foreign key.
Let me know if this addresses your issue.
See you, Youssef.
It's normal that there's an error: when inserting values into the table, don't forget that Oracle databases are case-sensitive.
So, you're inserting data into the 'type' table. The first insertion contains, for example, 'v6D'.
When you insert fields into 'car', there is a foreign key. The problem is that you enter 'v6d' in 'car' while it is 'v6D' in 'type'. Therefore, there's logically a problem with a non-existent foreign key.
Let me know if this addresses your issue.
See you, Youssef.