Kh75
Kh75

Reputation: 11

mySQL Database relations with DataGrip

Hi I'm starting to learn SQL last week and I faced a problem I write this mysql script to create a database The issue is that DataGrip (JetBrains) don't show me relations between tables when I display diagram Please help me to fix it here is the script

thanks for help

CREATE DATABASE IF NOT EXISTS inventaireParcInformatique;
USE inventaireParcInformatique;

CREATE TABLE UTILISATEUR (
                             idUser INT PRIMARY KEY,
                             nom VARCHAR(40) NOT NULL,
                             prenom VARCHAR(40) NOT NULL);

CREATE TABLE SALLES (
                        numSalle INT,
                        descriptif VARCHAR(250) NOT NULL,
                        CONSTRAINT pk_salles PRIMARY KEY (numSalle));


CREATE TABLE FABRICANTS (
                            codeFabricant CHAR(5),
                            nom VARCHAR(30) NOT NULL,
                            telephone CHAR(10),
                            CONSTRAINT pk_fabricants PRIMARY KEY (codeFabricant));

CREATE TABLE MACHINES (
                          idMachine INT,
                          codeFabricant CHAR(5) NOT NULL,
                          nom VARCHAR(100) NOT NULL,
                          addIP VARCHAR(4000),
                          addMAC VARCHAR(4000) NOT NULL,
                          DNS VARCHAR(4000),
                          commentaire LONGTEXT,
                          mobile TINYINT(1) NOT NULL DEFAULT 0,
                          numSalle INT,
                          operationnel TINYINT(1) NOT NULL DEFAULT 1 CHECK (operationnel IN (0,1)),
                          CONSTRAINT chk_mobile CHECK (mobile in (0,1)),
                          CONSTRAINT pk_machines PRIMARY KEY (idMachine),
                          CONSTRAINT fk_machines_fabricants FOREIGN KEY (codeFabricant) REFERENCES FABRICANTS(codeFabricant),
                          CONSTRAINT fk_machines_salles FOREIGN KEY (numSalle) REFERENCES SALLES(numSalle));


CREATE TABLE AFFECTATION (
                             idUser INT NOT NULL,
                             idMachine INT NOT NULL,
                             dateDebut DATE NOT NULL,
                             dateFin DATE,
                             CONSTRAINT pk_affectation PRIMARY KEY (idUser, idMachine, dateDebut));

ALTER TABLE AFFECTATION
    ADD CONSTRAINT fk_affectation_machines
        FOREIGN KEY (idMachine)
            REFERENCES MACHINES(idMachine);


ALTER TABLE AFFECTATION
    ADD CONSTRAINT fk_affectation_utilisateur
        FOREIGN KEY (idUser)
            REFERENCES UTILISATEUR(idUser);


CREATE TABLE TYPESCOMPOSANTS (
                                 codeType VARCHAR(10) PRIMARY KEY,
                                 libelle VARCHAR(50) NOT NULL);


enter image description here

Upvotes: 1

Views: 27

Answers (0)

Related Questions