存储过程操作主外键

最新推荐文章于 2021-12-16 20:34:57 发布

weixin_30687811

最新推荐文章于 2021-12-16 20:34:57 发布

阅读量124

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/HansZimmer/p/8961521.html

版权

CREATE TABLE Dept--系/学院表
(
DeptId int identity(1,1) primary key,
DeptName nvarchar(50),--系名称
AddTime datetime default(getdate())
)

CREATE TABLE Grade--班级表
(
GradeId int identity(1,1) primary key,
DeptId int FOREIGN KEY REFERENCES Dept(DeptId),
GradeNum varchar(50) unique,--专业班编号
GradeName nvarchar(50),--专业名称
AddTime datetime default(getdate())
)

CREATE TABLE Student
(
StudentId int identity(1,1) primary key,
GradeId int FOREIGN KEY REFERENCES Grade(GradeId),
StudentName nvarchar(50),--学生姓名
SudentSex nvarchar(1) default('男'),
)

存储过程实现主外键约束的表数据

create proc DeleteDeptProc
(
@DeptId int,
@IsSuccess bit=0 output
)
as
begin
delete from [dbo].[Student] where GradeId in (select GradeId from [dbo].[Grade] where DeptId=@DeptId)
delete from [dbo].[Grade] where DeptId=@DeptId
delete from [dbo].[Dept] where DeptId=@DeptId
set @IsSuccess=1
end

--执行上述存储过程
DECLARE
@DeptId int,
@IsSuccess bit
SET @DeptId = 1;
exec DeleteDeptProc @DeptId,@IsSuccess

转载于:https://www.cnblogs.com/HansZimmer/p/8961521.html