1

This is what I have now. I have done a check for DECIMAL() using BETWEEN AND. Was wondering if it is possible to do the same for VARCHAR()

CREATE TABLE STUDENT(
student_number      CHAR(4)         NOT NULL,
first_name          VARCHAR(50)     NULL,
last_name           VARCHAR(50)     NULL,
date_of_birth       DATE            NULL,
student_type        VARCHAR(20)     NULL,
CONSTRAINT student_pkey PRIMARY KEY (student_number),
CONSTRAINT student_check1 CHECK (student_type POSTGRADUDATE_STUDENT OR UNDERGRADUATE_STUDENT));
Faye Lynnette
  • 13
  • 6
  • 2
    Why not just use an ENUM column type? – Sloan Thrasher Apr 18 '18 at 05:47
  • 1
    Enum is a good suggestion here. Beyond that, MySQL does not enforece check constraints, so the constraint you wrote won't be used. The only other option would be to enforce using a trigger, but that's sort of ugly. I'd go with enum, which also has the benefit of reducing the amount of space needed to store the `student_type`. – Tim Biegeleisen Apr 18 '18 at 05:50
  • Oh, yes I could. Thank you! – Faye Lynnette Apr 18 '18 at 05:52
  • 1
    Note that ENUM will always permit an empty string. And it sorts by the internal code. Also consider switching to MariaDB, which supports CHECKs in the latest version. – Paul Spiegel Apr 18 '18 at 06:02
  • What are you mentioning DECIMAL & BETWEEN for? What does "the same" mean? Exactly what is it you can't find out about CHECK? PS This column is a "type tag" or type "variant tag". – philipxy Apr 18 '18 at 08:56
  • In response to philipxy, an example would be DECIMAL(2) and writing a CHECK constraint BETWEEN 1 AND 99. So, what I was trying to imply was having to check if it's either post-grad or under-grad by setting given values, like how I have given the range 1-99. – Faye Lynnette Apr 23 '18 at 15:47
  • Possible duplicate of [SQL Server "pseudo/synthetic" composite Id(key)](https://stackoverflow.com/questions/33149354/sql-server-pseudo-synthetic-composite-idkey) – philipxy Apr 30 '18 at 18:59
  • Hi. Please clarify via editing your post, not via comments. That is an anitpattern called [smart keys](https://stackoverflow.com/a/34082143/3404097). You have a case of [database/SQL subtypes/hierarchies/inheritance](https://stackoverflow.com/a/34082143/3404097). Always google many clear, concise & specific versions/phrasings of your question/problem/goal & read many answers. If you don't find an answer then post, using use one variant search for your title & keywords for your tags. PS Google 'stackexchange comment notifications' re "@". Note MySQL doesn't *enforce* CHECK constraints. – philipxy Apr 30 '18 at 19:00

0 Answers0