CODEWITHSUNDEEP
mysql
user2259555
user2259555

Reputation: 233

MySQL Foreign Keys error

I'm trying to create a self join table with varchars as the primary key and foreign keys. The table creates but I keep receiving errors when trying to insert data into the table

Table

 CREATE TABLE `staff` (
`staff_id` VARCHAR(255) NOT NULL,
`name` VARCHAR(255) NULL DEFAULT NULL,
`email` VARCHAR(255) NULL DEFAULT NULL,
`manager_id` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`staff_id`),
FOREIGN KEY (`manager_id`) REFERENCES `staff` (`staff_id`))

Data dump

   /*Data for the table `staff` */
  INSERT INTO staff (STAFF_ID, NAME, EMAIL, MANAGER_ID)
  VALUES ('lo1234', 'test','**.com', null);
 INSERT INTO staff (STAFF_ID, NAME, EMAIL, MANAGER_ID)
  VALUES ('er8909', 'test2','**.com', 'l01234');
 INSERT INTO staff (STAFF_ID, NAME, EMAIL, MANAGER_ID)
 VALUES ('ru6758', 'r**','**.com', 'l01234');
 INSERT INTO staff (STAFF_ID, NAME, EMAIL, MANAGER_ID)
 VALUES ('ad4567', 'admin','**.com', null);

Is there something wrong with the construction of the schema or the data being inserted?

Upvotes: 0

Views: 66

Answers (1)

BK004
BK004

Reputation: 392

You are wrong at this line make 1o1234 to 1012345, as

INSERT INTO staff (STAFF_ID, NAME, EMAIL, MANAGER_ID)
          VALUES ('lo1234', 'test','**.com', null);

change this to this:

INSERT INTO staff (STAFF_ID, NAME, EMAIL, MANAGER_ID)
  VALUES ('l01234', 'test','**.com', null);

Upvotes: 4

Related Questions