alter table mysql in postgresql

Question

alter table mysql in postgresql

Navigation

#1 by (0 votes)

1

I'm trying to make this modification from MySQL to postgresql:

ALTER TABLE documento_autor
ADD CONSTRAINT FOREIGN KEY(id_autor) REFERENCES autores (id_autor) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE documento_autor
ADD CONSTRAINT FOREIGN KEY(id_documento) REFERENCES documentos(id_documento) ON DELETE CASCADE ON UPDATE CASCADE;

It's in MySQL and I need to do that in postgresql.

mysql postgresql

asked by John Vanegas 07.09.2017 в 19:36

source

1 answer

Table html to Excel with [closed] format How can I implement three recaptcha on an index.php page?

score 0 · Accepted Answer

Restrictions that include ON DELETE CASCADE have special treatment:

Case 1: If the restriction does not exist

It should work as you do

ALTER TABLE documento_autor
ADD CONSTRAINT fk_documento_autor
FOREIGN KEY (id_autor)
REFERENCES autores(id_autor)
ON DELETE CASCADE  ON UPDATE CASCADE;


ALTER TABLE documento_autor
ADD CONSTRAINT fk_documento_autor_documento 
FOREIGN KEY(id_documento) 
REFERENCES documentos(id_documento) 
ON DELETE CASCADE ON UPDATE CASCADE;

Case 2: If the restriction exists, you must delete it before creating it

Suppose that the constraint of the documento_autor table is called fk_documento_autor

First you erase it:

ALTER TABLE documento_autor
DROP CONSTRAINT fk_documento_autor;

Then it is created again:

ALTER TABLE documento_autor
ADD CONSTRAINT fk_documento_autor
FOREIGN KEY (id_autor)
REFERENCES autores(id_autor)
ON DELETE CASCADE  ON UPDATE CASCADE;

Now we go with the other restriction:

If the restriction is called fk_documento_autor_documento

First you erase it:

ALTER TABLE documento_autor
DROP CONSTRAINT fk_documento_autor_documento;

Then it is created again:

ALTER TABLE documento_autor
ADD CONSTRAINT fk_documento_autor_documento 
FOREIGN KEY(id_documento) 
REFERENCES documentos(id_documento) 
ON DELETE CASCADE ON UPDATE CASCADE;

Note: If the table has data and does not comply with the restrictions you want to add, it will not be possible to create them in this way, you will have to disable the FKs, create the tables and then enable them, or more Well review the data, to not have inserted data that violates the restrictions.