Tables for doctor and patients

Question

Please check if my tables specs agree with business rules. Also, please explain the differences between CONSTRAINT, FOREIGN KEY and CONSTRAINT FOREIGN KEY.

I have four MySQL tables (USER, NAME, PATIENT and DOCTOR) with the following rules:

1. A patient must have a name
2. A patient can be a user
3. A doctor must have a name
4. A doctor must be a user
5. A patient can be a doctor and vice versa...
6. By 3 & 5, if a patient is a doctor that patient must be a user

---

CREATE TABLE USER (
userName VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL UNIQUE,
userId MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
userEmail VARCHAR(100) NOT NULL,
userPassword VARCHAR(64) NOT NULL,
PRIMARY KEY(userId))

---

CREATE TABLE PATIENT (
patientId MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
userId MEDIUMINT UNSIGNED,
patientBirthday CHAR(8), # yyyymmdd
patientBirthplace VARCHAR(50),
patientGender TINYINT(1) UNSIGNED, # 0:male 1:female
patientBloodType TINYINT(1) UNSIGNED,
PRIMARY KEY(patientId),
CONSTRAINT FOREIGN KEY(userId) REFERENCES USER(userId) 
ON DELETE CASCADE ON UPDATE CASCADE)

---

CREATE TABLE DOCTOR (
doctorId MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
userId MEDIUMINT NOT NULL UNSIGNED,
nameId MEDIUMINT NOT NULL UNSIGNED,
PRIMARY KEY(patientId),
CONSTRAINT FOREIGN KEY(userId) REFERENCES USER(userId) 
CONSTRAINT FOREIGN KEY(nameId) REFERENCES USER(nameId) 
ON DELETE CASCADE ON UPDATE CASCADE)

---

CREATE TABLE NAME (
nameId MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
userId MEDIUMINT UNSIGNED,
lastName VARCHAR(100) NOT NULL,
firstName VARCHAR(100) NOT NULL,
PRIMARY KEY(nameId),
CONSTRAINT FOREIGN KEY(userId) REFERENCES USER(userId) 
ON DELETE CASCADE ON UPDATE CASCADE)

Answer 1

The CONSTRAINT word used within a CREATE TABLE or ALTER TABLE tells MySQL engine that this value is not a separate column, but rather it is applied on a column within the table.

The FOREIGN KEY word tells MySQL that whatever is in that field must reference a valid PRIMARY KEY from the other table on INSERT or UPDATE record, otherwise an error is raised.

CONSTRAINT FOREIGN KEY is simply how you phrase it. For more information I suggest reading up about it in the manual.

Now for your criteria:

1. A patient must have a name: FALSE

   That one is not true, unless you added either of those two to your CREATE TABLE PATIENT

   • NOT NULL to the userID column and a CONSTRAINT FOREIGN KEY (userID) REFERENCES USER(userID) or, preferably in my opinion:
   • nameID MEDIUMINT NOT NULL, CONSTRAINT FOREIGN KEY(nameID) REFERENCES NAME(nameID),

2. A patient can be a user: TRUE

3. A doctor must have a name: TRUE

4. A doctor must be a user: TRUE

5. A patient can be a doctor and vice versa...: TRUE (via userID)

6. By 3 & 5, if a patient is a doctor that patient must be a user: TRUE