how to add constraint on a boolean data type in myql

Question

so i make a database with mysql, with my database design i design 3 table (customer, product, order) and I need one of the table to have a boolean variable column. in that column I need to use constraint when the value is 0 so u cant add the product to the order table.

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");

so for the product_condition I need to have constraint if its false I cant add order for that specific product what I need to do

Note that 5.7 fails and version 8 silently converts to tinyint https://dbfiddle.uk/vFFLksqu — P.Salmon, Dec 24 '22 at 06:25

Justin Chien · Accepted Answer · 2022-12-24T10:10:32.847

I'm new to mysql and sorry if any of my opinion is wrong.

So what you want to do is to control whether the product is avaliable for INSERT on order_details by setting the product_condition_1ready_0mentah to 0\1.And I've come up with an idea to create a stored procedure to achive that.

Code below is the 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

And I've tested if it works,here's the result:

If you set it to 0, it wont insert

If you set it to 1, it will insert

Hope this will help,and any correction will be appreciated.

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Dec 25 '22 at 14:36

how to add constraint on a boolean data type in myql

1 Answers1