How can I solve this problem?

Question

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 15-Nov-20 21:33pm

E7TRAF702

Updated 15-Nov-20 22:08pm

Add a Solution

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.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Tony Hill · Accepted Answer · 2020-11-15T21:40:00

Solution 1

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.

Posted 15-Nov-20 21:40pm

Tony Hill

Comments

E7TRAF702 16-Nov-20 4:17am

Thx

OriginalGriff · Accepted Answer · 2020-11-15T22:09:00

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.

How can I solve this problem?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us