Order matter when inserting the tables, If tables have foreign keys, the row values
of the foreign keys need to be intiliazed with NULL
For example in the first table created in the previous lesson, the last two columns
of the employee
table are forieng keys from the branch
table
INSERT INTO employee VALUES(100,'David','Wallace','1967-11-17','M',250000, NULL, NULL);
So insert NULL
values for the the primary key super_id
and branch_id
because those values haven't been filled yet
Create the first branch row values, the branch_id
can be updated
to employee
after being inserted, so foreign branch_id key is connected to employee
INSERT INTO employee VALUES(100,'David','Wallace','1967-11-17','M',250000, NULL, NULL); INSERT INTO branch VALUES(1,'Corporate',100,'2006-02-09'); UPDATE employee SET branch_id = 1 WHERE emp_id = 100; -- place the next employee who works for that branch in the employee table INSERT INTO employee VALUES(101,'Jan','Levinson','1961-05-11','F',110000,100,1); SELECT * FROM employee;
Insert the other employee who works for that branch
Do this for the other branches,
INSERT INTO employee VALUES(102,'Michael','Scott','1964-03-15','M',75000,100,NULL);
Remember to place NULL
inside branch_id
for employee value
if value is not set in the branch
table
INSERT INTO branch VALUES(2,'Scranton',102,'1992-04-06');
Then update the employee, and add other employees to the employee
table who work for that branch
UPDATE employee SET branch_id = 2 WHERE emp_id = 102; INSERT INTO employee VAlUES(103,'Angela','Martin','1971-06-25','F',63000,102,2); INSERT INTO employee VAlUES(104,'Kelly','Kapoor','1980-02-05','F',55000,102,2); INSERT INTO employee VAlUES(105,'Stanley','Hudson','1958-02-19','M',69000,102,2); SELECT * FROM employee;
We have to do it this way. Employess can't get the branch_id
or super_id
when no values have been inserted in client_table,
INSERT INTO employee VALUES(106,'Josh','Porter','1969-09-05','M',78000,100,NULL); INSERT INTO branch VALUES(3,'Stamford',106,'1998-02-13'); UPDATE employee SET branch_id = 3 WHERE emp_id = 106; INSERT INTO employee VALUES(107,'Andy','Bernard','1973-07-22','M',65000,106,3); INSERT INTO employee VALUES(108,'Jim','Halpert','1978-10-01','M',71000,106,3); SELECT * FROM employee;
INSERT INTO branch_supplier VALUES(2, 'Hammer Mill', 'Paper'); INSERT INTO branch_supplier VALUES(2, 'Uni-ball', 'Writing Utensils'); INSERT INTO branch_supplier VALUES(3, 'Patriot Paper', 'Paper'); INSERT INTO branch_supplier VALUES(2, 'J.T. Forms & Labels', 'Custom Forms'); INSERT INTO branch_supplier VALUES(3, 'Uni-ball', 'Writing Utensils'); INSERT INTO branch_supplier VALUES(3, 'Hammer Mill', 'Paper'); INSERT INTO branch_supplier VALUES(3, 'Stamford Lables', 'Custom Forms'); SELECT * FROM branch_supplier
INSERT INTO client VALUES(400, 'Dunmore Highschool', 2); INSERT INTO client VALUES(401, 'Lackawana Country', 2); INSERT INTO client VALUES(402, 'FedEx', 3); INSERT INTO client VALUES(403, 'John Daly Law, LLC', 3); INSERT INTO client VALUES(404, 'Scranton Whitepages', 2); INSERT INTO client VALUES(405, 'Times Newspaper', 3); INSERT INTO client VALUES(406, 'FedEx', 2); SELECT * FROM client;
INSERT INTO works_with VALUES(105, 400, 55000); INSERT INTO works_with VALUES(102, 401, 267000); INSERT INTO works_with VALUES(108, 402, 22500); INSERT INTO works_with VALUES(107, 403, 5000); INSERT INTO works_with VALUES(108, 403, 12000); INSERT INTO works_with VALUES(105, 404, 33000); INSERT INTO works_with VALUES(107, 405, 26000); INSERT INTO works_with VALUES(102, 406, 15000); INSERT INTO works_with VALUES(105, 406, 130000); SELECT * FROM works_with;