如何在mysql中为布尔数据类型添加约束

Question

所以我做一个数据库mysql，与我的数据库设计我设计3表(客户，产品，订单)，我需要一个表有一个布尔变量列。在该列中，当值为0时，我需要使用约束，这样就不能将产品添加到订单表中。

drop database trial;
create database trial;
use trial;
create table customer_details(
customer_id int not null,
customer_name varchar(100) not null,
customer_contact varchar(100) not null,
customer_address varchar(100) not null,
primary key(customer_id)
);
create table product_details(
product_id int not null,
product_name varchar(100) not null,
product_finishing varchar(100) not null,
product_price varchar(100) not null,
product_stock int check(product_stock >=0) not null,
product_condition_1ready_0mentah boolean not null check(1 >= product_condition_1ready_0mentah AND product_condition_1ready_0mentah >=0),
primary key(product_id,product_stock)
);

create table order_details(
order_id varchar(50) not null,
order_quantity int not null check(order_quantity > 0),
customer_id int not null,
product_id int not null,
order_date date not null,
order_desc varchar(255),
primary key(order_id),
FOREIGN KEY (customer_id) REFERENCES customer_details(customer_id),
FOREIGN KEY (product_id) REFERENCES product_details(product_id)
);
insert into product_details(product_id, product_name, product_finishing, product_price, product_stock, product_condition_1ready_0mentah)
values
(1, "meja", "ash", "Rp 10000", 10, true),
(2, "kursi", "redwood", "Rp 20000", 20, true),
(3, "panel", "maple", "Rp 300000", 20, false);
insert into customer_details(customer_id, customer_name, customer_contact, customer_address)
values
(1, "Test Meubel", "08987654321", "Solo");
insert into order_details(order_id, order_quantity, customer_id, product_id, order_date, order_desc)
values
("12345", 10, 1, 1, "2020-12-20", "Bla Bla Bla"),
("54321", 5, 1, 2, curdate(), "Notes bisa dimasukin sini");

对于product_condition，我需要有约束，如果它为假，我就不能添加特定产品的订单我需要做什么

Answer 1

我是mysql的新手，如果我的观点是错误的，我很抱歉。

因此，您要做的是通过将product_condition_1ready_0mentah设置为01来控制产品是否可用于order_details上的INSERT。我想到了一个想法，创建一个存储过程来实现这一点。

下面的代码是SP:

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_order_details`(p_order_id varchar(50), 
p_order_quantity int,
p_customer_id int,
p_product_id int,
p_order_date date,
p_order_desc varchar(255))
BEGIN
if (select product_condition_1ready_0mentah from product_details where product_id=p_product_id) = 0 then
select 'Not inserted, the product you choose is not avaliable!';
else 
insert into order_details(order_id, order_quantity, customer_id, product_id, order_date, order_desc) values
(p_order_id, p_order_quantity, p_customer_id, p_product_id, p_order_date, p_order_desc);
end if;
END

我已经测试了它是否有效，结果如下:

如果你设置为0，它不会插入

如果设置为1，则插入

希望这对你有帮助，如有任何更正，我将不胜感激。