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:
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.
ANY HELP WOULD BE GREATLY APPRECIATED.
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.
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).