March 18, 2009 at 2:07 am
My tables 6 tables.
CREATE Table lonekat(
lonekat_id intIDENTITY(1,1) NOT NULL,
beskrivning char(20)NOT NULL,
manadslon numeric(10,2)NOT NULL,
provisionsandel decimal(3,2)NOT NULL,
CONSTRAINT pk_lonekat_id primary key (lonekat_id));
-----------------------------------------------------------------
CREATE TABLE personal (
personal_id int IDENTITY(1,1) PRIMARY KEY,
chef_id int NULL REFERENCES personal(personal_id),
lonekat_id int NOT NULL REFERENCES lonekat(lonekat_id),
fornamn char(20) NOT NULL,
efternamn char(20) NOT NULL,
titel char(10) NOT NULL);
-----------------------------------------------------------------
CREATE Table utbetalning(
utbetalning_id int IDENTITY(1,1)NOT NULL,
personal_id int Constraint c_personal_id References personal(personal_id),
grundlon numeric(10,2) NOT NULL,
bonus numeric(10,2) NOT NULL,
totallon numeric(10,2)NOT NULL,
loneperiodchar(6) NOT NULL,
CONSTRAINT pk_utbetalning_id primary key (utbetalning_id));
-----------------------------------------------------------------
CREATE Table produkt(
produkt_id int IDENTITY(1,1)NOT NULL,
namn char(20) NOT NULL,
provision numeric(10,2) NOT NULL,
CONSTRAINT pk_produkt_id primary key (produkt_id));
-----------------------------------------------------------------
CREATE Table kund(
kund_id int IDENTITY(1,1) NOT NULL,
kundansvarig_id int Constraint c_kund_ansvar_id References personal(personal_id),
fornamn char(20) NOT NULL,
efternamn char(20) NOT NULL,
telefonnr char(15) NOT NULL,
mobiltelefonchar(15) NOT NULL,
ring_ej_flaggabit NOT NULL,
CONSTRAINT pk_kund_id primary key (kund_id));
-----------------------------------------------------------------
CREATE Table bokning(
bokning_id int IDENTITY(1,1)NOT NULL,
produkt_id int Constraint c_prod_id References produkt(produkt_id),
saljare int Constraint c_personal_id_seljar_id References personal(personal_id),
kund_id int NOT NULL Constraint c_kund_id References kund(kund_id),
tidpunkt date NOT NULL,
CONSTRAINT pk_bokning_id primary key (bokning_id));
I have insert lonekat whit rows:
INSERT INTO LONEKAT (beskrivning,manadslon,provisionsandel)
VALUES ('Ingångslön',10000,0.10)
But when i try to insert on the table: "PERSONAL"
Insert into personal (chef_id,lonekat_id,fornamn,efternamn,titel)
VALUES (34,1,'Gökhan','özkanat','Kung');
then i get error:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__personal__loneka__2C3393D0". The conflict occurred in database "Gangster", table "dbo.lonekat", column 'lonekat_id'.
The statement has been terminated.
Can someone please help to insert rows on personal?
/Gökhan
March 18, 2009 at 2:19 am
Hi
Lonekat_id inserted into personal table must be existing in lonekat table.
"Keep Trying"
March 18, 2009 at 3:10 am
This is what i inserted on lonekat
INSERT INTO LONEKAT (beskrivning,manadslon,provisionsandel)
VALUES ('Ingångslön',10000,0.10)
INSERT INTO LONEKAT (beskrivning,manadslon,provisionsandel)
VALUES ('Junior säljare',15000,0.40)
INSERT INTO LONEKAT (beskrivning,manadslon,provisionsandel)
VALUES ('Säljare',20000,0.60)
INSERT INTO LONEKAT (beskrivning,manadslon,provisionsandel)
VALUES ('Senior säljare',25000,0.80)
INSERT INTO LONEKAT (beskrivning,manadslon,provisionsandel)
VALUES ('Grupp ledare',30000,1)
INSERT INTO LONEKAT (beskrivning,manadslon,provisionsandel)
VALUES ('Chefslön',35000,0.00)
INSERT INTO LONEKAT (beskrivning,manadslon,provisionsandel)
VALUES ('VD lön',40000,0.00)
looks like this...
lonekat_idbeskrivning manadslonprovisionsandel
1 Ingångslön 100000.10
2 Junior säljare 150000.40
3 Säljare 200000.60
4 Senior säljare 250000.80
5 Grupp ledare 300001.00
6 Chefslön 400000.00
7 VD lön 1000000.00
March 18, 2009 at 3:44 am
Try this statement
Insert into personal (chef_id,lonekat_id,fornamn,efternamn,titel)
VALUES (1, 1,'Gökhan','özkanat','Kung')
it should work.
Because you have referred personal_id column value to chief_id, so what is the general rule of foreign key? Foreign key value should exist in the master table. Otherwise it will throw the error message.
What you have done?
You are trying to insert 34 in chief_id column which is referring personal_id column. Since you have declared it as IDENTITY, the value in the personal_id column is '1'( First execution).
so the same value should be in the chief_id column(becuase it is referring personal_id column).
Insert into personal (chef_id,lonekat_id,fornamn,efternamn,titel)
VALUES (3,1,'Gökhan','özkanat','Kung')
if you execute the below statement it will throw the same error message.
But, if you change it to 2, it will work.
I hope now you understand the problem. You have to maintain same value in personal_id as well as chief_id column or simply insert NULL into chief_id column if you no need any value on it.
karthik
March 18, 2009 at 3:44 am
Try this one
Insert into personal (chef_id,lonekat_id,fornamn,efternamn,titel)
VALUES (null,1,'Gökhan','özkanat','Kung')
it will work.
karthik
March 18, 2009 at 4:05 am
i dont understand why i insert null?
March 18, 2009 at 4:06 am
ah sorry its chef_id... thnx
March 18, 2009 at 4:08 am
still problem...
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__personal__loneka__2C3393D0". The conflict occurred in database "Gangster", table "dbo.lonekat", column 'lonekat_id'.
The statement has been terminated.
March 18, 2009 at 4:27 am
Can you post the insert statement that you executed?
karthik
March 18, 2009 at 4:37 am
i have only insert this on lonekat
INSERT INTO LONEKAT (beskrivning,manadslon,provisionsandel)
VALUES ('Ingångslön',10000,0.10)
INSERT INTO LONEKAT (beskrivning,manadslon,provisionsandel)
VALUES ('Junior säljare',15000,0.40)
INSERT INTO LONEKAT (beskrivning,manadslon,provisionsandel)
VALUES ('Säljare',20000,0.60)
INSERT INTO LONEKAT (beskrivning,manadslon,provisionsandel)
VALUES ('Senior säljare',25000,0.80)
INSERT INTO LONEKAT (beskrivning,manadslon,provisionsandel)
VALUES ('Grupp ledare',30000,1)
INSERT INTO LONEKAT (beskrivning,manadslon,provisionsandel)
VALUES ('Chefslön',35000,0.00)
INSERT INTO LONEKAT (beskrivning,manadslon,provisionsandel)
VALUES ('VD lön',40000,0.00)
nothing on personer
Insert into personal (chef_id,lonekat_id,fornamn,efternamn,titel)
VALUES (null,1,'Gökhan','özkanat','Kung')
this gives error:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__personal__loneka__2C3393D0". The conflict occurred in database "Gangster", table "dbo.lonekat", column 'lonekat_id'.
The statement has been terminated.
March 18, 2009 at 5:00 am
polo_sport9 (3/18/2009)
i have only insert this on lonekatINSERT INTO LONEKAT (beskrivning,manadslon,provisionsandel)
VALUES ('Ingångslön',10000,0.10)
INSERT INTO LONEKAT (beskrivning,manadslon,provisionsandel)
VALUES ('Junior säljare',15000,0.40)
INSERT INTO LONEKAT (beskrivning,manadslon,provisionsandel)
VALUES ('Säljare',20000,0.60)
INSERT INTO LONEKAT (beskrivning,manadslon,provisionsandel)
VALUES ('Senior säljare',25000,0.80)
INSERT INTO LONEKAT (beskrivning,manadslon,provisionsandel)
VALUES ('Grupp ledare',30000,1)
INSERT INTO LONEKAT (beskrivning,manadslon,provisionsandel)
VALUES ('Chefslön',35000,0.00)
INSERT INTO LONEKAT (beskrivning,manadslon,provisionsandel)
VALUES ('VD lön',40000,0.00)
nothing on personer
Insert into personal (chef_id,lonekat_id,fornamn,efternamn,titel)
VALUES (null,1,'Gökhan','özkanat','Kung')
this gives error:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__personal__loneka__2C3393D0". The conflict occurred in database "Gangster", table "dbo.lonekat", column 'lonekat_id'.
The statement has been terminated.
Hi,
I did the same thing, but I'm not getting any error. Am I missing something?
This is what I ran in my DB...
CREATE Table #lonekat(
lonekat_id int IDENTITY(1,1) NOT NULL,
beskrivning char(20) NOT NULL,
manadslon numeric(10,2) NOT NULL,
provisionsandel decimal(3,2) NOT NULL,
CONSTRAINT pk_lonekat_id primary key (lonekat_id));
CREATE TABLE #personal (
personal_id int IDENTITY(1,1) PRIMARY KEY,
chef_id int NULL REFERENCES #personal(personal_id),
lonekat_id int NOT NULL REFERENCES #lonekat(lonekat_id),
fornamn char(20) NOT NULL,
efternamn char(20) NOT NULL,
titel char(10) NOT NULL);
INSERT INTO #lonekat (beskrivning,manadslon,provisionsandel)
VALUES ('Ingångslön',10000,0.10)
INSERT INTO #lonekat (beskrivning,manadslon,provisionsandel)
VALUES ('Junior säljare',15000,0.40)
INSERT INTO #lonekat (beskrivning,manadslon,provisionsandel)
VALUES ('Säljare',20000,0.60)
INSERT INTO #lonekat (beskrivning,manadslon,provisionsandel)
VALUES ('Senior säljare',25000,0.80)
INSERT INTO #lonekat (beskrivning,manadslon,provisionsandel)
VALUES ('Grupp ledare',30000,1)
INSERT INTO #lonekat (beskrivning,manadslon,provisionsandel)
VALUES ('Chefslön',35000,0.00)
INSERT INTO #lonekat (beskrivning,manadslon,provisionsandel)
VALUES ('VD lön',40000,0.00)
Insert into #personal (chef_id,lonekat_id,fornamn,efternamn,titel)
VALUES (1,1,'Gökhan','özkanat','Kung')
select * from #lonekat
select * from #personal
drop table #lonekat
drop table #personal
Regards,
Ashok S
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply