0

I'm creating a college grading system with Java. I have a credit number column in courses table. I want this credit number to be limited between 1 to 8. I don't want any other number to be entered. I'm a beginner.

CREATE TABLE courses(
id INT PRIMARY KEY,
course_name VARCHAR(50) NOT NULL,
credit_number BETWEEN 1 AND 8 NOT NULL,
course_description VARCHAR(255));
E_net4
  • 27,810
  • 13
  • 101
  • 139
Kelise
  • 49
  • 4

2 Answers2

2

Use a check constraint:

CREATE TABLE courses (
    id INT PRIMARY KEY,
    course_name VARCHAR(50) NOT NULL,
    credit_number int NOT NULL,
    course_description VARCHAR(255),
    CONSTRAINT chk_courses_credit_number CHECK (credit_number BETWEEN 1 AND 8)
);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

Slight variation on the accepted answer -

You could also use TINYINT instead of INT, which would restrict the possible range to -128 to 127 (one byte), which is okay since you only need 1-8: https://dev.mysql.com/doc/refman/5.7/en/integer-types.html

CREATE TABLE courses (
    id INT PRIMARY KEY,
    course_name VARCHAR(50) NOT NULL,
    credit_number TINYINT NOT NULL,
    course_description VARCHAR(255),
    CONSTRAINT chk_courses_credit_number CHECK (credit_number BETWEEN 1 AND 8)
);
hbere
  • 306
  • 1
  • 5
  • 8