K
K
Konstantin Malyarov2017-01-27 16:11:17
MySQL
Konstantin Malyarov, 2017-01-27 16:11:17

How to create a foreign key correctly?

There are two tables: patients (has a full name and DR) and a patient card (date of admission, type of payment, patient).
The patient card table takes values ​​from the patient table.

CREATE TABLE `card_patient` ( 
  `id_card_patient` int(11) NOT NULL AUTO_INCREMENT, 
  `number_ward` varchar(100) NOT NULL, 
  `card_outpatient` varchar(100) NOT NULL, 
  `pay` varchar(100) NOT NULL, 
  `card_inpatient` varchar(100) NOT NULL, 
  `patient` int(11) NOT NULL, 
  `gender` char(1) NOT NULL, 
  `enter_day` date NOT NULL, 
  `diagnosis` text NOT NULL, 
  `mkb` varchar(100) NOT NULL, 
  `create_string` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  `edite_string` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 
  PRIMARY KEY (`id_card_patient`), 
  KEY `card_patient_patient_FK` (`patient`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

CREATE TABLE `patient` ( 
  `id_patient` int(11) NOT NULL AUTO_INCREMENT, 
  `surname` varchar(100) NOT NULL, 
  `name` varchar(100) NOT NULL, 
  `midname` varchar(100) NOT NULL, 
  `birthday` date NOT NULL, 
  `create_string` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  `edite_string` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 
  PRIMARY KEY (`id_patient`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

How to make the patient field from the patient card table refer to the patient id field from the patient table?
I read on the Internet, I could not figure it out.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
R
Rsa97, 2017-01-27
@Konstantin18ko

Add to `card_patient` table:

FOREIGN KEY (`patient`)
  REFERENCES `patient` (`id_patient`)
  ON DELETE CASCADE
  ON UPDATE CASCADE

Just keep in mind that no automatic table linking will appear during SELECT, MySQL will simply not allow you to add a value to the `card_patient`.`patient` field that is missing in the `patient`.`id_patient` field.
Well, depending on the ON DELETE and ON UPDATE settings, you can automatically delete and change `card_patient`.`patient` when deleting/changing `patient`.`id_patient` or blocking such deletion/change with an existing connection.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question