URL: https://linuxfr.org/forums/programmation-sql/posts/foreign-keys-et-mysql
Title: FOREIGN KEYS et MySQL....
Authors: David FRANCOIS
Date: 2006年10月14日T12:21:08+02:00
Tags: 
Score: 0
Bonjour,
je n'arrive pas à mettre en place une clef étrangère entre deux tables. J'essaye de dire à MySQL de positionner à NULL le champ Personnel.agence en cas d'effacement des lignes de la table Agence et également d'updater la clef étrangère mais ma requête ne passe pas...
Petite précision, mes tables sont bien en InnoDB.
 +-------------------+
 | Tables_in_nf16 |
 +-------------------+
 | Agence |
 | Locataire |
 | Personnel |
 | Proprietaire |
 | Propriete_a_louer |
 | Visite |
 +-------------------+
 6 rows in set (0.00 sec)
 
 mysql> DESC Agence;
 +-----------+--------------+------+-----+---------+----------------+
 | Field | Type | Null | Key | Default | Extra |
 +-----------+--------------+------+-----+---------+----------------+
 | numa | mediumint(9) | NO | PRI | NULL | auto_increment |
 | adresse | varchar(255) | NO | | NULL | |
 | telephone | varchar(10) | NO | | NULL | |
 +-----------+--------------+------+-----+---------+----------------+
 3 rows in set (0.00 sec)
 
 mysql> DESC Personnel;
 +----------+--------------+------+-----+---------+----------------+
 | Field | Type | Null | Key | Default | Extra |
 +----------+--------------+------+-----+---------+----------------+
 | nump | mediumint(9) | NO | PRI | NULL | auto_increment |
 | nom | varchar(50) | NO | | NULL | |
 | prenom | varchar(50) | NO | | NULL | |
 | adresse | varchar(255) | NO | | NULL | |
 | fonction | varchar(30) | NO | | NULL | |
 | salaire | mediumint(9) | NO | | NULL | |
 | agence | mediumint(9) | NO | MUL | NULL | |
 +----------+--------------+------+-----+---------+----------------+
 7 rows in set (0.00 sec)
 
 mysql> ALTER TABLE Agence ADD FOREIGN KEY numa REFERENCES Personnel(agence) ON DELETE SET NULL ON UPDATE CASCADE;
 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REFERENCES Personnel(agence) ON DELETE SET NULL ON UPDATE CASCADE' at line 1
</div><div class="naked_ctrl">
<form action="/index.cgi/contrast" method="get" name="gate">
<p><a href="http://altstyle.alfasado.net">AltStyle</a> によって変換されたページ <a href="https://linuxfr.org/forums/programmation-sql/posts/foreign-keys-et-mysql.md">(-&gt;オリジナル)</a>
/ <label>アドレス: <input type="text" name="naked_post_url" value="https://linuxfr.org/forums/programmation-sql/posts/foreign-keys-et-mysql.md" size="22" /></label> <label>モード: <select name="naked_post_mode">
<option value="default">デフォルト</option>
<option value="speech">音声ブラウザ</option>
<option value="ruby">ルビ付き</option>
<option value="contrast" selected="selected">配色反転</option>
<option value="larger-text">文字拡大</option>
<option value="mobile">モバイル</option>
</select>
<input type="submit" value="表示" />
</p>
</form>
</div>