0

I am struggling a little bit here.

Basically I have this relation:

  • Staff (Staff-Email, Staff-FName, Staff-LName, Staff-Qualification)

It is important to note that Staff-Qualification is a multi-valued attribute.

Therefore, does it have to go in a separate table, like this?

  • Staff_Qual (Staff-Email, Staff-Qualification)

Because it's multi-valued or can it stay in the Staff table.

However, the problem with splitting them up is that both tables would have the same primary key. I am really struggling on what to do. Can this happen? Do I need to add a constraint somewhere?

Any help would be really appreciated!

thatWiseGuy
  • 384
  • 2
  • 3
  • 18
KieranLowe
  • 81
  • 1
  • 10
  • That's a classic one-to-many relation. The Qualification table's key is a foreign key which relates to the main table. Read up on one-to-many relationships and foreign keys. – Duston Dec 28 '15 at 20:46
  • @Duston But isn't the Qualifications table's key also Primary, as it would be (Staff-Email, Staff-Qualification) so would it be both a primary and foreign? – KieranLowe Dec 28 '15 at 22:16

2 Answers2

1

They go into a separate table but the PRIMARY KEY on that table is not (Staff-EMail) it's (Staff-Email, Staff-Qualification). You'd have a separate FOREIGN KEY on (Staff-EMail) that REFERENCES the PRIMARY KEY in Staff.

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
  • I have changed that my primary key in the Staff_Qual table is both Staff-Email and Staff-Qualification). However, I don't really understand what you mean by: _"You'd have a separate FOREIGN KEY on (Staff-EMail) that REFERENCES the PRIMARY KEY in Staff."_ What I have done is: - CREATE TABLE Staff_Qualifications ( Staff-Email VARCHAR(25) NOT NULL, Staff-Qualification VARCHAR(20) CONSTRAINT TestPK PRIMARY KEY (Staff-Email, Staff-Qualification) CONSTRAINT TestFK FOREIGN KEY (Staff-Email, Staff-Qualification) REFERENCES Staff(Staff-Email) ); Would this be correct? – KieranLowe Dec 28 '15 at 22:36
  • Please edit your code into your question instead of putting it in a comment. – philipxy Dec 30 '15 at 07:12
1

What is a UNIQUE NOT NULL column set (possibly declared via PRIMARY KEY) in a table and what is a FOREIGN KEY column set in a table has nothing to do with what other ones are in that table or other tables except that a FOREIGN KEY should reference a UNIQUE NOT NULL.

From this recent answer:

Just declare per what is true of your relationships/tables:

  • a PK or UNIQUE NOT NULL declaration says that every subrow value in a column set is unique. Ie that the column set is a superkey. (A PK is just a distinguished UNIQUE NOT NULL.)
  • a FK declaration says that a column list subrow value in referencing columns must also be in referenced superkey columns.

PS Yes, you should drop the "multivalued" column from your table and add a new table using some UNIQUE NOT NULL column set (eg PRIMARY KEY) from the original plus a column for the multiple values that will be associated with each value of those columns. Declare the original's copied columns in the new table as FOREIGN KEY to the original. The PRIMARY KEY of the new one is those columns plus the new column.

PPS (As commented by Mike Sherrill 'Cat Recall') "MySQL does not enforce a requirement that the referenced columns be UNIQUE or be declared NOT NULL." Even though "You are advised to use foreign keys that reference only UNIQUE (including PRIMARY) and NOT NULL keys." ("Additionally, MySQL requires that the referenced columns be indexed for performance reasons.")

Community
  • 1
  • 1
philipxy
  • 14,867
  • 6
  • 39
  • 83
  • 1
    Your advice is right on target, but in MySQL, a foreign key constraint can reference nonunique and nullable columns. I wish I were making this up. "Additionally, MySQL requires that the referenced columns be indexed for performance reasons. However, the system does not enforce a requirement that the referenced columns be UNIQUE or be declared NOT NULL." [Source](https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html) – Mike Sherrill 'Cat Recall' Dec 31 '15 at 01:48
  • @MikeSherrill'CatRecall' I try hard to say nothing but the truth even when I am not saying the whole truth! I incorporated your comment & said a FK "should" be to UNIQUE NOT NULL. – philipxy Dec 31 '15 at 03:20
  • I know. I only pointed this out because the question is tagged "MySQL". MySQL is really vexing for people who take SQL standards seriously. – Mike Sherrill 'Cat Recall' Dec 31 '15 at 04:53