Click here to Skip to main content
15,039,149 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
------------ table creation, constraints and structure changing -------------

<pre>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('30179','C400','12-SEP-08');
insert into ORDER1 ( NORD , NCLI , DATECOM) values('30182','C123','23-SEP-08');
insert into ORDER1 ( NORD , NCLI , DATECOM) values('30184','C400','23-JUN-08');
insert into ORDER1 ( NORD , NCLI , DATECOM) values('30185','B062','25-JUN-09');
insert into ORDER1 ( NORD , NCLI , DATECOM) values('30186','C400','02-JUN-09');
insert into ORDER1 ( NORD , NCLI , DATECOM) values('30188','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 ('30184','CS464',25);
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30179','CS262',60);
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30179','PA60',20);
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30182','PA60',30);
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30184','CS464',120);
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30184','PA45',27);
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30185','CS464',260);
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30185','PA60',15);
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30185','PS222',600);
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30186','PA45',3);
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30188','CS464',180);
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30188','PA45',22);
insert into DETAIL1( NORD , NPRO , QCOM ) values ('30188','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:

insert into DETAIL1( NORD , NPRO , QCOM ) values ('30184','CS464',120);

This line dont working plz help me to solve it
Posted
Updated 15-Nov-20 22:08pm
Comments
Richard Deeming 16-Nov-20 3:41am
   
"Don't working" is not enough information for anyone to help you.

Click the green "Improve question" link and update your question to include a proper description of the problem. Include the full details of any errors.

The problem is that you are trying to enter more than one record with the same primary key value of '30184', look at the other inserts, the duplicates easily be seen.
   
Comments
E7TRAF702 16-Nov-20 4:17am
   
Thx
To add to what Tony Hill has said ...

Primary keys are by definition unique: You are telling the system that this value identifies as row absolutely, if you try to execute a command using a specific value in this column, it will refer only to this row and no other. They cannot ever be duplicates.

Since the Detail1 table appears to exist to link the Product1 and Order1 tables together you should expect duplicates to exist in the foreign key columns - so they cannot be primary keys. You need to add an ID field (IDENTITY is probably what you want) to the Detail table to provide a unique Primary Key that identifies each linking row. That allows you to specifically SELECT, UPDATE, or DELETE linking rows

In addition, add your foreign key constraints BEFORE you add any data - that way the INSERT will fail immediately if the foreign key relationship cannot be established, rather than just getting a generic "constraint failure" when you create the relationship and having to hunt down which row(s) are giving a problem.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900