Forum Stats

  • 3,768,176 Users
  • 2,252,755 Discussions
  • 7,874,483 Comments

Discussions

DateAdd Function - Age restriction of a student based on birth_date

User_LLY1O
User_LLY1O Member Posts: 1 Green Ribbon
edited Jun 3, 2021 6:47PM in SQL & PL/SQL

Hi, I am working on my seminar thesis and I cannot get through with this problem: I have a table of students with a column birth_date and I only want to allow stdents UNDER 18 years old to be in my database. I want to use DateAdd function to do this or a trigger. Can you help me with how the CONSTRAINT should look like? I have come up with this but it doesn't seem to be working ("Missing expression" error returns).


CREATE TABLE student (

  id_student   INTEGER NOT NULL,

  name VARCHAR2(50) NOT NULL,

  birth_date  DATE NOT NULL

);


ALTER TABLE student

  ADD CONSTRAINT CheckAge CHECK (birth_date > DATEADD(date(), 'DD.MM.YYYY', 0, 0, -18)) ;


This should be added into the table:

INSERT INTO student (id_student, name, birth_date) VALUES ('1', 'John', '01.01.2012');


This should not be added into the table:

INSERT INTO student (id_student, name, birth_date) VALUES ('1', 'John', '01.01.2000');


Thanks a lot in advance for any help! (I am a beginner so although this may be trivial to you, it is not for me.)

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,205 Red Diamond

    Hi, @User_LLY10

    Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data. In this case, include some INSERT statements that should work, and some that should fail. The desired results will be the contents of the table when all the successful INSERT statements have been run.

    Always say which version of Oracle you're using (e.g. 12.2.0.1.0).


     I have come up with this but it doesn't seem to be working ("Missing expression" error returns).


    ALTER TABLE student

      ADD CONSTRAINT CheckAge CHECK (birth_date > DATEADD(date(), 'DD.MM.YYYY', 0, 0, -18)) ;

    There is no DATEADD or date () functions in Oracle. To see if birth_date is less than 18 years ago, use

    birth_date > ADD_MONTHS (SYSDATE, -18 * 12)
    

    However, you can't call SYSDATE in a CHECK constraint. You could write a trigger to do what you want.

  • User_H3J7U
    User_H3J7U Member Posts: 640 Silver Trophy

    @Frank Kulash

    You could write a trigger to do what you want.

    Trigger is not sufficient. Tomorrow somebody will violate "I only want to allow stdents UNDER 18 years old to be in my database".

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,205 Red Diamond

    Hi, @User_H3J7U

    Trigger is not sufficient. 

    No, a trigger by itself is never sufficient for anything. You always need to do something in the trigger.

    For example, if you want to make sure that rows are deleted by the person's 18th birthday, then the trigger can use dbms_scheduler to create a job that will run on the right date and delete the row.

  • mathguy
    mathguy Member Posts: 10,151 Blue Diamond
    edited Jun 4, 2021 3:33AM

    You are writing a seminar thesis having to do with Oracle SQL, and you think there are functions like DATE() and DATEADD()?Are you sure you use Oracle SQL in your class, and not some other SQL dialect? Strange either way...

    Of course, if you are going to use the scheduler to check for you daily, you wouldn't need a trigger. (I don't even see how you would.) The job would simply delete rows for the persons who turn 18 on that day, using a simple delete statement.

    But you mentioned "constraint" explicitly in your question, and you deserve an answer to that. The simple answer, in Oracle, is that constraints can't depend on non-deterministic things, such as the "current date". It's as simple as that. The reason is obvious: suppose you could have a constraint like that, and you would add someone who is almost 18 years old. No problem adding him or her now; but in two weeks he or she WILL be 18 years old, and the data would still be in the table. Is that OK? The constraint won't be able to do anything for you, since the data is not modified in any way; only the "current date" has changed, but that is not part of the database, it's part of the real world. To avoid such hazardous behavior, Oracle just doesn't allow such constraints.

    It is also odd that you would want to delete data from your db - assuming you were even thinking about that; perhaps you weren't, but one way or another you have to deal with already existing rows when persons turn 18, too. Will you never need it, for example for auditing purposes, etc.? The much more reasonable approach would be to write a trivial view against the table, filtering in only the persons who are not 18 years old yet. (A view, unlike a constraint, has no restriction on the use of sysdate.) Then do anything you need to do with that data, referencing the view instead of the base table. If filtering the data turns out to take too long, you can materialize the view; you would only need to refresh it once per day (presumably at night, and presumably a very fast refresh anyway). Or, if for some reason you really want to delete data from the table, it would make more sense to keep a "historical data" table and "move" those rows to the archive, instead of simply deleting them and losing all the information for good.