Using a CHECK constraint to check for minimum age
164966Feb 15 2004 — edited Feb 16 2004Is it possible to use a check constraint in a table to check whether a person meets a minimum age? For example, if a person is registering as a member, I want to make sure the person is at least 18 years old (contract age), so that a minor doesn't try to register as an adult.
From what I understand, I can't use SYSDATE in a check statement. Is there another way? What about using CURRENT_DATE and FLOOR to form a statement for this check?
Here's the DDL for the table (table is not fully decomposed to keep it simple; "dob" represents "Date of Birth" and "zipcode" is a FK to this table):
CREATE TABLE member (
member_id VARCHAR2(10) NOT NULL,
fname VARCHAR2(30) NOT NULL,
lname VARCHAR2(30) NOT NULL,
address1 VARCHAR2(50),
address2 VARCHAR2(25),
zipcode NUMBER(5) NOT NULL,
phone VARCHAR2(15) NOT NULL,
dob DATE NOT NULL,
gender CHAR(1) NOT NULL,
CONSTRAINT ck_gender gender IN ('M','F'),
CONSTRAINT pk_member PRIMARY KEY (member_id),
CONSTRAINT fk_member_zipcode (zipcode) REFERENCES zipcode)
);
Thanks for your help.