Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to prevent decimal values entering in db?

— 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 insertion should generate an error because the pricePerNight is a decimal number

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

I should get error when inserting decimal values in pricePerNight column like 100.87
I also tried CHECK constraint like this
pricePerNight BIGINT(10) NOT NULL CHECK (pricePerNight >= 0 AND pricePerNight % 1 = 0)
but it still gets inserted in db.

Tags:

2

Answers


  1. 0

    If you want to insert rounded value instead of entered one then modify your query to

    INSERT INTO Cabin (cabinType, cabinDescription, pricePerNight, pricePerWeek, photo)
VALUES (?, ?, ROUND(?), ?, ?);

    After parameters substitution this will be

    INSERT INTO Cabin (cabinType, cabinDescription, pricePerNight, pricePerWeek, photo)
VALUES ('test cabin 2', 'test cabin with invalid data', ROUND(100.87), 625, 'insertCabin1.jpg');

    The function will return 101 instead of entered 100.87, and the error won’t occur.

    Login or Signup to reply.
  2. 0 
    CREATE TABLE IF NOT EXISTS Cabin (
cabinID BIGINT AUTO_INCREMENT PRIMARY KEY,
cabinType VARCHAR(150) NOT NULL,
cabinDescription VARCHAR(255),
pricePerNight DECIMAL(10,2) NOT NULL CHECK (pricePerNight=ROUND(pricePerNight)),

pricePerWeek DECIMAL(10,2) NOT NULL,
photo VARCHAR(50)
);

-- valid data, because 101 = ROUND(101)
INSERT INTO Cabin (cabinType, cabinDescription, pricePerNight, pricePerWeek, photo)
VALUES ('test cabin 1', 'test cabin with valid data', 101, 625, 'insertCabin1.jpg');

-- invalid data, because 100.87 <> ROUND(100.87)
INSERT INTO Cabin (cabinType, cabinDescription, pricePerNight, pricePerWeek, photo)
VALUES ('test cabin 2', 'test cabin with invalid data', 100.87, 625, 'insertCabin2.jpg');

    I wouldn’t think BIGINT is needed for the price per night. That cabin must be very posh!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search