Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    el paso, tx

    Question Unanswered: -need help w/Primary key-

    Greeetings Everyone,

    This might be a fairly simple question for you all, but a bit confusing to me. I am taking a database management course and our assignment is to create a database with diff. tables, then populate & alter them. I created a table (EMPLOYEE) and everything was fine untill I inserted values. The problem that the instructor gave us states:

    "Underlined domains are key attributes."
    and gives us:

    for the table we should create. I took this to mean that, since DNAME was a primary key in another table, EMPNO was to be a primary key in the EMPLOYEE table, I think this makes sense. But EMPNO is not unique, and for the data that we insert the same EMPNO appears more than once such as:

    ------- --------
    ------- 20
    ------- 21
    ------- 32
    ------- 20
    ------- 20
    is there another way to declare this value since it is not unique, maybe another type of key. I was looking at Composite key as maybe a way, but I think that composite keys are only used to link two fields, such as DNAME and EMPNO, if I wanted them to be linked, and was not used for instances when you plan on listing redundant data such as EMPNO.


  2. #2
    Join Date
    Jan 2005


    Just a thought but maybe the instructor meant your primary key on EMPLOYEE table should consist of DNAME & EMPNO?
    It is then possible that you have more than one EMPNO that's the same but in in a different department(DNAME).
    Last edited by jacos_za; 02-03-05 at 04:10. Reason: typo

  3. #3
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    If the instructor really meant that your primary key should consist of DNAME and EMPNO, like Jacos said, and if DNAME shows "department name", I - personally - wouldn't want such a column as part of the primary key. "Department number" would make more sense to me.
    However, if that's your situation, create primary key as he wanted to:

    ALTER TABLE employee ADD CONSTRAINT pk_emp PRIMARY KEY (dname, empno);

    Should it be (dname, empno) or rather (empno, dname)?

    Furthermore, you said that DNAME is a primary key in another column. You could create a foreign key that'll reference employee.dname with department.dname, such as:

    ALTER TABLE employee ADD CONSTRAINT fk_emp_dept FOREIGN KEY (dname)
    REFERENCES department (DNAME);

    Check this page to read more about constraints (and view some examples).

Posting Permissions

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