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

    Join Date
    Dec 2011
    Posts
    2
    Rep Power
    0

    Please look at this. and help me out


    1. Create the following 3 tables. Choose appropriate data types and create all specified
    primary and foreign keys.
    Table: DEPARTMENTS
    Name
    ----------------------------------------- --------
    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
    Name
    ----------------------------------------- --------
    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
    Name
    ----------------------------------------- --------
    LOCATION_ID Primary Key
    POSTAL_CODE
    CITY
    STATE_PROVINCE
    COUNTRY
    ************************

    I have made the tables but they are not working for me. Please look at the code as when i try to enter data into these tables I get an error message Integrity Constraint Voilated

    ************************
    CREATE TABLE KAPLAN_DEPARTMENT
    (

    DEPARTMENT_ID NUMBER NOT NULL,

    DEPARTMENT_NAME VARCHAR2(20) NOT NULL,

    MANAGER_ID NUMBER,

    LOCATION_ID NUMBER
    ,
    CONSTRAINT KAPLAN_DEPARTMENT_PK PRIMARY KEY
    (
    DEPARTMENT_ID
    )
    ENABLE,

    CONSTRAINT DEPT_EMP_FK FOREIGN KEY(MANAGER_ID) REFERENCES KAPLAN_EMP(EMPLOYEE_ID) ENABLE,
    CONSTRAINT DEPT_LOC_FK FOREIGN KEY(LOCATION_ID) REFERENCES KAPLAN_LOCATIONS(LOCATION_ID) ENABLE

    )
    ;
    ***********************
    CREATE TABLE KAPLAN_EMP
    (

    EMPLOYEE_ID NUMBER NOT NULL,

    FIRST_NAME VARCHAR2(20),

    LAST_NAME VARCHAR2(20),

    EMAIL VARCHAR2(20),

    SALARY NUMBER,

    HIRE_DATE DATE,

    MANAGER_ID NUMBER,

    DEPARTMENT_ID NUMBER
    ,
    CONSTRAINT KAPLAN_DEPARTMENT_PK PRIMARY KEY
    (
    EMPLOYEE_ID
    )
    ENABLE,
    CONSTRAINT EMP_FK FOREIGN KEY(MANAGER_ID) REFERENCES KAPLAN_EMP(EMPLOYEE_ID) ENABLE,
    CONSTAINT EMP_DEPT_FK FOREIGN KEY(DEPARTMENT_ID) REFERENCES KAPLAN_DEPT(DEPARTMENT_ID) ENABLE

    )
    ;
    *******************************
    CREATE TABLE KAPLAN_LOCATIONS
    (

    LOCATION_ID NUMBER NOT NULL,

    POSTAL_CODE NUMBER,

    CITY VARCHAR2(20),

    STATE_PROVINCE VARCHAR2(20),

    COUNTRY VARCHAR2(20)
    ,
    CONSTRAINT KAPLAN_LOCATIONS_PK PRIMARY KEY
    (
    LOCATION_ID
    )

    ENABLE
    )
    ;
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    your tables look okay

    your error message occurs when you insert data, right?

    this means the data you are entering does not satisfy the constraints that you have defined to protect the data integrity of your tables

    look at the data in your tables, and then look at the data you're trying to insert, and you will find the cause of the error

    by the way, you're not using mysql, are you

    what database system is this? oracle?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    2
    Rep Power
    0
    Thanks Dear for the reply Yes I am using oracle. And i am entering the data as per the data types. Please check them again if its possible for you see the foreign keys if they have any problem. I also get error while I am entering data in the Location table and it says the the parent table not found for constraint. I dont know how to fix this. Please assist me or create them at your end to check whats the issue.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    sorry, i prefer not to even attempt working with oracle

    i'll move the thread to the oracle forum and wish you good luck

    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    834
    Rep Power
    387

    Wink


    As r937 points out, you may need to enter the correct data type and also take care of the constraints.

    For example, this means the "manager" needs to exist before you enter an employee:
    Code:
    CONSTRAINT EMP_FK FOREIGN KEY(MANAGER_ID) REFERENCES KAPLAN_EMP(EMPLOYEE_ID) ENABLE,
    And this one means the department needs to exist before you assign the department number to an employee:
    Code:
    CONSTAINT EMP_DEPT_FK FOREIGN KEY(DEPARTMENT_ID) REFERENCES KAPLAN_DEPT(DEPARTMENT_ID) ENABLE
    Good luck!

IMN logo majestic logo threadwatch logo seochat tools logo