Member Avatar for hwoarang69

below, I have create a table using oracle sql.

How can I change so that number of credits for a course is always between 0 and 6. Any idea how can i do this?

CREATE TABLE COURSES
  (COURSE VARCHAR(6),
   DESCRIPTION VARCHAR(60),
   CREDITS INT,
   CONSTRAINT pk PRIMARY KEY (COURSE));
Edited by hwoarang69
  • 2 Contributors
  • 5 Replies
  • 240 Views
  • 4 Hours Discussion Span
  • Latest Post Latest Post by Member #120589

Dani AI

Generated

Short answer: enforce the rule in the database with a CHECK constraint (or, if you prefer, a tiny lookup table + FK). A column type only defines storage; it won't limit business values to 0–6 by itself. , since you run plain INSERT statements you should put the rule on the table so any client or script is blocked when it tries to insert an invalid credit value. ’s idea of validating outside the database works, but a DB-level constraint is the most reliable place for this rule.

Oracle (example)

CREATE TABLE COURSES (
  COURSE      VARCHAR2(6),
  DESCRIPTION VARCHAR2(60),
  CREDITS     NUMBER(1) NOT NULL,
  CONSTRAINT pk_courses PRIMARY KEY (COURSE),
  CONSTRAINT chk_credits CHECK (CREDITS BETWEEN 0 AND 6)
);

MySQL (example)

CREATE TABLE courses (
  course      VARCHAR(6),
  description VARCHAR(60),
  credits     TINYINT UNSIGNED NOT NULL,
  CONSTRAINT pk_courses PRIMARY KEY (course),
  CONSTRAINT chk_credits CHECK (credits BETWEEN 0 AND 6)
);

If you already have the table, add the rule with:

ALTER TABLE COURSES
ADD CONSTRAINT chk_credits CHECK (CREDITS BETWEEN 0 AND 6);

Notes and troubleshooting:

  • CHECK enforces the business range; it’s separate from the numeric type. Using NUMBER(1) or TINYINT only affects storage, not the business rule.
  • If your MySQL build does not enforce CHECK, use a small lookup table of allowed values (0..6) and a foreign key, or add a trigger or application check. That lookup-table approach (a table containing 0–6 and an FK from COURSES) is the portable, DB-enforced alternative that suggested.
  • To find bad rows before adding a constraint: SELECT * FROM COURSES WHERE CREDITS NOT BETWEEN 0 AND 6;

Recommended Answers

All 5 Replies

Member Avatar for Member #120589

I would imagine that data validation could be done with server-side code. I don't use Oracle myself, but I'm assuming that the datatype that you need is TT_TINYINT (0 to 255). I've seen some people mention ENUM, but IMO this is not appropriate for simple integers.

Are you using any server-side language?

Member Avatar for hwoarang69

but cant you enter 7 in TT_TINYINT? i need to do it so range is from 0 to 6. I am using oracle mysql.

Member Avatar for Member #120589

Read my post. I said it accepts 0 to 255. So yes, it can. There is no specific datatype that I know of for what you're asking. Are you able to validate server-side? You don't give any info wrt how you're insering data

Edited by Member #120589
Member Avatar for hwoarang69

i am just inserting data by using mysql(Oacle) insert statments.

Edited by hwoarang69
Member Avatar for Member #120589

Ah, ok, in that case, I don't know. You could try to use an IF function maybe (??) but that means you'd have to implement it in the actual SQL query which is probably not what you want. Perhaps you could use a related table using a constraint - make the credits field an FK of a credits table PK:

credits table
PK 0-6
Other fields??

So, beyond this I'm not much help - sorry.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.