How to prevent decimal values entering in db?

1

-- Table: Cabin
CREATE TABLE IF NOT EXISTS Cabin (
cabinID BIGINT AUTO_INCREMENT PRIMARY KEY,
cabinType VARCHAR(150) NOT NULL,
cabinDescription VARCHAR(255),
pricePerNight BIGINT NOT NULL,
pricePerWeek DECIMAL(10,2) NOT NULL,
photo VARCHAR(50)
);

INSERT INTO Cabin (cabinType, cabinDescription, pricePerNight, pricePerWeek, photo)
VALUES ('test cabin 2', 'test cabin with invalid data', 100.87, 625, 'insertCabin1.jpg');
-- This should generate an error because the pricePerNight is a decimal number

I am using myphpadmin and it is successfully entered into the database with rounded figure 101.
so how can i modify to prevent decimal values entering into database?
Please guide, thanks in advance.

2

CEILING ( pricePerNight )

3

I simply want to reject the insertion if it contains decimals. not round figure.

4

A CHECK constraint can achieve that (MySQL 8 anyway):

create temporary table if not exists dec_test(val decimal(10,2) not null
constraint no_decimal check(mod(val,1)=0) ENFORCED);
insert into dec_test(val) values(12);
insert into dec_test(val) values(1.2); /* fails */

This is assuming you can't change the column data type, or if doing so would round decimal values.

FYI SQLTeam.com is a Microsoft SQL Server site, we're not experts in MySQL.

5

hi

You can put a WHERE clause to filter out such records before inserting

6

This is off topic, I know, but outside of Stack Overflow, where do people go for MySQL help? I mean, surely there is a user community, somewhere, but I can't find it.

7

I don't know. There was the old dbforum.com that was pretty good, covered most of the database products. StackOverflow is generally pretty good. I would think there's a subreddit focused on MySQL that's well curated, just need to dig for it.

It's not my intention to keep people from seeking MySQL help here, but to set expectations properly.