0

Can a CK have a FK referencing to a CK in another table?

Department (Dept, Dept_name)
Employee(RegNo, FirstName, LastName, BirthDate, Dept_fk, Salary, City)

Dept_name is a CK of the Department table.

Can Dept_fk be part of a CK (Dept_fk, FirstName, LastName, Birthdate) in Employee when Dept_name is not set as primary key in Department table?

philipxy
  • 14,867
  • 6
  • 39
  • 83
  • The title question is different from the body question. (But see the many post versions & many comments on my answer.) – philipxy Aug 16 '22 at 05:04

1 Answers1

0

Two definitions of "CK (candidate key) of a given table" are:

  • A column set in the table which functionally determines every column and which contains no smaller such set.
  • A column set in the table whose subrow values are unique and which contains no smaller such set.

We cannot determine the CKs of a table without the information needed by the definition we are using. Eg all the FDs (functional dependencies), or a canonical cover of FDs or all the column sets whose subrow values are unique, etc. That information can always be expressed without involving another table.

We can pick one CK of a table to call "the" PK (primary key) of it. PKs are irrelevant in relational theory. (If you are using an ER method and it has rules about PKs vs CKs then you should reference & tag it.)

Dept cannot be part of candidate key (ex.: Dept, FirstName, LastName, Birthdate) in Employee table since Dept is not set as primary key in Department table

Dept is not in Employee so it can't be part of a CK of it. But if you added it, whether it was a CK of it is independent of other tables.

If you are asking about whether you can do something based on a distinction between Dept being a CK vs PK in Department: PKs are are always irrelevant.

If the "Dept"s are typos for "Dept#": The mere fact that Dept# is or is not a PK of the Department table has no bearing on the CKs of another table. Whether it is a PK vs CK is always irrelevant.

But can I still call Dept a candidate key just knowing that Dept is a candidate key in the Department table?

It's the CK of Department. So in English we can say that it's a CK. But to be a CK is to be a CK of a particular table.

Maybe you mean "can I still call Dept a CK" of Employee just knowing...". Only if you show that it is one. And whether it is one is independent of any other table.

(Maybe you should be calling some things FKs in this question?)

PS A column can appear in multiple tables without a FK between them. ("Reference" is only useful when you are talking about a FK.) Different columns can have FKs between them. There is a FK if and only if all subrow values in one set of columns appear in another ("referenced") one. An SQL FK only has to go to a superkey (superset of a CK) not a CK. A FK can be from any column set: a FK is only a (super)key in the referenced table. Learn the definitions of FD, superkey, CK, PK, FK (to CK or superkey).

philipxy
  • 14,867
  • 6
  • 39
  • 83
  • A typo mistake, I meant Dept all the time. So "The mere fact that Dept# is not a PK of the Department table has no bearing on the CKs of another table" is the answer I was looking for. –  Oct 17 '15 at 20:05
  • First thing, I suggest as a title : Can a CK have a FK linked to a CK in another table? To make it easier to understand, I also modify the attribut name "Dept" as Dept_Name. –  Oct 18 '15 at 01:18
  • Thanks. There are still some "Dept"s though. Are they supposed to be Dept#? or Dept_name? A mix? Also the correct term for "linked to" is "referencing". Why are FKs now in your title but they're not in you question? Please take the time to edit your question so that it is *clear* with *no typos* and *says what you mean* including *what you are assuming* and *what you want to know*. And when you give an example first say what it is an example *of*. Why are you not just addressing/answering all my questions? – philipxy Oct 18 '15 at 01:33
  • PS What does "a CK has a FK" mean? Do you mean, Can it *be* a FK? A CK is a set of columns. A FK is a list of columns referencing a list of columns that is a CK or superkey (depending on the definition). You are unfortunately constantly using everyday words that are unclear instead of referring to defined things and their defined parts and properties and the defined things they do. – philipxy Oct 18 '15 at 01:59
  • CK = Dept_fk, FirstName, LastName, Birthdate. FK=Dept_fk. I corrected the attribut name Dept# as Dept_fk according to Oracle convention name. My teacher is asking us to use # as a symbol(not part of the name) to identify a FK. –  Oct 18 '15 at 02:05
  • Thanks. But calling it _fk or knowing that it is a FK does not say what it is a FK to. Just call it Dept & say that it is a FK in Employee referencing Departement. (Like I just comented.) I still don't understand "since Dept_name...", what does Dept_name have to do with anything? PS Please don't remove parts of questions (like your last paragraph) that an answer has addressed. Please just clarify it. Please re-read my comments. – philipxy Oct 18 '15 at 02:18
  • Ok....this the best I can do. Thanks for trying to help. If you re-read the main question and the details the way they are now and you still dont understand my question, delete this post please. –  Oct 18 '15 at 02:50