Add date format constraint to varchar attribut-CodePudding

I'm trying to create this table below in oracle but the i want to add a constraint to datexp (date format 'MM/YYYY') attribute but sadly none of my attempt worked correctly:

create table carte
(
    idcarte char(5) primary key,
    typec varchar(20) 
        check(typec in ('E-dinars smart', 'E-dinars universel', 'visa electron', 'visa international', 'mastercard international')),
    datexp varchar(9) ,
    numerocarte number(20),
    signaturecvv2 number(3)
);

I tried:

datexp varchar(9) check(to_date(datexp, 'MM/YYYY')),

datexp varchar(9) check(date_exp = to_date(datexp, 'MM/YYYY')),

CodePudding user response：

From my point of view, that would be terribly wrong. Never, EVER store dates as strings. NEVER. It is prone to errors, and you shouldn't waste your time on figuring out how to create constraints to prevent users from entering invalid dates - let database handle that. How? Simply by setting the DATE datatype for that column.

Besides, I'd suggest you to create referential integrity constraint for carte types:

SQL> CREATE TABLE carte_type
  2  (
  3     typec   NUMBER CONSTRAINT pk_cartyp PRIMARY KEY,
  4     name    VARCHAR2 (50) NOT NULL
  5  );

Table created.

SQL> CREATE TABLE carte
  2  (
  3     idcarte         CHAR (5) PRIMARY KEY,
  4     typec           NUMBER CONSTRAINT fk_cart_typ REFERENCES carte_type (typec),
  5     datexp          DATE,
  6     numerocarte     NUMBER (20),
  7     signaturecvv2   NUMBER (3)
  8  );

Table created.

SQL>

If you're worried about date format (mm/yyyy), don't worry about that - enter any date that belongs to that month/year, and then - when presenting that info to end users - apply desired format mask to to_char function, e.g.

select to_char(datexp, 'mm/yyyy') as datexp
from ...

It doesn't really matter whether you'll enter e.g. 08/12/2021 (dd/mm/yyyy) or 23/12/2021 - both of them are in 12/2021.

Or, if you're using GUI tool (or report builder), it already offers format mask property so - use it.

CodePudding user response：

You should store date values as a DATE and can use a check constraint to restrict it to values at the start of the month. You could even add a virtual column to generate DATEXP in the required format:

CREATE TABLE carte(
  idcarte       VARCHAR2(5)
                CONSTRAINT carte__idcarte__pk PRIMARY KEY,
  typec         VARCHAR2(20)
                CONSTRAINT carte__typec__chk CHECK(
                  typec in(
                    'E-dinars smart',
                    'E-dinars universel',
                    'visa electron',
                    'visa international',
                    'mastercard international'
                  )
                ),
  datexp        DATE
                CONSTRAINT carte__datexp__chk CHECK(datexp = TRUNC(datexp, 'MM')),
  formatted_datexp
                VARCHAR2(7)
                GENERATED ALWAYS AS (TO_CHAR(datexp, 'MM/YYYY')),
  numerocarte   NUMBER(20),
  signaturecvv2 NUMBER(3)
);

However, if you have a valid business reason to store it as a string (you probably don't, even if you think you do), from Oracle 12 you could try to convert to a date and use DEFAULT NULL ON CONVERSION ERROR in the check constraint with an exact format model:

CREATE TABLE carte(
  idcarte       VARCHAR2(5)
                CONSTRAINT carte__idcarte__pk PRIMARY KEY,
  typec         VARCHAR2(20)
                CONSTRAINT carte__typec__chk CHECK(
                  typec in(
                    'E-dinars smart',
                    'E-dinars universel',
                    'visa electron',
                    'visa international',
                    'mastercard international'
                  )
                ),
  datexp        VARCHAR2(7)
                CONSTRAINT carte__datexp__chk CHECK(
                  TO_DATE(datexp DEFAULT NULL ON CONVERSION ERROR, 'FXMM/YYYY')
                    IS NOT NULL
                ),
  numerocarte   NUMBER(20),
  signaturecvv2 NUMBER(3)
);

(Note: In earlier versions, you can drop the DEFAULT NULL ON CONVERSION ERROR and the constraint will still be checked but it will fail with an exception relating to parsing the date rather than a more descriptive error from the constraint failing.)

db<>fiddle here