#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    2
    Rep Power
    0

    SQL error message help


    I am working on a project for my sql class and I am having a hard time figuring out what the issue is with the coding. Would you mind looking at it for me and see if anything wrong pops out at you. I believe that I am doing something wrong with the foreign key constraints. Here is the issue I can get the first problem to execute/complete successfully without errors (but I think that I did not properly structure the foreign key constraints). But when I move to question #2 I start receiving errors related to the foreign key constraints. I have tried these question over and over and I am really frustrated at this point, so any help is greatly appreciated.

    Here is the question on the final:

    1. Create the following 3 tables. Choose appropriate data types and create all specified
    primary and foreign keys.
    Table: DEPARTMENTS
    ----------------------------------------- --------
    DEPARTMENT_ID Primary Key
    DEPARTMENT_NAME NOT NULL
    MANAGER_ID FK refers to PK of Employees table.
    LOCATION_ID FK refers to PK of Locations table.
    Table: EMPLOYEES
    ----------------------------------------- --------
    EMPLOYEE_ID Primary Key
    FIRST_NAME
    LAST_NAME
    EMAIL
    SALARY
    PHONE_NUMBER
    HIRE_DATE
    MANAGER_ID FK refers to the PK of the Employees table.
    DEPARTMENT_ID FK refers to the PK of the Departments table.
    Table: LOCATIONS
    ----------------------------------------- --------
    LOCATION_ID Primary Key
    POSTAL_CODE
    CITY
    STATE_PROVINCE
    COUNTRY


    And here is the SQL code that I cam up with create the tables.

    CREATE TABLE locations
    (loc_locationid numeric(5)primary key,
    loc_postal nvarchar(9),
    loc_city nvarchar(20),
    loc_state varchar(2),
    loc_country nvarchar(20)
    );
    CREATE TABLE department
    (dep_departmentid numeric(5) primary key,
    dep_departmentname nvarchar (20) NOT NULL,
    dep_managerid numeric(5),
    dep_locationid numeric(5) foreign key (dep_locationid) references locations(loc_locationid)
    );
    CREATE TABLE employees
    (emp_employeeid numeric(5) primary key,
    emp_firstname nvarchar(20),
    emp_lastname nvarchar(20),
    emp_email nvarchar(20),
    emp_salary numeric(7,2),
    emp_phonenumber numeric(5),
    emp_hiredate datetime,
    emp_managerid numeric(5) foreign key (emp_managerid) references employees(emp_employeeid),
    emp_departmentid numeric(5)
    );

    QUESTION # 2
    Insert 3 departments, 7 employees, and 2 locations.
    Here is the coding I came up with.

    INSERT INTO Department (dep_departmentid, dep_departmentname, dep_managerid, dep_locationid)VALUES ('10001', 'Customer Service', '20246','15001');
    INSERT INTO Department (dep_departmentid, dep_departmentname, dep_managerid, dep_locationid)VALUES ('10002', 'Production','20012','15002')
    INSERT INTO Department (dep_departmentid, dep_departmentname, dep_managerid, dep_locationid)VALUES ('10003','Warehouse','25342','15001');
    INSERT INTO employees (emp_employeeid, emp_firstname, emp_lastname, emp_email, emp_salary, emp_phonenumber, emp_hiredate,emp_managerid, emp_departmentid)VALUES ('34567','Jon','Young','jon.young@partsrus.com','70000.00','567-357-8251','05-June-01','20246','10001');
    INSERT INTO employees (emp_employeeid, emp_firstname, emp_lastname, emp_email, emp_salary, emp_phonenumber, emp_hiredate,emp_managerid, emp_departmentid)VALUES ('28459','Tom','Jones','tom.jones@partsrus.com','35000.00','301-393-5420','23-Nov-05','20012','10002');
    INSERT INTO employees (emp_employeeid, emp_firstname, emp_lastname, emp_email, emp_salary, emp_phonenumber, emp_hiredate,emp_managerid, emp_departmentid)VALUES ('36781','Maria','Lopez','maria.lopez@partsrus.com','30000','567-357-8252','15-Dec-07','20246','10001');
    INSERT INTO employees (emp_employeeid, emp_firstname, emp_lastname, emp_email, emp_salary, emp_phonenumber, emp_hiredate,emp_managerid, emp_departmentid)VALUES ('36710','Paul','Snider','paul.snider@partsus.com','35500','325-682-1257','12-July-05','25342','10003');
    INSERT INTO employees (emp_employeeid, emp_firstname, emp_lastname, emp_email, emp_salary, emp_phonenumber, emp_hiredate,emp_managerid, emp_departmentid)VALUES ('39821','Cindy','Lucas','cindy.lucas@partsus.com','35000','325-682-1269','08-june-02','25342','10003');
    INSERT INTO employees (emp_employeeid, emp_firstname, emp_lastname, emp_email, emp_salary, emp_phonenumber, emp_hiredate,emp_managerid, emp_departmentid)VALUES ('29834','Jordan','Walker','jordan.walker@partsus.com','40000','301-393-1625','06-Feb-02','20012','10002');
    INSERT INTO employees (emp_employeeid, emp_firstname, emp_lastname, emp_email, emp_salary, emp_phonenumber, emp_hiredate,emp_managerid, emp_departmentid)VALUES ('32541','Richard','Parks','richard.parks@partsus.com','45000','301-393-2452','29-sept-04','20012','10002');
    INSERT INTO locations (loc_locationid,loc_postal,loc_city,loc_state,loc_country)VALUES ('15001','17225-5647','Greencastle','PA','USA');
    INSERT INTO locations (loc_locationid,loc_postal,loc_city,loc_state,loc_country)VALUES ('15002','21740-6542','Hagerstown','MD','USA');

    Here is the errors I receive:

    Msg 547, Level 16, State 0, Line 1
    The INSERT statement conflicted with the FOREIGN KEY constraint "FK__departmen__dep_l__0519C6AF". The conflict occurred in database "finals", table "dbo.locations", column 'loc_locationid'.
    The statement has been terminated.
    Msg 547, Level 16, State 0, Line 2
    The INSERT statement conflicted with the FOREIGN KEY constraint "FK__departmen__dep_l__0519C6AF". The conflict occurred in database "finals", table "dbo.locations", column 'loc_locationid'.
    The statement has been terminated.
    Msg 547, Level 16, State 0, Line 3
    The INSERT statement conflicted with the FOREIGN KEY constraint "FK__departmen__dep_l__0519C6AF". The conflict occurred in database "finals", table "dbo.locations", column 'loc_locationid'.
    The statement has been terminated.
    Msg 8152, Level 16, State 4, Line 4
    String or binary data would be truncated.
    The statement has been terminated.
    Msg 8152, Level 16, State 4, Line 5
    String or binary data would be truncated.
    The statement has been terminated.
    Msg 8152, Level 16, State 4, Line 6
    String or binary data would be truncated.
    The statement has been terminated.
    Msg 8152, Level 16, State 4, Line 7
    String or binary data would be truncated.
    The statement has been terminated.
    Msg 8152, Level 16, State 4, Line 8
    String or binary data would be truncated.
    The statement has been terminated.
    Msg 8152, Level 16, State 4, Line 9
    String or binary data would be truncated.
    The statement has been terminated.
    Msg 8152, Level 16, State 4, Line 10
    String or binary data would be truncated.
    The statement has been terminated.
    Msg 8152, Level 16, State 4, Line 11
    String or binary data would be truncated.
    The statement has been terminated.
    Msg 8152, Level 16, State 4, Line 12
    String or binary data would be truncated.
    The statement has been terminated.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,150
    Rep Power
    4274
    you posted in the mysql forum, but that's not mysql code nor mysql error messages

    what database are you running?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    2
    Rep Power
    0
    I am using microsoft server 2008 sql. Sorry if i posted this to the wrong place.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,150
    Rep Power
    4274
    Originally Posted by kittie89us
    I am using microsoft server 2008 sql. Sorry if i posted this to the wrong place.
    no prob, thread moved

    regarding your errors -- the key value being referenced must exist before you add a reference to it

    you added 3 departments, then went straight to adding an employee, specifying a location which didn't exist yet
    Last edited by r937; February 20th, 2012 at 09:17 AM.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo spyfu logo threadwatch logo seochat tools logo