1

We can have more than one candidate key in a relation. But can we have two candidate keys in a relation different in length?

Suppose I have a relation R(A,B,C,D,E) and we have only two sets of attributes which uniquely identify a tuple in the relation: {A,B,C} and {D,E}.

So can we say both {A,B,C} and {D,E} are candidate keys?

philipxy
  • 14,867
  • 6
  • 39
  • 83
NARAYAN
  • 21
  • 1
  • 5
  • Might be a better Question for the DBA Stack Exchange. – Basil Bourque Aug 06 '17 at 16:04
  • What is a *definition* of CK? What does googling (many clear, concise, specific variations of) your question tell you? PS What was "Set1" in your original post? (Click on "edited" to see previous versions.) – philipxy Aug 08 '17 at 00:05

2 Answers2

6

But can we have two candidate keys in a relation different in length?

Yes.

A CK (candidate key) is a superkey (unique column set) that doesn't contain a smaller one.

So we sometimes say a CK is a "minimal" superkey, using "minimal" in this sense of a particular superkey not being able to be made into smaller superkey by removing attributes. It doesn't mean some other thing that might come to mind. In particular it doesn'tmean a CK that has fewer attributes than any other CK or no more attribtutes than another CK.

You need to learn the definitions of technical terms. Names don't tell you anything.

There's also no point in worrying about things you notice while following a definition. Just follow it.

Suppose I have a relation R(A,B,C,D,E) and we have only two sets of attributes which uniquely identify a tuple in the relation: {A,B,C} and {D,E}.

That is not possible. If {A,B,C} & {D,E} are unique, then all supersets of them are also unique. Eg the set of all attributes is always unique, here {A,B,C,D,E}.

Presumably what you mean is, we have only two minimal "sets of attributes which uniquely identify a tuple", ie only two CKs.

So can we say both ABC and DE are candidate keys?

You can if both those two unique sets don't contain smaller unique sets. By the definition of CK.

philipxy
  • 14,867
  • 6
  • 39
  • 83
-1

In order for a combination of rows to be a primary key candidate if needs to fulfill two requirements:

a.) None of the columns may contain NULL values.

b.) The combination must be unique.

fhossfel
  • 2,041
  • 16
  • 24
  • This doesn't answer the question. PS Relations don't have nulls, SQL tables have nulls. Relational terms do not always have the same meaning as reminiscent SQL terms. Assuming a table has no nulls & no duplicate rows, so we can apply the relational terms to SQL tables, an SQL PK is not necessarily a CK, it is in general only a superkey. You have described the criteria necessary to declare an SQL PK, but not a relational CK/PK. – philipxy Aug 10 '18 at 03:53
  • It does answer the question but maybe I should have said a little more: I interpret a candidate key as a combination of columns that might act as a PK or FK. If the column combination in the referenced table is a primary key candidate it can be used as a foreign key by defining a unique constraint on it (on most RDBMs) and is therefore a candidate key. – fhossfel Aug 11 '18 at 08:43
  • CK has a definition & you are not giving it. A CK is a superkey containing no smaller superkey. FKs are irrelevant. They are not part of the definition of CK. Anyway it doesn't matter what *you* want to call a CK, what matters is what the *question* calls a CK. (Anyway a relational FK references a relational CK, but for SQL tables that can be considered relations a SQL so-called FK references a *superkey*--SQL so-called PK or UNIQUE NOT NULL--not necessarily a CK. – philipxy Aug 11 '18 at 09:16
  • What I meant by not answering is, the question in the title & 1st paragraph, "Can a relation have Candidate Keys with different lengths?". I'd agee that giving a definition of "CK", as you are trying to do, is a start. (Although you give the definition of something else.) I would agree that applying the definition of CK to the question's example shows that the answer is "yes". But you don't address that. Or the other misconceptions in the question--see my answer. – philipxy Aug 11 '18 at 09:25