Foreign Key

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago in General Permalink

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

2 attachment(s):
  • FK-CREATE
  • FK-DISAPEARED
fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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);

ansgar's profile image ansgar posted 4 years ago Permalink

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

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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

???

ansgar's profile image ansgar posted 4 years ago Permalink

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

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

Done but still does not workd

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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);
ansgar's profile image ansgar posted 4 years ago Permalink

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

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink
/***********************************************************
    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)
);

/***************
ansgar's profile image ansgar posted 4 years ago Permalink

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:

Description

I suppose these were not generated on your side.

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

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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.

ansgar's profile image ansgar posted 4 years ago Permalink

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:

Description

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink
/***********************************************************
    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

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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

ansgar's profile image ansgar posted 4 years ago Permalink
  • Which server and server version are we talking about?
  • did you already exit HeidiSQL and restart it?
fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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

3 attachment(s):
  • mariadb
  • message-button
  • mariadb-version
ansgar's profile image ansgar posted 4 years ago Permalink

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.

ansgar's profile image ansgar posted 4 years ago Permalink

Probably related thread.

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink
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.

2 attachment(s):
  • model-Silverunn
  • 91580486_149831939722118_6682234054693093376_n
ansgar's profile image ansgar posted 4 years ago Permalink

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
ansgar's profile image ansgar posted 4 years ago Permalink

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.

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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

ansgar's profile image ansgar posted 4 years ago Permalink

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
fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

I don't know what I've written improperly

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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

1 attachment(s):
  • alarme
fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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

ansgar's profile image ansgar posted 4 years ago Permalink

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.

ansgar's profile image ansgar posted 4 years ago Permalink

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

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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

1 attachment(s):
  • CONTRAT
fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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

1 attachment(s):
  • contrat-5
fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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

1 attachment(s):
  • book
fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

I think we ougth to indicate all the time

ON DELETE CASCADE ON UPDATE RESTRICT

to complete the task

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

not true sometimes it works sometimes is does not

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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

ansgar's profile image ansgar posted 4 years ago Permalink

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.

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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.

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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
fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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    
);
fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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

1 attachment(s):
  • Capture
ansgar's profile image ansgar posted 4 years ago Permalink

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:

Description

pasty's profile image pasty posted 4 years ago Permalink

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).

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

Hi Pasty How can you see MyISAM storage engine?

pasty's profile image pasty posted 4 years ago Permalink

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).

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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;

ansgar's profile image ansgar posted 4 years ago Permalink

And I oversaw that all the time..

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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.

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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

2 attachment(s):
  • Before-saving
  • after-saving
ansgar's profile image ansgar posted 4 years ago Permalink

It appears properly, but I cannot save it.

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

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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;

pasty's profile image pasty posted 4 years ago Permalink

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.

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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

Thank you very much

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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

1 attachment(s):
  • 92574484_210938886880580_6416008930597535744_n
fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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 /

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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. ????

1 attachment(s):
  • liste-adresse
fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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);

ansgar's profile image ansgar posted 4 years ago Permalink

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

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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));

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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))

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

forgot to send the result

1 attachment(s):
  • concat
fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

This example ought to be included in the Help

1 attachment(s):
  • concat-help-example
fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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

fernand.st-georges@protonmail.com's profile image fernand.st-georges@protonmail.com posted 4 years ago Permalink

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

Please login to leave a reply, or register at first.