PostgreSQL to MySQL

Hello,

Bellow is PostgreSQL table.

-- Table: document
CREATE TABLE document (
    id int  NOT NULL,
    description varchar(1024)  NOT NULL,
    location varchar(1024)  NOT NULL,
    patient_id int  NOT NULL,
    CONSTRAINT document_pk PRIMARY KEY (id)
);

-- Reference: document_patient (table: document)
ALTER TABLE document ADD CONSTRAINT document_patient
    FOREIGN KEY (patient_id)
    REFERENCES patient (id)
    NOT DEFERRABLE
    INITIALLY IMMEDIATE
;

Code (markup):

How to convert this " NOT DEFERRABLE INITIALLY IMMEDIATE " to compatible with MySQL ?
Thanks.

 

That feature is not supported in mysql. You would need to look at mysql docs to see if that would pose a problem to your application. IME, I've never needed this functionality in MySQL, just use NOT NULL to require a value to be inserted in a constrained field, or: SET FOREIGN_KEY_CHECKS=0; before performing a bulk insert, if that's what this is being used for, and I believe it would provide a similar constraint, but again depending on how the application is designed, you may need to alter the application to get it to work properly. In the case of SET FOREIGN_KEY_CHECKS=0;, you may run into a situation where you have to manually clean up data if you have nulls or collisions during a bulk import when you try to turn foreign key checks back on.

 

This can be converted by DB developer. With DEFERRABLE INITIALLY IMMEDIATE, you can defer the constraints on demand when you need it.