CODEWITHSUNDEEP
mysqldatabase
jordpw
jordpw

Reputation: 181

MySQL -- Adding candidate keys

I have a database with multiple tables, and for two of the tables, I have a candidate key along with the primary key. I know there's no syntax for candidate keys, so how would I represent this?

Here's the code. I would like SSN to be a candidate key for Patient, as well as for Employee. What should I do? Thanks.

create table Employee(
ID int(8),
Fname varchar(20),
Lname varchar(20),
SSN char(11),
Specialty varchar(15),
dateofHire date,
primary key(ID)

);

create table Patient(
num int(8),
Fname varchar(20),
Lname varchar(20),
SSN char(11),
dateofBirth date,   
primary key(num)

);

Upvotes: 0

Views: 2325

Answers (1)

user2864740
user2864740

Reputation: 61975

Candidate keys are not directly manifested in SQL-relational databases as a separate type - they are "left" in the RA conceptual zone.

However, a Unique Index / Constraint (over one or more columns) can be used to enforce a Candidate key cardinality (and provide an efficient look-up over such); where a Primary key can be viewed as a specialization of such.

(It must be a unique index to establish the cardinality; a non-unique index doesn't do.)

That being said .. SSN's can be duplicated (or even not assigned) and thus are not a great choice for a universal candidate key. For a person-based system - and especially for patients - I would use a surrogate key only (unless there is a guaranteed-stable like an Employee ID). Let the front-end find and resolve conflicts and provide a method of dealing with such duplicates cases (names, SSNs, etc) as they arise.

Also, depending upon the use-case, don't forget to allow for dealing with patients who's identity is not [yet] known ..

Upvotes: 2

Related Questions