Webapp -应该在数据库或应用程序或两者中检查约束

Question

简化上下文:

假设我的数据库中有2个表:Room(id, maxContract)和Contract(id, roomid, status)

假设我有一个房间17，允许max2客户端，现在我将搜索Contract表的roomid = 17和status = active行，如果超过max(在这种情况下为2)行，我会阻止进一步的INSERT，直到合同到期。

问题:

现在我看到了两种方法，首先是在数据库本身，也许是在TRIGGER上，第二个是在我的webapp DAO中这样做:查询Contract表以获取计数，if-else检查逻辑，如果true

只运行insert

但我只是一个新手，我不知道什么是最好的(或常见的)方法，我应该怎么做?如果这是我的个人应用程序，我会为了最大的安全性而做这两件事，但设计一个网页我也必须考虑性能。

在前端到后端的情况下，我知道验证在后端是强制性的，在前端是可选的，但在后端到数据库之间，我不知道确切的

(如果这是基于意见的，并且没有最佳实践，我想知道这两种实现的优缺点)

编辑:更确切地说:用户点击JOIN ROOM =>调用insertToRoom()方法

+解决方案1:

insertToRoom(){
if (roomIsAvailable()){
execute INSERT query;
}
else alert: "room is full";
}

roomIsAvailable()是查询和计算有多少合同绑定到房间的方法

+解决方案2:

insertToRoom(){
execute INSERT query;
}

数据库:

CREATE TRIGGER before INSERT
if (some code to count the rooms)
ROLLBACK TRANSACTION;

在本例中，如果加入一个不可用的房间，数据库将返回错误，这反过来导致应用程序中的execute INSERT query返回false。

无论哪种方式，都不会插入错误数据，最终用户将获得错误警报

Answer 1

我希望不止一个用户可以使用您的应用程序。

如果您将从DB到应用程序的当前状态，评估那里的条件，然后将插入操作运行到DB表中，则可能违反条件。让我们想象一下这个步骤序列:

1. 用户从DB中读取当前状态
用户B
2. 从DB中读取当前状态
用户一个
3. 对条件进行评估，结果为"有一个客户端的空间"。
用户B
4. 对条件进行评估，结果为"有一个客户端的空间"。
用户一个
5. update DB ->
用户B
6. update DB ->

所以这不是一个选项。

您可以使用触发器(如您所提到的)，如果您的数据库有这样的可能性。

您也可以创建一个SQL语句，它将检查条件并在一步(自动)中更新DB中的记录。这取决于你的数据库引擎，是否可能。

更新2022-05-27

我被要求详细解释原子插入解决方案。

我不是MySQL专家，我很确定，有更优雅的方法，如何做到这一点，但像这样的东西也可以工作:

让我们先创建所需的DB对象:

create table Room(
id INT,
maxContract INT);

create table Contract(
id INT AUTO_INCREMENT,
roomid INT,
status VARCHAR(30),
PRIMARY KEY(id)
);
create view UsedSpace as
select r.id as roomid, count(c.id) as used
from Room r
left outer join Contract c on c.roomid = r.id and status='active'
group by r.id;

然后我们可以使用下面的语句向Contract表中插入新行:

insert into Contract(roomid, status)
select r.id, 'active'
from Room r
inner join UsedSpace us on r.id = us.roomid and r.maxContract > us.used
where id = 17;

活动契约过多时，不插入新行

可以通过

检查行是否插入

select row_count();

这是一个快速显示结果的小提琴。