March 24, 2009 at 3:56 pm
I am trying to insert this values on my table (Personal)
1 34 1 Johan Forsberg Saljare
My tables
DROP TABLE bokning;
DROP TABLE kund;
DROP TABLE produkt;
DROP TABLE UTBETALNING;
DROP TABLE PERSONAL;
DROP TABLE LONEKAT;
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));
Some inserts 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',45000,0.00)
INSERT INTO LONEKAT (beskrivning,manadslon,provisionsandel)
VALUES ('VD lön',100000,0.00)
But i cant insert on personal whit this values...
1 34 1 Johan Forsberg Saljare
March 24, 2009 at 4:00 pm
Two things. What is the error message you are getting, and what is the actual INSERT statement look like, or did i just miss it in your original post?
March 24, 2009 at 4:04 pm
Hi
The script cannot work. First you drop the PERSONAL table (if not only part of this sample).
If the DROP was just for the example ensure that your foreign keys point to existing. So you need a person with person_id 1 as chief and a lonekat entry with lonekat_id 34.
If this is not the problem please post the error message you get.
Greets
Flo
March 24, 2009 at 4:06 pm
Insert into personal (chef_id,lonekat_id,fornamn,efternamn,titel)
VALUES (34,1,'Johan','Forsberg','Säljare');
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK__personal__chef_i__2645B050". The conflict occurred in database "Gangster", table "dbo.personal", column 'personal_id'.
The statement has been terminated.
If i make this instead it works..
Insert into personal (chef_id,lonekat_id,fornamn,efternamn,titel)
VALUES (NULL,1,'Johan','Forsberg','Säljare');
March 24, 2009 at 4:09 pm
Your personal table does not contain an entry with person_id "1".
Greets
Flo
March 24, 2009 at 4:14 pm
when i drop all my tables and recreat them and insert whit this...
Insert into personal (chef_id,lonekat_id,fornamn,efternamn,titel)
VALUES (NULL,1,'Johan','Forsberg','Säljare');
Then the personal_id on personal get the value of 3
what does this mean?
March 24, 2009 at 4:15 pm
polo_sport09 (3/24/2009)
Insert into personal (chef_id,lonekat_id,fornamn,efternamn,titel)VALUES (34,1,'Johan','Forsberg','Säljare');
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK__personal__chef_i__2645B050". The conflict occurred in database "Gangster", table "dbo.personal", column 'personal_id'.
The statement has been terminated.
If i make this instead it works..
Insert into personal (chef_id,lonekat_id,fornamn,efternamn,titel)
VALUES (NULL,1,'Johan','Forsberg','Säljare');
Here is personal:
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);
The column chef_id is referencing the personal_id column in this table, meaning that both values will need to be the same or chef-id null, since you allow null values.
March 24, 2009 at 4:17 pm
polo_sport09 (3/24/2009)
when i drop all my tables and recreat them and insert whit this...Insert into personal (chef_id,lonekat_id,fornamn,efternamn,titel)
VALUES (NULL,1,'Johan','Forsberg','Säljare');
Then the personal_id on personal get the value of 3
what does this mean?
It appears that there may have been two inserts done already.
March 24, 2009 at 4:23 pm
This is the values i got from my teacher to insert...
is it wrong values or is it something wrong whit me?
pers_idchef_idlönekategoriförnamnefternamnTitel
1341Johan Forsberg Säljare
2351Carl Johnson Säljare
3291Erik Persson Säljare
4301Jonas Svegerud Säljare
5311Petter Solberg Säljare
6321Esbjörn Adamnsson Säljare
7331Lorriane Barksdale Säljare
8342Johannes Agerberg Säljare
9352Christoffer Andersson Säljare
10292Peter Svensson Säljare
11302Tobias Petterson Säljare
12312Sofia Nilsson Säljare
13322Maria Ahlgren Säljare
14332Helena Stig Säljare
15342Erika Bonde Säljare
16352Gudrun Pauli Säljare
17292Ronda Brann Säljare
18302Hassan Loomis Säljare
19312Ceola Rockett Säljare
20322Willena Duty Säljare
21332Esteban Thurmond Säljare
22342Brittney Arneson Säljare
23353Anna Lund Säljare
24293Jens Backmyr Säljare
25303Olof Håkansson Säljare
26313Sharonda Thorson Säljare
27323Ralph Windsor Säljare
28333Sanford Bowlin Säljare
29364Lisa Wagers Säljare
30364Sonja Schreck Säljare
31374Ali Lecompte Säljare
32374Veronica Hein Säljare
33384Doria Urban Säljare
34384Georgina Bivens Säljare
35384Soo Mendoza Säljare
36395Petronella Tillström Säljare
37395Zula Lovejoy Säljare
38395Zoe Baum Säljare
39NULL6Donald Frump Chef
March 24, 2009 at 4:29 pm
The data of your teacher already contain a specific person_id so you cannot use an IDENTITY column.
* First you have to insert the person with chief_id = NULL (the president)
* Now insert the persons which have a chief_id = the president person_id
* Now insert the persons which have the previous inserted persons as chief
* ...
* Done your company has employees 😉
Greets
Flo
March 24, 2009 at 4:42 pm
Thnx/Tack
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply