Foreign Key

Hi

I create a foreign key. But I cannot save it. I seems to save but it does not.

?????

Strange. Anyone met this situation before.

Thanks Fern

Or is it because the foreign key was already created in the initial script?

ALTER TABLE ADRESSE ADD CONSTRAINT FK_ADRESSE_VILLE FOREIGN KEY (NO_VILLE) REFERENCES VILLE (NO_VILLE);

I'm not sure. But I cannot reproduce that in the latest build. You should probably just update HeidiSQL.

Hi I installed the 10.3 version and it does the same.

???

May I ask you to update once again, now that I just released v11.0?

Done but still does not workd

This is the extracted contrat definition. Lower you have the original script that served to create this table. Missing, the foreign key definition.

-- Listage de la structure de la table processeur_bois. contrat
CREATE TABLE IF NOT EXISTS `contrat` (
  `NO_CONTRAT` int(11) NOT NULL,
  `DATE_CREATION_CONTRAT` date DEFAULT NULL,
  `DATE_MODIFICATION_CONTRAT` date DEFAULT NULL,
  `DATE_DEBUT_CONTRAT` date DEFAULT NULL,
  `EXPLOITATION` char(3) DEFAULT NULL,
  `EXPLORATION` char(3) DEFAULT NULL,
  `NO_RESPONSABLE_CLIENT` int(11) NOT NULL,
  `NO_SOUMISSION` int(11) NOT NULL,
  `ID_TYPE_CONTRAT` int(11) NOT NULL,
  PRIMARY KEY (`NO_CONTRAT`),
  KEY `FK_CONTRAT_RESPONSABLE_CLIENT` (`NO_RESPONSABLE_CLIENT`),
  KEY `FK_CONTRAT_SOUMISSION` (`NO_SOUMISSION`),
  KEY `FK_CONTRAT_TYPE_CONTRAT` (`ID_TYPE_CONTRAT`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2;

CREATE TABLE CONTRAT
(
    NO_CONTRAT      INT NOT NULL,
    DATE_CREATION_CONTRAT DATE,
    DATE_MODIFICATION_CONTRAT DATE,
    DATE_DEBUT_CONTRAT DATE,
    EXPLOITATION    CHAR(3),
    EXPLORATION     CHAR(3),
    NO_RESPONSABLE_CLIENT INT NOT NULL,
    NO_SOUMISSION   INT NOT NULL,
    ID_TYPE_CONTRAT INT NOT NULL,
    PRIMARY KEY (NO_CONTRAT)
);

This is the part missing in Heidi

/***********************************************************
    Les clés étrangères de la table "CONTRAT"
***********************************************************/

ALTER TABLE CONTRAT
        ADD CONSTRAINT FK_CONTRAT_RESPONSABLE_CLIENT FOREIGN KEY (NO_RESPONSABLE_CLIENT)
        REFERENCES RESPONSABLE_CLIENT (NO_RESPONSABLE_CLIENT),
        ADD CONSTRAINT FK_CONTRAT_SOUMISSION FOREIGN KEY (NO_SOUMISSION)
        REFERENCES SOUMISSION (NO_SOUMISSION),
        ADD CONSTRAINT FK_CONTRAT_TYPE_CONTRAT FOREIGN KEY (ID_TYPE_CONTRAT)
        REFERENCES TYPE_CONTRAT (ID_TYPE_CONTRAT);

I don't have the referenced tables here: RESPONSABLE_CLIENT, RESPONSABLE_CLIENT and TYPE_CONTRAT. Maybe you could post their CREATE code also.

/***********************************************************
    Table "RESPONSABLE_CLIENT"
***********************************************************/

CREATE TABLE RESPONSABLE_CLIENT
(
    NO_RESPONSABLE_CLIENT INT NOT NULL,
    PRENOM_RESPONSABLE_CLIENT VARCHAR(65),
    NOM_RESPONSABLE_CLIENT VARCHAR(75),
    ID_TYPE_RESPONSABLE_CLIENT INT NOT NULL,
    NO_CLIENT       INT NOT NULL,
    PRIMARY KEY (NO_RESPONSABLE_CLIENT)
);


    Table "TYPE_CONTRAT"
***********************************************************/

CREATE TABLE TYPE_CONTRAT
(
    ID_TYPE_CONTRAT INT NOT NULL,
    TYPE_CONTRAT    VARCHAR(15),
    DESCRIPTION_TYPE_CONTRAT VARCHAR(100),
    PRIMARY KEY (ID_TYPE_CONTRAT)
);

/***************

Ok, in the end I can see all foreign keys were created. I had to create the table soumission as you did not post that one. However, after firing the ALTER TABLE queries, all foreign keys are there:

I suppose these were not generated on your side.

I also guess you got some error message when trying to add foreign keys, right?

This is how it ought to appear. If I create the foreign keys in Heidi, they are not saved when I save the table. But I don't get any error message.

But you are not firing these ALTER .. queries in a query tab with suppressed error messages, did you?

Be sure the yellow warning sign is pressed if you want to see errors:

/***********************************************************
    Les clés étrangères de la table "CONTRAT"
***********************************************************/

ALTER TABLE CONTRAT
        ADD CONSTRAINT FK_CONTRAT_RESPONSABLE_CLIENT FOREIGN KEY (NO_RESPONSABLE_CLIENT)
        REFERENCES RESPONSABLE_CLIENT (NO_RESPONSABLE_CLIENT),
        ADD CONSTRAINT FK_CONTRAT_SOUMISSION FOREIGN KEY (NO_SOUMISSION)
        REFERENCES SOUMISSION (NO_SOUMISSION),
        ADD CONSTRAINT FK_CONTRAT_TYPE_CONTRAT FOREIGN KEY (ID_TYPE_CONTRAT)
        REFERENCES TYPE_CONTRAT (ID_TYPE_CONTRAT);

ALTER TABLE CONTRAT
        ADD CONSTRAINT FK_CONTRAT_RESPONSABLE_CLIENT FOREIGN KEY (NO_RESPONSABLE_CLIENT)
        REFERENCES RESPONSABLE_CLIENT (NO_RESPONSABLE_CLIENT),
        ADD CONSTRAINT FK_CONTRAT_SOUMISSION FOREIGN KEY (NO_SOUMISSION)
        REFERENCES SOUMISSION (NO_SOUMISSION),
        ADD CONSTRAINT FK_CONTRAT_TYPE_CONTRAT FOREIGN KEY (ID_TYPE_CONTRAT)
        REFERENCES TYPE_CONTRAT (ID_TYPE_CONTRAT);;
/* Lignes affectées : 0  Lignes trouvées : 0  Avertissements : 0  Durée pour 1 requête : 0,156 s */

The Stop Errors in batch mode is depressed, but as you see in the previous line there are no errors

Nothing is written in the database, except the table itself and the indexes, but the indexe columns are not referenced to original table

• Which server and server version are we talking about?
• did you already exit HeidiSQL and restart it?

Hi I did exit and restart many times.

Could we talk in real time. I'm in Eastern Standard Time. Could use ZOOM to talk first. Fern

I have the impression we are nearly real time here. Not sure how Zoom would make it quicker. Also, if we solve it here, we could help others having the same problem, later.

Could you post the result of SHOW CREATE TABLE CONTRAT please.

Probably related thread.

SHOW CREATE TABLE CONTRAT

CREATE TABLE `contrat` (
  `NO_CONTRAT` int(11) NOT NULL AUTO_INCREMENT,
  `DATE_CREATION_CONTRAT` date DEFAULT NULL,
  `DATE_MODIFICATION_CONTRAT` date DEFAULT NULL,
  `DATE_DEBUT_CONTRAT` date DEFAULT NULL,
  `EXPLOITATION` char(3) DEFAULT NULL,
  `EXPLORATION` char(3) DEFAULT NULL,
  `NO_RESPONSABLE_CLIENT` int(11) NOT NULL,
  `NO_SOUMISSION` int(11) NOT NULL,
  `ID_TYPE_CONTRAT` int(11) NOT NULL,
  PRIMARY KEY (`NO_CONTRAT`),
  KEY `FK_CONTRAT_RESPONSABLE_CLIENT` (`NO_RESPONSABLE_CLIENT`),
  KEY `FK_CONTRAT_SOUMISSION` (`NO_SOUMISSION`),
  KEY `FK_CONTRAT_TYPE_CONTRAT` (`ID_TYPE_CONTRAT`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2

It shows INDEXES INSTEAD OF FK CONSTRAINTS

This is how it ought to be

/***********************************************************
    Génération du DDL
    Schéma MRD         : "Schéma1 1.0"
    Fichier MRD        : "(2) processeur bois.mrd"
    Généré le          : 01/04/2020 11:31:20
    Par                : l'Interface MRD-ODBC 2.9.2.0
***********************************************************/

/***********************************************************
    ÉNONCÉS CREATE
***********************************************************/

/***********************************************************
    Table "CONTRAT"
***********************************************************/

CREATE TABLE CONTRAT
(
    NO_CONTRAT      INT NOT NULL,
    DATE_CREATION_CONTRAT DATE,
    DATE_MODIFICATION_CONTRAT DATE,
    DATE_DEBUT_CONTRAT DATE,
    EXPLOITATION    CHAR(3),
    EXPLORATION     CHAR(3),
    NO_RESPONSABLE_CLIENT INT NOT NULL,
    NO_SOUMISSION   INT NOT NULL,
    ID_TYPE_CONTRAT INT NOT NULL,
    PRIMARY KEY (NO_CONTRAT)
);

/***********************************************************
    Les clés étrangères de la table "CONTRAT"
***********************************************************/

**ALTER TABLE CONTRAT
        ADD CONSTRAINT FK_CONTRAT_RESPONSABLE_CLIENT FOREIGN KEY (NO_RESPONSABLE_CLIENT)
        REFERENCES RESPONSABLE_CLIENT (NO_RESPONSABLE_CLIENT),
        ADD CONSTRAINT FK_CONTRAT_SOUMISSION FOREIGN KEY (NO_SOUMISSION)
        REFERENCES SOUMISSION (NO_SOUMISSION),
        ADD CONSTRAINT FK_CONTRAT_TYPE_CONTRAT FOREIGN KEY (ID_TYPE_CONTRAT)
        REFERENCES TYPE_CONTRAT (ID_TYPE_CONTRAT);
**
/*  Fin

I'm a model manager. I worked for Intel a couple of times to manage their SAP model and used Silverrun to do so. It generates your model in any of those databases version. But still, it seems Heidi does not create the FK even if we try to create them in the tool.

Here's my result of SHOW CREATE TABLE contrat, after creating the foreign keys:

CREATE TABLE `contrat` (
  `NO_CONTRAT` int(11) NOT NULL,
  `DATE_CREATION_CONTRAT` date DEFAULT NULL,
  `DATE_MODIFICATION_CONTRAT` date DEFAULT NULL,
  `DATE_DEBUT_CONTRAT` date DEFAULT NULL,
  `EXPLOITATION` char(3) DEFAULT NULL,
  `EXPLORATION` char(3) DEFAULT NULL,
  `NO_RESPONSABLE_CLIENT` int(11) NOT NULL,
  `NO_SOUMISSION` int(11) NOT NULL,
  `ID_TYPE_CONTRAT` int(11) NOT NULL,
  PRIMARY KEY (`NO_CONTRAT`),
  KEY `FK_CONTRAT_RESPONSABLE_CLIENT` (`NO_RESPONSABLE_CLIENT`),
  KEY `FK_CONTRAT_TYPE_CONTRAT` (`ID_TYPE_CONTRAT`),
  KEY `FK_CONTRAT_SOUMISSION` (`NO_SOUMISSION`),
  CONSTRAINT `FK_CONTRAT_RESPONSABLE_CLIENT` FOREIGN KEY (`NO_RESPONSABLE_CLIENT`) REFERENCES `responsable_client` (`NO_RESPONSABLE_CLIENT`),
  CONSTRAINT `FK_CONTRAT_SOUMISSION` FOREIGN KEY (`NO_SOUMISSION`) REFERENCES `soumission` (`NO_SOUMISSION`),
  CONSTRAINT `FK_CONTRAT_TYPE_CONTRAT` FOREIGN KEY (`ID_TYPE_CONTRAT`) REFERENCES `type_contrat` (`ID_TYPE_CONTRAT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

You see I got the foreign keys.

I created them using the table designer in HeidiSQL.

If I do that with your script from above, I get this one (quite the same):

CREATE TABLE `contrat2` (
  `NO_CONTRAT` int(11) NOT NULL,
  `DATE_CREATION_CONTRAT` date DEFAULT NULL,
  `DATE_MODIFICATION_CONTRAT` date DEFAULT NULL,
  `DATE_DEBUT_CONTRAT` date DEFAULT NULL,
  `EXPLOITATION` char(3) DEFAULT NULL,
  `EXPLORATION` char(3) DEFAULT NULL,
  `NO_RESPONSABLE_CLIENT` int(11) NOT NULL,
  `NO_SOUMISSION` int(11) NOT NULL,
  `ID_TYPE_CONTRAT` int(11) NOT NULL,
  PRIMARY KEY (`NO_CONTRAT`),
  KEY `FK_CONTRAT_RESPONSABLE_CLIENT2` (`NO_RESPONSABLE_CLIENT`),
  KEY `FK_CONTRAT_SOUMISSION2` (`NO_SOUMISSION`),
  KEY `FK_CONTRAT_TYPE_CONTRAT2` (`ID_TYPE_CONTRAT`),
  CONSTRAINT `FK_CONTRAT_RESPONSABLE_CLIENT2` FOREIGN KEY (`NO_RESPONSABLE_CLIENT`) REFERENCES `responsable_client` (`NO_RESPONSABLE_CLIENT`),
  CONSTRAINT `FK_CONTRAT_SOUMISSION2` FOREIGN KEY (`NO_SOUMISSION`) REFERENCES `soumission` (`NO_SOUMISSION`),
  CONSTRAINT `FK_CONTRAT_TYPE_CONTRAT2` FOREIGN KEY (`ID_TYPE_CONTRAT`) REFERENCES `type_contrat` (`ID_TYPE_CONTRAT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

I don't have a clue how to help further here.

Did you probably fire the ALTER queries in a different database?

I will try again on a MariaDB 10.4 server, like you did. But I have no hope that suddenly let's me reproduce this issue.

The alter query's done just after the create tables. Could be the MariaDB that has the problem?

That doesn't matter.

While I think you were using "the tool" in HeidiSQL to create the foreign keys (and I guess you mean the visual table designer), that also does not matter. Foreign keys should be there afterwards.

On MariaDB 10.4.10, I can even create a foreign key referencing a column of the same table:

CREATE TABLE `contrat2` (
  `NO_CONTRAT` int(11) NOT NULL,
  `DATE_CREATION_CONTRAT` date DEFAULT NULL,
  `DATE_MODIFICATION_CONTRAT` date DEFAULT NULL,
  `DATE_DEBUT_CONTRAT` date DEFAULT NULL,
  `EXPLOITATION` char(3) DEFAULT NULL,
  `EXPLORATION` char(3) DEFAULT NULL,
  `NO_RESPONSABLE_CLIENT` int(11) NOT NULL,
  `NO_SOUMISSION` int(11) NOT NULL,
  `ID_TYPE_CONTRAT` int(11) NOT NULL,
  PRIMARY KEY (`NO_CONTRAT`),
  KEY `FK_contrat2_contrat2` (`NO_RESPONSABLE_CLIENT`),
  CONSTRAINT `FK_contrat2_contrat2` FOREIGN KEY (`NO_RESPONSABLE_CLIENT`) REFERENCES `contrat2` (`NO_CONTRAT`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

It works that way too. I just ought to create the FK constraint at the same time I create the table BUT IT DOES NOT WORK SAYING THE REFERENCES IS NOT CORRECT

CREATE TABLE contrat2 ( NO_CONTRAT int(11) NOT NULL, DATE_CREATION_CONTRAT date DEFAULT NULL, DATE_MODIFICATION_CONTRAT date DEFAULT NULL, DATE_DEBUT_CONTRAT date DEFAULT NULL, EXPLOITATION char(3) DEFAULT NULL, EXPLORATION char(3) DEFAULT NULL, NO_RESPONSABLE_CLIENT int(11) NOT NULL, NO_SOUMISSION int(11) NOT NULL, ID_TYPE_CONTRAT int(11) NOT NULL, PRIMARY KEY (NO_CONTRAT), KEY FK_contrat2_contrat2 (NO_RESPONSABLE_CLIENT), CONSTRAINT FK_contrat2_contrat2 FOREIGN KEY (NO_RESPONSABLE_CLIENT) REFERENCES contrat2 (NO_CONTRAT) ) ENGINE=InnoDB DEFAULT CHARSET=LATIN1

CREATE TABLE alarme2 ( ID_ALARME INT NOT NULL, FONCTIONH VARCHAR(255), UTILISATEUR_NOUVEAU_ROLE VARCHAR(255), MOT_DE_PASSE VARCHAR(255), HOST_KEY VARCHAR(255), TELEPHONE_CEL VARCHAR(255), CODE_SECU_SYSTEME_ALARME DOUBLE(10, 2), ID_FOURNISSEUR INT NOT NULL, PRIMARY KEY (ID_ALARME), KEY FK_Alarme2_Alarme2 (ID_FOURNISSEUR), CONSTRAINT FK_Alarme2_Alarme2 FOREIGN KEY (ID_FOURNISSEUR) REFERENCES fournisseur (ID_FOURNISSEUR) ) ENGINE=InnoDB DEFAULT CHARSET=LATIN1

I don't know what I've written improperly

CREATE TABLE fournisseur ( ID_FOURNISSEUR int(11) NOT NULL AUTO_INCREMENT, NOM_FOURNISSEUR varchar(45) DEFAULT NULL, NO_COMPTE_FOURNISSEUR varchar(45) DEFAULT NULL, NO_TEL_FOURNISSEUR varchar(14) DEFAULT NULL, ADRESSE_INTERNET varchar(255) DEFAULT NULL, NOM_RESPONSABLE varchar(255) DEFAULT NULL, COMMENTAIRE varchar(255) DEFAULT NULL, NO_TPS varchar(255) DEFAULT NULL, NO_TVQ varchar(255) DEFAULT NULL, LIMITE double(10,2) DEFAULT NULL, DERN_ACHAT date DEFAULT NULL, ACHAT_A_DATE double(10,2) DEFAULT NULL, ESC varchar(255) DEFAULT NULL, POURC_ESC double(10,2) DEFAULT NULL, ACC_TPS varchar(255) DEFAULT NULL, ACC_TVQ varchar(255) DEFAULT NULL, LIBRE_MONTANT double(10,2) DEFAULT NULL, NO_ADRESSE int(11) NOT NULL, ID_TYPE_FOURNISSEUR int(11) NOT NULL, NO_FOURNISSEUR int(11) DEFAULT NULL, NO_GL char(20) DEFAULT NULL, PRIMARY KEY (ID_FOURNISSEUR), KEY FK_FOURNISSEUR_ADRESSE (NO_ADRESSE), KEY FK_FOURNISSEUR_TYPE_FOURNISSEUR (ID_TYPE_FOURNISSEUR) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin2

almost there, now its says that the foreign key oonstraint is incorrectly formed can't find the issue when I compare what you sent to me before

Even writing it this way does not work

CREATE TABLE alarme2 ( ID_ALARME int(11) NOT NULL, FONCTION varchar(255) DEFAULT NULL, UTILISATEUR_NOUVEAU_ROLE varchar(255) DEFAULT NULL, MOT_DE_PASSE varchar(255) DEFAULT NULL, HOST_KEY varchar(255) DEFAULT NULL, TELEPHONE_CEL varchar(255) DEFAULT NULL, CODE_SECU_SYSTEME_ALARME double(10,2) DEFAULT NULL, ID_FOURNISSEUR int(11) NOT NULL, PRIMARY KEY (ID_ALARME), KEY FK_ALARME_FOURNISSEUR (ID_FOURNISSEUR), CONSTRAINT FK_ALARME_FOURNISSEUR FOREIGN KEY (ID_FOURNISSEUR) REFERENCES alarme2 (ID_FOURNISSEUR) ) ENGINE=InnoDB DEFAULT CHARSET=LATIN1

That's most probably happening because the id_fournisseur is defined differently than in the source table. E.g. the "unsigned" setting must match, and the datatype.

I only wonder what you did previously, as you never posted any error message up to now?

This is your example but in the creation of another table. I realized that the FK references to different columns

CREATE TABLE alarme2 ( ID_ALARME int(11) NOT NULL, FONCTION varchar(255) DEFAULT NULL, UTILISATEUR_NOUVEAU_ROLE varchar(255) DEFAULT NULL, MOT_DE_PASSE varchar(255) DEFAULT NULL, HOST_KEY varchar(255) DEFAULT NULL, TELEPHONE_CEL varchar(255) DEFAULT NULL, CODE_SECU_SYSTEME_ALARME double(10,2) DEFAULT NULL, ID_FOURNISSEUR int(11) NOT NULL, PRIMARY KEY (ID_ALARME), KEY FK_ALARME_FOURNISSEUR (ID_FOURNISSEUR), CONSTRAINT FK_ALARME_FOURNISSEUR FOREIGN KEY (ID_FOURNISSEUR) REFERENCES alarme2 (ID_ALARME) ) ENGINE=InnoDB DEFAULT CHARSET=LATIN1

IT WORKS WHEN YOU REFERENCE TO DIFFERENT COLUMNS IN THE SAME TABLE, but NOT if you reference a two identical columns from different tables

See this does not work. But it ought to, because the ID_TYPE_CONTRAT is the key of the table TYPE_CONTRAT, that provides the referential integrity related to the existence of a value in the table CONTRAT5, value that as to be the PRIMARY KEY of the table TYPE_CONTRAT.

CREATE TABLE contrat5 ( NO_CONTRAT int(11) NOT NULL, DATE_CREATION_CONTRAT date DEFAULT NULL, DATE_MODIFICATION_CONTRAT date DEFAULT NULL, DATE_DEBUT_CONTRAT date DEFAULT NULL, EXPLOITATION char(3) DEFAULT NULL, EXPLORATION char(3) DEFAULT NULL, NO_RESPONSABLE_CLIENT int(11) NOT NULL, NO_SOUMISSION int(11) NOT NULL, ID_TYPE_CONTRAT int(11) NOT NULL, PRIMARY KEY (NO_CONTRAT), KEY FK_contrat5_contrat5 (ID_TYPE_CONTRAT), CONSTRAINT FK_contrat5_contrat5 FOREIGN KEY (ID_TYPE_CONTRAT) REFERENCES TYPE_CONTRAT (ID_TYPE_CONTRAT) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

But if I do it this way it works. Nonsense. It references to the primary key of the same table.

CREATE TABLE contrat5 ( NO_CONTRAT int(11) NOT NULL, DATE_CREATION_CONTRAT date DEFAULT NULL, DATE_MODIFICATION_CONTRAT date DEFAULT NULL, DATE_DEBUT_CONTRAT date DEFAULT NULL, EXPLOITATION char(3) DEFAULT NULL, EXPLORATION char(3) DEFAULT NULL, NO_RESPONSABLE_CLIENT int(11) NOT NULL, NO_SOUMISSION int(11) NOT NULL, ID_TYPE_CONTRAT int(11) NOT NULL, PRIMARY KEY (NO_CONTRAT), KEY FK_contrat5_contrat5 (ID_TYPE_CONTRAT), CONSTRAINT FK_contrat5_contrat5 FOREIGN KEY (ID_TYPE_CONTRAT) REFERENCES contrat5 (NO_CONTRAT) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

this works Still have to figure it out properly so I could populate the database correctly Thanks for your help. There is a unique way of doing things with the appropriate receipe.

I found this example

https://mariadb.com/kb/en/foreign-keys/

CREATE TABLE author ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL ) ENGINE = InnoDB;

CREATE TABLE book ( id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200) NOT NULL, author_id SMALLINT UNSIGNED NOT NULL, CONSTRAINT fk_book_author FOREIGN KEY (author_id) REFERENCES author (id) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB;

I tranlate into this

CREATE TABLE responsable_client2 ( NO_RESPONSABLE_CLIENT SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, PRENOM_RESPONSABLE_CLIENT varchar(65) DEFAULT NULL ) ENGINE=INNODB

CREATE TABLE contrat5 ( NO_RESPONSABLE_CLIENT SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, NO_SOUMISSION int(11) NOT NULL, ID_TYPE_CONTRAT int(11) NOT NULL, CONSTRAINT FK_contrat5_contrat5 FOREIGN KEY (NO_RESPONSABLE_CLIENT) REFERENCES RESPONSABLE_CLIENT2 (NO_RESPONSABLE_CLIENT) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE=INNODB

I think we ougth to indicate all the time

ON DELETE CASCADE ON UPDATE RESTRICT

to complete the task

not true sometimes it works sometimes is does not

How come this tool does not work properly, or is it because developper don't mind?

As said quite some times before, I could not reproduce that issue. And I still believe you did something wrong, or maybe are using some setting which you forgot or similar.

Sometime it works, sometime it does not work. If it was not configured properly it would never work. I never seen this before. I worked with Sybase, Oracle, Terradata, SQL Server, SQL Anywhere...never worked out like this. Just disapointed. I'm working on a project and I'll have to start all over again.

These 2 work

CREATE TABLE author (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE book3 (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author_id SMALLINT UNSIGNED NOT NULL,
  CONSTRAINT `fk_book_author3`
    FOREIGN KEY (author_id) REFERENCES author (id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
) ENGINE = InnoDB;

CREATE TABLE `responsable_client2` (
  `NO_RESPONSABLE_CLIENT` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `PRENOM_RESPONSABLE_CLIENT` varchar(65) DEFAULT NULL
) ENGINE=INNODB

CREATE TABLE `contrat5` (
  `NO_RESPONSABLE_CLIENT` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `NO_SOUMISSION` int(11) NOT NULL,
  `ID_TYPE_CONTRAT` int(11) NOT NULL,
  CONSTRAINT `FK_contrat5_contrat5` 
  FOREIGN KEY (`NO_RESPONSABLE_CLIENT`) REFERENCES `RESPONSABLE_CLIENT2` (`NO_RESPONSABLE_CLIENT`)
   ON DELETE CASCADE
   ON UPDATE RESTRICT
) ENGINE=INNODB

But not this one. If you could explain why, then we are close to the solution

CREATE TABLE VILLE
(
    NO_VILLE        SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    NOM_VILLE       VARCHAR(50)
)ENGINE=INNODB

CREATE TABLE ADRESSE
(
    NO_ADRESSE      SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    NO_CIVIQUE_RUE  VARCHAR(45),
    BOITE_POSTALE   VARCHAR(35),
    CODE_POSTAL     VARCHAR(7),
    NO_VILLE        SMALLINT UNSIGNED NOT NULL,
    ADRESSE_2       VARCHAR(35),
   CONSTRAINT `FK_ADRESSE_VILLE` 
    FOREIGN KEY (`NO_VILLE`)
   REFERENCES `VILLE` (`NO_VILLE`)
            ON DELETE CASCADE
        ON UPDATE RESTRICT    
);

I just installed MySQL Workbench and executed my entire script, one shot, directly from Silverrun output. Every tables are there with the FK, every one of them

I just tested your both scripts from above here, and both work, including the one which you say did not work. I only had to add some missing semicolons to the end of queries. Are you sure you added these on your side as well?

And also, you should probably send queries one by one, although both should work, but just in case:

I see the table in question (contrat) is using MyISAM storage engine. It seems MyISAM does not support foreign keys (sorry can't link to the relevant kb at mariadb.com, look for Server Documentation, Storage Engines, MyISAM, MyISAM Overview).

Hi Pasty How can you see MyISAM storage engine?

How can you see MyISAM storage engine?

In the code listing in the 7th post, in the CREATE TABLE statement for the contrat table the storage engine is being set to MyISAM (ENGINE=MyISAM).

It worked this manner. Thanks. I was now trying to figure out, why it was consistent. I think you gave me an appropriate answer. Thanks

CREATE TABLE VILLE ( NO_VILLE SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, NOM_VILLE VARCHAR(50) )ENGINE=INNODB;

CREATE TABLE ADRESSE ( NO_ADRESSE SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, NO_CIVIQUE_RUE VARCHAR(45), BOITE_POSTALE VARCHAR(35), CODE_POSTAL VARCHAR(7), NO_VILLE SMALLINT UNSIGNED NOT NULL, ADRESSE_2 VARCHAR(35), CONSTRAINT FK_ADRESSE_VILLE FOREIGN KEY (NO_VILLE) REFERENCES VILLE (NO_VILLE) ON DELETE CASCADE ON UPDATE RESTRICT
)ENGINE=INNODB;

And I oversaw that all the time..

Well Patsy. One more issue to solve. How comme I cannot create and save FK directly in the database. It appears properly, but I cannot save it.

Is this because it seems not to have been generated with INNODB?

If a least I could save the modification. There must exist a manner of correcting the problem

It appears properly, but I cannot save it.

Then you will get some error message. Why don't you post it here?

This the message that appears once the INSERT (Enregistrer) button is pressed. No error written

SELECT 1 FROM categorie_bien LIMIT 1; SELECT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='inventaire' AND TABLE_NAME='categorie_bien' ORDER BY ORDINAL_POSITION; ALTER TABLE bien ADD CONSTRAINT FK1_bien_categorie_bien FOREIGN KEY (ID_CATEGORIE_BIEN) REFERENCES categorie_bien (ID_CATEGORIE_BIEN); SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME='inventaire'; SHOW TABLE STATUS FROM inventaire; SHOW FUNCTION STATUS WHERE Db='inventaire'; SHOW PROCEDURE STATUS WHERE Db='inventaire'; SHOW TRIGGERS FROM inventaire; SELECT , EVENT_SCHEMA AS Db, EVENT_NAME AS Name FROM information_schema.EVENTS WHERE EVENT_SCHEMA='inventaire'; SELECT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='inventaire' AND TABLE_NAME='bien' ORDER BY ORDINAL_POSITION; SHOW INDEXES FROM bien FROM inventaire; SELECT FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='inventaire' AND TABLE_NAME='bien' AND REFERENCED_TABLE_NAME IS NOT NULL; SELECT FROM information_schema.KEY_COLUMN_USAGE WHERE CONSTRAINT_SCHEMA='inventaire' AND TABLE_NAME='bien' AND REFERENCED_TABLE_NAME IS NOT NULL; / Ouverture de la session « Asinii » */ SHOW CREATE TABLE inventaire.bien;

Is this because it seems not to have been generated with INNODB?

First make sure both the child (referencing) table (the one for which the FOREIGN KEY constraint is being defined) and the parent (referenced) table (the other table, whose columns are being referenced by the child table) use InnoDB storage engine. Only then try adding the FOREIGN KEY constraint for the child table.

Why are there so many different storage engine? I globaly modified all databases I have created to InnoDB storage engine

Patsy I works properly now since the modification triggered to InnoDB storage engine

Thank you very much

Can you tell me what the differences between all those database motors?

This code generated from Silverrun RDM (This tool has been created by two professors that teached me in Laval University, in Quebec City Canada, while following an MBA specialized in Managing Information Technology. I've myself been using this tool since 1997. Small application but really efficient. It can be experimented by downloading a copy on the Silverrun Grandite's site)

When creating each table, I had to add "ENGINE=InnoDB DEFAULT CHARSET=latin2" at the end on the create code, as you can see navigating the code.

/ Génération du DDL Schéma MRD : "Schéma1 1.0" Fichier MRD : "(1) dfn.mrd" Généré le : 10/04/2020 11:58:54 Par : l'Interface MRD-ODBC 2.9.2.0 /

/ ÉNONCÉS CREATE /

/ Table "ADRESSE" /

CREATE TABLE ADRESSE ( NO_ADRESSE INT NOT NULL, NO_CIVIQUE_RUE VARCHAR(45), BOITE_POSTALE VARCHAR(35), CODE_POSTAL VARCHAR(7), NO_VILLE INT NOT NULL, ADRESSE_2 VARCHAR(35), PRIMARY KEY (NO_ADRESSE) )ENGINE=InnoDB DEFAULT CHARSET=latin2;

/ Table "FONCTION" /

CREATE TABLE FONCTION ( NO_FONCTION INT NOT NULL, FONCTION VARCHAR(50), DATE_DEBUT_FONCTION DATE, DATE_FIN_FONCTION DATE, PRIMARY KEY (NO_FONCTION) )ENGINE=InnoDB DEFAULT CHARSET=latin2;

/ Table "PROVINCE" /

CREATE TABLE PROVINCE ( CODE_PROVINCE VARCHAR(8) NOT NULL, NOM_PROVINCEE VARCHAR(30), PRIMARY KEY (CODE_PROVINCE) )ENGINE=InnoDB DEFAULT CHARSET=latin2;

/ Table "USAGER" /

CREATE TABLE USAGER ( NO_USAGER INT NOT NULL, NOM_USAGER VARCHAR(255), PRENOM_USAGER VARCHAR(255), DATE_DEBUT_USAGER DATE, FORMATION_SECURITE CHAR(3), TELEPHONE VARCHAR(255), NO_EMPLOYEUR VARCHAR(255), NAS VARCHAR(255), DATE_NAISSANCE DATE, RAISON VARCHAR(255), TAUX_ASS_EM DOUBLE(10, 2), VAC_PAYER VARCHAR(255), POUR_VACANCE DOUBLE(10, 2), MONTANT_VACANCE DOUBLE(10, 2), EXEMPT_FED DOUBLE(10, 2), EXTRA_FED DOUBLE(10, 2), EXEMPT_PROV DOUBLE(10, 2), EXTRA_PROV DOUBLE(10, 2), ADM_ASS_EM VARCHAR(255), ADM_R_C VARCHAR(255), V_POURB_IV DOUBLE(10, 2), VAC_PAYE_POU DOUBLE(10, 2), LIBRE_MONTANT DOUBLE(10, 2), LIBRE VARCHAR(255), NO_TRANSIT DOUBLE(10, 2), NO_ETABLISSEMENT DOUBLE(10, 2), NO_COMPTE DOUBLE(10, 2), CODE_METIER DOUBLE(10, 2), TYPE_TRAVAIL DOUBLE(10, 2), REGION_TRAVAIL DOUBLE(10, 2), SYNDICAT VARCHAR(255), PROV_EMPLOI DOUBLE(10, 2), PERIODE_APPR VARCHAR(255), STATUS VARCHAR(255), ANNEXE_SALAIRE VARCHAR(255), ADRESSE_COURRIEL VARCHAR(60), PREMIERE_NATION CHAR(3), NO_ADRESSE INT, NO_FONCTION INT NOT NULL, NO_EMPLOYE INT, NOMBRE_COUVRETOUT INT, DATE_COUVRETOUT DATE, DATE_DEPART DATE, COMMENT LONGTEXT, EMERGENCY_CONTACT VARCHAR(50), EMERGENCY_CONTACT_NUMBER VARCHAR(14), INACTIV BIT, PRIMARY KEY (NO_USAGER) )ENGINE=InnoDB DEFAULT CHARSET=latin2;

/ Table "VILLE" /

CREATE TABLE VILLE ( NO_VILLE INT NOT NULL, NOM_VILLE VARCHAR(50), CODE_PROVINCE VARCHAR(8) NOT NULL, PRIMARY KEY (NO_VILLE) )ENGINE=InnoDB DEFAULT CHARSET=latin2;

/ Les clés étrangères de la table "ADRESSE" /

ALTER TABLE ADRESSE ADD CONSTRAINT FK_ADRESSE_VILLE FOREIGN KEY (NO_VILLE) REFERENCES VILLE (NO_VILLE);

/ Les clés étrangères de la table "USAGER" /

ALTER TABLE USAGER ADD CONSTRAINT FK_USAGER_ADRESSE FOREIGN KEY (NO_ADRESSE) REFERENCES ADRESSE (NO_ADRESSE), ADD CONSTRAINT FK_USAGER_FONCTION FOREIGN KEY (NO_FONCTION) REFERENCES FONCTION (NO_FONCTION);

/ Les clés étrangères de la table "VILLE" /

ALTER TABLE VILLE ADD CONSTRAINT FK_VILLE_PROVINCE FOREIGN KEY (CODE_PROVINCE) REFERENCES PROVINCE (CODE_PROVINCE);

/ Fin /

select adresse.NO_ADRESSE AS NO_ADRESSE,adresse.NO_CIVIQUE_RUE + ' / ' + ville.NOM_VILLE + ' / ' + province.NOM_PROVINCE AS liste_adresse from ((ville join adresse on(adresse.NO_VILLE = ville.NO_VILLE)) join province on(ville.ID_PROVINCE = province.ID_PROVINCE))

This is the type of view I can create with Sql Server. I need the adresse number including the street name which is " adresse.NO_CIVIQUE_RUE' " + the name of the city " ville.NOM_VILLE " + the name of the province " province.NOM_PROVINCE "

I need to concatenate those 3 elements as Liste_adresse. The picture shows the result as it is, but it does not concatenate as SQL server does. What am I doing wrong? I check the help CONCAT, tried using the code, seems it does only concatenate words, but not related tables. ????

What does it mean?

when I execute the view, no error message

This is the code that appears below

SELECT CAST(LOAD_FILE(CONCAT(IFNULL(@@GLOBAL.datadir, CONCAT(@@GLOBAL.basedir, 'data/')), 'processeur_bois/liste_adresse.frm')) AS CHAR CHARACTER SET utf8);

With that LOAD_FILE() command, HeidiSQL tries to load the original view code from a definition file on the server.

no error message, do you have an example I could use?

how come there is no way to concatenate data from different tables or different columns from the same table

like name, surname and phone number as an example

really simple for Oracle concatenation

CREATE TABLE tab1 (col1 VARCHAR2(6), col2 CHAR(6), col3 VARCHAR2(6), col4 CHAR(6) );

INSERT INTO tab1 (col1, col2, col3, col4) VALUES ('abc', 'def ', 'ghi ', 'jkl'); SELECT col1||col2||col3||col4 "Concatenation" FROM tab1;

Concatenation

abcdef ghi jkl

this is the solution

select CONCAT(adresse.NO_CIVIQUE_RUE ,' ',ville.NOM_VILLE ,' ', province.NOM_PROVINCEE) as liste_adresse

from ((ville join adresse on(adresse.NO_VILLE = ville.NO_VILLE)) join province on(ville.ID_PROVINCE = province.ID_PROVINCE));

Even better

select adresse.NO_ADRESSE AS no_adresse,concat(adresse.NO_CIVIQUE_RUE,' '/',',' ',ville.NOM_VILLE,' ','/',' ',province.NOM_PROVINCEE) AS liste_adresse from ((ville join adresse on(adresse.NO_VILLE = ville.NO_VILLE)) join province on(ville.ID_PROVINCE = province.ID_PROVINCE))

forgot to send the result

This example ought to be included in the Help

https://www.mysqltutorial.org/sql-concat-in-mysql.aspx

Hi Someone could tell me how to connect Heidi to SQL Server