Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011
    Posts
    10

    Unanswered: Foreign Key question

    Hi all,

    I assume that this is a very straight forward question but I can't find a direct answer. I have an employees table in my database. One of the columns is 'emp_state' which is to show the State in which the employee resides. I also have a 'Customer' table and an 'office' table, which also both have a 'State' field. To reduce the amount of redundant data in my database, I have created a 'State' table, which will have all the states with a corresponding ID. I want to reference this table in the other tables as mentioned. When setting up my table, what should I define as the foreign key in the relevant tables? Should it be 'State_id' or 'State_name'? I want the state name to appear in any queries that are run, rather than the state ID which would be meaningless to a user.

    Thank you!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post CREATE TABLE statements for all tables involved.

    >Should it be 'State_id' or 'State_name'?
    I am unclear to what "it" (above) refers.
    STATE table should contain both columns above.
    What datatype is STATE_ID; NUMBER or VARACHAR2?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2011
    Posts
    10
    Code:
    CREATE TABLE employees (
    ...
    ...
    emp_state NUMBER(2),
    ...
    CONSTRAINT fk_state
      FOREIGN KEY (emp_state)
      REFERENCES states(state_id)
    );
    
    CREATE TABLE customers (
    ...
    ...
    cust_state NUMBER(2),
    ...
    CONSTRAINT fk_state
      FOREIGN KEY (cust_state)
      REFERENCES states(state_id)
    );
    
    CREATE TABLE office (
    ...
    office_state NUMBER(2),
    ...
    CONSTRAINT fk_state
      FOREIGN KEY (office_state)
      REFERENCES states(state_id)
    );
    
    CREATE TABLE states (
    state_id NUMBER(2), 
    state_name VARCHAR2(20),
    CONSTRAINT state_pk PRIMARY KEY (state_id)
    );
    I want 'state_name' to appear in the 'state' fields of the other tables, rather than the 'state_id'.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Select ee.employee_name, ss.state_name
    from employees ee, states ss
    where ee.emp_state = ss.state_id;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •