3
\$\begingroup\$

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)
\$\endgroup\$
2
\$\begingroup\$

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

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.