- 
 
 
 

   


, , . , score sampdb student_d, ' student. , , score.student_d . score : , student. ( , .) , ' , student score.

. ' . , DELETE, ' . ', ' , ?

ϳ MySQL nnoDB. , . .
  • , .
  • , .
  • , ' . , . , NULL, , , ' . , , , . ', , NULL, .
nnoDB , . .

, ' :

FOREGN KEY [ndex_name] (ndex_columns)
REFERENCES tbl_name (ndex_columns)
[ON DELETE acton]
[ON UPDATE acton]
[MATCH FULL | MATCH PARTAL]

, , , , nnoDB . , ON UPDATE MATCH ( FOREGN KEY , .). .
  • FOREGN KEY , , . ' ndex_name .
  • REFERENCES , , . ndex_columns , , ndex_columns FOREGN KEY.
  • ON DELETE 䳿, . 䳿.
  • ON DELETE CASCADE . , . .
  • ON DELETE SET NULL , NULL.

, , . ( , , , , PRMARY KEY, .)

.
  • , , , . , , REFERENCES. ( , , , ' .) ֳ . nnoDB .
  • ³ . , NT CHAR. ³ . ³ SGNED UNSGNED.

, . parent chld, chld , par_d parent:

CREATE TABLE parent
(
par_d NT NOT NULL,
PRMARY KEY (par_d)
) TYPE = NNODB;
CREATE TABLE chld
(
par_d NT NOT NULL,
chld_d NT NOT NULL,
PRMARY KEY (par_d, chld_d),
FOREGN KEY (par_d) REFERENCES parent (par_d) ON DELETE CASCADE
) TYPE = NNODB;

ON DELETE CASCADE. , - parent par_d chld.

parent chld:

mysql> NSERT NTO parent (par_d) VALUES(1),(2) , (3) ;
mysql> NSERT NTO chld (par_d,chld_d) VALUES(1,1),(1,2);
mysql> NSERT NTO chld (par_d,chld_d) VALUES(2,1), (2,2), (2,3);
mysql> NSERT NTO chld (par_d,chld_d) VALUES(3,1);
, par_d chld par_d parent:
mysql> SELECT * FROM parent;
par_d
1
2
3

mysql> SELECT * FROM chld;
par_d chld_d
11
12
21
22
23
31

, nnoDB ' , chld , par_d parent:

mysql> NSERT NTO chld (par_d,hld_d) VALUES(4,1);
ERROR 1216: Cannot add a chld row: a foregn key constrant fals
1216: : .

, :

mysql> DELETE FROM parent where par_d = 1;
MySQL parent:

roysql> SELECT * FROM parent;
par_d
2
3

chld:

mysql> SELECT * FROM chld;
par_d chld_d
21
22
23
31

, . , , , , NULL. chld .
  • ON DELETE CASCADE ON DELETE SET NULL. nnoDB , (par_d), NULL.
  • par_d chld NOT NULL. , , ON DELETE SET NULL, NULL.
  • chld par_d PRMARY KEY. PRMARY KEY NULL. par_d , NULL, PRMARY KEY UNQUE. UNQUE nnoDB , , .

, , parent, , . ϳ chld :

CREATE TABLE chld
(
par_d NT NULL,
chld_d NT NOT NULL,
UNQUE (par_d, chld_d),
FOREGN KEY (par_d) REFERENCES parent (par_d) ON DELETE SET NULL
) TYPE = NNODB;

, par_d, , , ( . par_d NULL, , , ):

mysql> NSERT NTO chld (par_d,chld_d) VALUES(1,1), (1,2) ;
mysql> NSERT NTO chld (par_d,chld_d) VALUES(2,1), (2,2), (2,3) ; mysql> NSERT NTO chld (par_d,chld_d) VALUES(3,1);
mysql> NSERT NTO chld (par_d,chld_d) VALUES(4,1);
ERROR 1216: Cannot add a chld row: a foregn key constrant fals
1216: : .


³ . , chld:

mysql> DELETE FROM parent where par_d = 1;
mysql> SELECT * FROM chld;
par_d chld_d
NULL1
NULL2
21
22
23
31

, par_d , 1, . par_d , ON DELETE SET NULL.

г ' , . , , . ' .

MySQL:
- 3.23.44
ON DELETE CASCADE - 3.23.50
ON DELETE SET NULL - 3.23.50

, 3.23.50, . , , , 3.23.50 :
  • ALTER TABLE CREATE NDEX nnoDB, ' , . , .
  • SHOW CREATE TABLE . mysqldump, .




, ' , MySQL nnoDB ( , )?

, , . . , , score . , student. ³ , ' .

, , . , 13. , . student , , score:

DELETE FROM student WHERE student_d = 13

' . :

DELETE FROM student WHERE student_d =13;
DELETE FROM score WHERE student_d = 13;

' 4 , , . , . , , , :

DELETE student, score FROM student, score
WHERE student.student_d = 13 AND student.student_d =score.student_d;

, , , WHERE , , student. LEFT JON, student, - score:

DELETE student, score FROM student LEFT JON score USNG (student_d)
WHERE student.student_d=13;




  ':
  :
-Ѳ  Ѳ- . ³
-Ѳ Ѳ- . ³

APServer | SiMan CMS | ˳ | | | | ˳ | PHP Builder | Download | | Smarty | ϳ PHP | / | |