Msg 547, The INSERT statement conflicted with the FOREIGN KEY constraint

Question

In SQL Server 2012 Management studio, I tried many time to create some tables and insert into the tables some values, but the problem here is in relationship with tables :

The INSERT statement conflicted with the FOREIGN KEY constraint

The Errors :

Msg 8152, Level 16, State 14, Line 116 String or binary data would be truncated.

Msg 547, Level 16, State 0, Line 122 The INSERT statement conflicted with the FOREIGN KEY constraint "eworkerFK". The conflict occurred in database "7", table "dbo.member_clup", column 'manager_id'.

The statement has been terminated.

Msg 547, Level 16, State 0, Line 128 The INSERT statement conflicted with the FOREIGN KEY constraint "SALEDFk". The conflict occurred in database "7", table "dbo.worker", column 'worker_number'.

Can anyone help me to insert values into the correct tables ?

CREATE TABLE address
(
code_place int,
PLACE_NAME VARCHAR (15),
OUT_israel varchar (15)
CONSTRAINT address1 PRIMARY KEY(code_place)

)

create table member_clup
(
manager_id int,
manager_name varchar(15),
manager_last varchar(12),
phone_num int,
type varchar(10),
CONSTRAINT manager1PK PRIMARY KEY(manager_id)
)


create table worker
(
worker_number int,
worker_name varchar(50),
worker_last varchar(49),
id varchar(9),
type varchar(50),
manager_id int
CONSTRAINT workerPK PRIMARY KEY(worker_number)
CONSTRAINT eworkerFK FOREIGN KEY(manager_id)
REFERENCES member_clup(manager_id)

)

  CREATE TABLE rooms(
     room_number int,
	 floor int,
	 room_type varchar(8)
	 CONSTRAINT roomsKEY PRIMARY KEY(room_number)

  )

 CREATE TABLE cars
(
cars_number int,
car_type varchar(15),
car_model int,
CONSTRAINT carsPK PRIMARY KEY(cars_number)
) 

CREATE TABLE tourest(
   Cust_number int ,
   Cust_name varchar(50),
   cust_lastname varchar(50),
   cust_phone varchar(10),
   code_place int,
   Room_id int,
   Saledby int
   CONSTRAINT tourest1 PRIMARY KEY(Cust_number)
   CONSTRAINT addressFK FOREIGN KEY(code_place)
   REFERENCES address(code_place),
    CONSTRAINT SALEDFk FOREIGN KEY(Saledby)
   REFERENCES worker(worker_number),
     CONSTRAINT roomsFK FOREIGN KEY(Room_id)
   REFERENCES rooms(room_number)
   )
   CREATE TABLE kesher
(
Cust_number int,
cars_number int,
CONSTRAINT custPK1 PRIMARY KEY(Cust_number,cars_number),
CONSTRAINT kesher1FK FOREIGN KEY(Cust_number)
   REFERENCES cars(cars_number),
     CONSTRAINT kesherFK FOREIGN KEY(Cust_number)
   REFERENCES tourest(Cust_number)
)

insert into cars VALUES
(100100,'mazda',2002),
(100205,'ford',2017),
(100206,'porch',1998),
(100207,'mazda',2017),
(100208,'opel',2002),
(100209,'mazda',2016),
(100210,'pijuot',2002),
(100211,'mazda',2015),
(100212,'mazda',2010),
(100213,'volvo',2002),
(100215,'ford',20012)

insert into rooms VALUES
(100,1,'single'),
(101,1,'double'),
(102,1,'single'),
(103,1,'double'),
(201,2,'signle'),
(202,2,'signle'),
(203,2,'signle'),
(204,2,'signle'),
(300,3,'double'),
(301,3,'double'),
(302,3,'double'),
(303,3,'double'),
(304,3,'signle')

insert into address VALUES
(500,'Akko','no'),
(501,'Haifa','no'),
(502,'Nahariya','no'),
(503,'Nataniya','no'),
(504,'carmieal','no'),
(505,'Nahef','no'),
(507,'Nitsrat','no'),
(510,'OUT','yes')

insert into member_clup VALUES
(5400,'shmolek','snaa','0525732572','General'),
(5696,'malloc','ali','0525552501','Rooms'),
(5991,'ramada','hassan','0532731212','Rooms & Tips'),
(5210,'meri','yako','0525022572','General Manager')

insert into worker 
VALUES(1234,'halaa','khaled',1234567,'none',5696),
(2234,'fares','adoon',6542897,'none',5696),
(6670,'halaa','khaled',1001234,'none',5991),
(2554,'halaa','khaled',5658741,'none',5210),
(9987,'halaa','khaled',1123456,'none',5400)
insert into tourest VALUES
(1510,'moshe','yke','0525732579',500,101,2234),
(1520,'ninar','lait','052655541',500,102,6670),
(1521,'hasan','ahmad','0532578741',501,101,2234),
(1522,'ameer','karm','0545222741',500,104,6670),
(1523,'aliel','sraa','0525771572',504,100,2234),
(1524,'hasa','veto','0525122579',505,303,6670),
(1525,'saed','snaa','05255632579',505,303,2234),
(1526,'yakov','mero','0528132579',502,202,6670),
(1527,'mece','loka','0525962579',502,302,9987),
(1528,'ana','yokaf','0525791179',502,302,9987),
(1529,'lelya','mandlina','0527832579',505,203,9987),
(1530,'mnal','khokha','0525758579',507,204,5991),
(1531,'moka','panana','0525805579',507,200,2234)


insert into kesher VALUES

(1510,100100),
(1520,100209),
(1521,100100),
(1522,100209),
(1523,100206),
(1524,100206),
(1525,100213),
(1526,100206),
(1527,100213),
(1528,100213),
(1529,100209)

Answer 1

This code is straightforward to debug, you surely can work it out. Run each statement on it's own and see its result. If it fails, split it into smaller statements and repeat. If you can't split it, it's already small enough to debug by eyesight, it won't take long to figure the issue.

Before debugging though, know this, restarting from scratch is sometimes helpful/necessary, so have restart script(s) ready. In this scenario only a bunch of DROP TABLEs are necessary, they'll remove the tables and their records.

Your CREATE TABLE statements are working fine, that's one thing passed. Even if you had problems with them though, run them one by one to debug, not all at once.

Your INSERTs are problematic, yeah. Run each INSERT block one by one, and for the block that fails, split it into individual INSERT statements, one per record, and now you'll be able to tell which row is the problem.

Even though you cite certain foreign key conflict issues, I'm getting a bunch of different foreign key issues. Double-check the code you provided. Also, the order of what statement is ran first matters (can I furnish the third floor of my house while I don't even have third and second floors? Can you cancel a booking that you didn't book yet?)

Lastly, the data seem to have truncation issues, whether the table column should be bigger or the records' values are incorrect is something only you can tell.