级联删除

student表(id主键)
id  name
001  王刚
002  李双
grade表(studentId为外键)
id,  studentId, score
1    001        60
2    002        70


现想求一SQL语句,删除student表id为001的信息,则同时删除子表相应数据. 用一条SQL语句能否实现

做项目的时候通常如何处理这样的问题.         SQL code

  
  

   
   
   
   

   
   ALTER
   
    
   
   TABLE
   
    
   
   [
   
   dbo
   
   ]
   
   .
   
   [
   
   student
   
   ]
   
    
   
   ADD
   
    
    
   
   CONSTRAINT
   
    
   
   [
   
   FK_grade_student
   
   ]
   
    
   
   FOREIGN
   
    
   
   KEY
   
    
    (
        
   
   [
   
   id
   
   ]
   
   
    ) 
   
   REFERENCES
   
    
   
   [
   
   dbo
   
   ]
   
   .
   
   [
   
   student
   
   ]
   
    (
        
   
   [
   
   id
   
   ]
   
   
    ) 
   
   ON
   
    
   
   DELETE
   
    
   
   CASCADE
   
     
   
   ON
   
    
   
   UPDATE
   
    
   
   CASCADE
   
    

   
   GO
   
   

  
  

你建表的时候建个关系嘛,那样代码都省了

 

 

 

 

也可用觸發器

SQL code

   
   

    
    
    
    create
    
     
    
    trigger
    
     del_student 
    
    on
    
     student
after 
    
    delete
    
    

    
    as
    
    

    
    delete
    
     grade 
    
    from
    
     studentId 
    
    in
    
    (
    
    select
    
     ID 
    
    from
    
     deleted)
   
   

推薦用級聯刪除