Comment créer des relations dans MySQL

Question

En classe, nous étudions tous les bases de données, et tout le monde utilise Access. Comme cela m'ennuie, j'essaie de faire ce que le reste de la classe fait, mais avec des commandes SQL brutes avec MySQL au lieu d'utiliser Access.

J'ai réussi à créer des bases de données et des tables, mais maintenant comment faire pour établir une relation entre deux tables ?

Si j'ai mes deux tables comme ceci :

CREATE TABLE accounts(
    account_id INT NOT NULL AUTO_INCREMENT,
    customer_id INT( 4 ) NOT NULL ,
    account_type ENUM( 'savings', 'credit' ) NOT NULL,
    balance FLOAT( 9 ) NOT NULL,
    PRIMARY KEY ( account_id )
)

y

CREATE TABLE customers(
    customer_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    address VARCHAR(20) NOT NULL,
    city VARCHAR(20) NOT NULL,
    state VARCHAR(20) NOT NULL,
    PRIMARY KEY ( customer_id )
)

Comment puis-je créer une "relation" entre les deux tables ? Je veux que chaque compte se voie attribuer un numéro de client (pour indiquer qui en est le propriétaire).

Answer 1

Si les tables sont innodb vous pouvez le créer comme ceci :

CREATE TABLE accounts(
    account_id INT NOT NULL AUTO_INCREMENT,
    customer_id INT( 4 ) NOT NULL ,
    account_type ENUM( 'savings', 'credit' ) NOT NULL,
    balance FLOAT( 9 ) NOT NULL,
    PRIMARY KEY ( account_id ), 
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) 
) ENGINE=INNODB;

Vous devez spécifier que les tables sont innodb car le moteur myisam ne supporte pas les clés étrangères. Regardez aquí pour plus d'informations.

Answer 2

Comme l'a dit ehogue, mettez ceci dans votre CREATE TABLE

FOREIGN KEY (customer_id) REFERENCES customers(customer_id) 

Alternativement, si vous avez déjà créé la table, utilisez une commande ALTER TABLE :

ALTER TABLE `accounts`
  ADD CONSTRAINT `FK_myKey` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON DELETE CASCADE ON UPDATE CASCADE;

Une bonne façon de commencer à apprendre ces commandes est d'utiliser la commande Outils MySQL GUI qui vous offrent une interface plus "visuelle" pour travailler avec votre base de données. Le véritable avantage de cette méthode (par rapport à celle d'Access) est qu'après avoir conçu votre table via l'interface graphique, elle vous montre le langage SQL qu'elle va exécuter, ce qui vous permet d'en tirer des enseignements.

Answer 3

Pour compléter le commentaire de ehogue, vous devriez faire en sorte que la taille des clés des deux tables corresponde. Plutôt que de

customer_id INT( 4 ) NOT NULL ,

Faites-le

customer_id INT( 10 ) NOT NULL ,

et assurez-vous que votre colonne int dans la table des clients est également int(10).

Answer 4

Certains moteurs MySQL supportent les clés étrangères. Par exemple, InnoDB peut établir des contraintes basées sur des clés étrangères. Si vous essayez de supprimer une entrée dans une table qui a des dépendants dans une autre, la suppression échouera.

Si vous utilisez un type de table dans MySQL, comme MyISAM, qui ne prend pas en charge les clés étrangères, vous ne liez les tables nulle part ailleurs que dans vos diagrammes et vos requêtes.

Par exemple, dans une requête, vous liez deux tables dans une instruction de sélection avec une jointure :

SELECT a, b from table1 LEFT JOIN table2 USING (common_field);

Answer 5

Si vous voulez aller plus loin dans la conception visuelle de bases de données, essayez Banc d'essai MySQL