0
CREATE TABLE EMPLOYEE (
DISTRICT_ID                CHAR(5 BYTE)               NOT NULL,
EMP_ID                     CHAR(5 BYTE)               NOT NULL,
EMP_FNAME                  CHAR(50 BYTE)              NULL,
EMP_LNAME                  CHAR(50 BYTE)              NOT NULL,
ZIPCODE                    CHAR(5 BYTE)               NULL,
HIREDATE                   DATE               DEFAULT SYSDATE       NULL,
PREVIOUS_EXPERIENCE_YEARS  NUMBER(3,1)        DEFAULT 0             NULL,
HIGHEST_EARNED_DEGREE      CHAR(11 BYTE)      DEFAULT 'Bachelors'   NULL,
DIRECT_ADMIN_ID            CHAR(5 BYTE)       DEFAULT (null)        NULL,
IS_ADMIN                   CHAR(1 BYTE)       DEFAULT 'N'           NOT   NULL,
IS_TEACHER                 CHAR(1 BYTE)       DEFAULT 'Y'           NOT NULL,
EDU_EMAIL                  VARCHAR2(20 BYTE)          NOT NULL,
CONSTRAINT                   EMPLOYEE_PK        PRIMARY KEY(EMP_ID),
CONSTRAINT                   EMPLOYEE_FK      
FOREIGN KEY(DISTRICT_ID)
REFERENCES DISTRICT(DISTRICT_ID),
CONSTRAINT                   EMPLOYEE_FK1  
FOREIGN KEY(DIRECT_ADMIN_ID)
REFERENCES ADMIN(A_EMP_ID),
CONSTRAINT                   EMPLOYEE_UK1       UNIQUE(EDU_EMAIL),
CONSTRAINT                 HIGHEST_EARNED_DEGREEVALUES CHECK
                          (HIGHEST_EARNED_DEGREE IN ('GRE','High School',
                          'Associate','Bachelor','Master','Doctorate')),
CONSTRAINT                 HIREDATE CHECK ('HIREDATE'>='01-Jan-1950'),
CONSTRAINT                 EDU_EMAIL CHECK ('Anytext'>='2'),
CONSTRAINT                 EDU_EMAIL CHECK (SELECT = UPPER(EDU)) "Uppercase"
                                    FROM EDU_EMAIL
);

What I'm trying to do is to add a constraint to check for the appearance of a valid email address, the email just needs to be properly formatted. This constraint needs to check to make sure all the email addresses entered into EMPLOYEE.EDU_EMAIL are in the form [Anytext@[ANytext].edu. Wherever we see [Anytext] there must be at least 2 characters. The edu part must be edu but can be any capitalization pattern such as EDU or eDu and so on. The email should be accepted if it has two characters and any type of capitalization for .edu. Trying to use the UPPER() function in some sort of way to make this constraint. Also constraining EMPLOYEE.HIGHEST_EARNED_DEGREE to the values I put in, this should be correct I believe. And constraining EMPLOYEE.HIREDATE to be January 1, 1950 or later, rejecting impossible entries. Any help is appreciated. I get missing expression when I try to run this in Oracle.

Barry Robinholt
  • 19
  • 4
  • `HIREDATE CHECK ('HIREDATE'>='01-Jan-1950'),` - I have already told what is wrong with that in your previous question: http://stackoverflow.com/a/33636737/330315 Do you actually _read_ the answers you get? You are asking the same things over and over again. –  Nov 12 '15 at 06:56

1 Answers1

2

This check constraint doesn't make sense:

CONSTRAINT EDU_EMAIL CHECK (SELECT = UPPER(EDU)) "Uppercase" FROM EDU_EMAIL

You can't use a subquery in a check constraint. You can use regular expressions. Something like this:

CONSTRAINT EDU_MAIL CHECK (REGEXP_LIKE(LOWER(EDU_MAIL), '^[a-z0-9._%+-]+@[A-Za-z0-9.-]+\.edu$')

At least two other constraints are incorrect because you have used single quotes where they are not appropriate. Only use single quotes for string and date constants.

CONSTRAINT HIREDATE CHECK ('HIREDATE'>='01-Jan-1950'),
CONSTRAINT EDU_EMAIL CHECK ('Anytext'>='2'),

I don't know what the second one should be. The first should be:

CONSTRAINT HIREDATE CHECK (HIREDATE >= DATE '1950-01-01')
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786