create table CLEINT1 ( NCL1 char(10) not null primary key, NAME1 char (32) not null, ADRESS char(60) not null,LOCALITY char (30) not null, CAT char (2) not null, ACCOUNT1 decimal(9,2) not null);
insert into CLEINT1 ( NCL1,NAME1,ADRESS,LOCALITY,CAT,ACCOUNT1) values ('B112','HANSENNE','23,r . Dumont','poitiers','C1',1250);
insert into CLEINT1 ( NCL1,NAME1,ADRESS,LOCALITY,CAT,ACCOUNT1) values ('B062','GOFFIN','72,R. de la Gare','Namur','B2',-3200);
insert into CLEINT1 ( NCL1,NAME1,ADRESS,LOCALITY,CAT,ACCOUNT1) values ('B322','MONTI','112,r.NEUVE','GENEVE', 'B2',0);
insert into CLEINT1 ( NCL1,NAME1,ADRESS,LOCALITY,CAT,ACCOUNT1) values('B512','GILLET','14, r. de lete','Toulouse','B1',-8700);
insert into CLEINT1 ( NCL1,NAME1,ADRESS,LOCALITY,CAT,ACCOUNT1) values('C003','AVRIN','8,r.de la Cure','Toulouse','B1',-1700);
insert into CLEINT1 ( NCL1,NAME1,ADRESS,LOCALITY,CAT,ACCOUNT1) values('C123','MERCIER','25,r.Lemaitre','Namur','C1',-2300);
insert into CLEINT1 ( NCL1,NAME1,ADRESS,LOCALITY,CAT,ACCOUNT1) values('C400','FERARD','65,r.du Tertre','Poitiers','B2',350);
insert into CLEINT1 ( NCL1,NAME1,ADRESS,LOCALITY,CAT,ACCOUNT1) values('D063','MERCIER','201,BVD du Nord','Touluse','C2',-2250);
select * from CLEINT1;
select distinct LOCALITY from CLEINT1;
select NCL1,NAME1,LOCALITY from CLEINT1 where CAT='C1' and LOCALITY <>'Toulouse';
select NCL1,NAME1,ACCOUNT1 from CLEINT1 where LOCALITY in( 'poitiers','bruxelles') and ACCOUNT1 >0;
Create table PRODUCT1( NPRO char(15) not null primary key, LABEL char(60) not null,PRICE decimal(6) not null,QSTOCK decimal(8) not null);
insert into PRODUCT1(NPRO,LABEL,PRICE,QSTOCK) values ('CS262','CHEV.SAPIN.2000x6x2', 75, 45);
insert into PRODUCT1(NPRO,LABEL,PRICE,QSTOCK) values('CS264','CHEV.SAPIN.2000x6x2', 120, 2690);
insert into PRODUCT1(NPRO,LABEL,PRICE,QSTOCK) values('CS464','CHEV.SAPIN.2000x6x2', 220, 450);
insert into PRODUCT1(NPRO,LABEL,PRICE,QSTOCK) values('PA45','POINTE ACIER 45 (20K)', 105, 580);
insert into PRODUCT1(NPRO,LABEL,PRICE,QSTOCK) values('PA60','POINTE ACIER 60 (10K)', 95, 134);
insert into PRODUCT1(NPRO,LABEL,PRICE,QSTOCK) values('PH222','PL HETRE 200x20x2', 230, 782);
insert into PRODUCT1(NPRO,LABEL,PRICE,QSTOCK) values('PS222','PL SAPIN 200x20x2', 185, 1220);
select * from PRODUCT1;
select * from PRODUCT1 where LABEL LIKE '%ACIER%';
Create table ORDER1 ( NORD char(12) not null, NCLI char(10) not null,DATECOM date not null);
insert into ORDER1 ( NORD , NCLI , DATECOM) values('30160','C401','12-SEP-08');
insert into ORDER1 ( NORD , NCLI , DATECOM) values('30161','C123','23-SEP-08');
insert into ORDER1 ( NORD , NCLI , DATECOM) values('30163','C402','23-JUN-08');
insert into ORDER1 ( NORD , NCLI , DATECOM) values('30164','B062','25-JUN-09');
insert into ORDER1 ( NORD , NCLI , DATECOM) values('30165','C400','02-JUN-09');
insert into ORDER1 ( NORD , NCLI , DATECOM) values('30166','B512','21-JUN -09');
alter table ORDER1 add constraint NORD_C PRIMARY KEY (NORD);
select * from ORDER1;
Create table DETAIL1 ( NORD char(12) not null, NPRO char(15) not null, QCOM decimal (8) not null, PRIMARY KEY (NORD,NPRO));
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30170','CS462',25);
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30171','CS262',60);
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30172','PA62',20);
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30173','PA60',30);
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30174','CS464',120);
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30175','PA45',27);
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30181','CS467',260);
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30182','PA61',15);
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30183','PS222',600);
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30184','PA45',3);
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30185','CS466',180);
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30186','PA44',22);
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30187','PA60',33);
alter table DETAIL1 ADD constraint DETAIL_CLR1 foreign key (NPRO) REFERENCES PRODUCT1 (NPRO);
alter table DETAIL1 ADD constraint DETAIL_CLE2 foreign key (NORD) REFERENCES ORDER1 (NORD);
select * from DETAIL1
What I have tried:
alter table DETAIL1 ADD constraint DETAIL_CLR1 foreign key (NPRO) REFERENCES PRODUCT1 (NPRO);
alter table DETAIL1 ADD constraint DETAIL_CLE2 foreign key (NORD) REFERENCES ORDER1 (NORD);
This is dont working
Error :
ORA-02298: cannot validate (SQL_HYRRJGCEMJHXEUXLYDSWJXBSJ.DETAIL_CLR1) - parent keys not found
ORA-02298: cannot validate (SQL_HYRRJGCEMJHXEUXLYDSWJXBSJ.DETAIL_CLE2) - parent keys not found