3 Replies Latest reply: Aug 4, 2013 11:34 AM by Frank Kulash RSS

    Create constraint to enforce proper email domain

    8bb7968b-e6ae-456c-8459-f418352e9e0a

      Hi,

       

      I'm new to SQL, but am trying to help a friend build a database for his company he is trying to start (think of a mechanic and his home garage).

       

      I'm using Oracle 11g Express. Per the the below code, how do I build a check constraint that enforces that the value for 'Email' must be using the domain of his company? E.G.- *@billsgarage.com

       

      Thanks for your help!

       

       

      CREATE TABLE EMPLOYEE

      (EmpNo NUMBER(6) PRIMARY KEY,

      Title VARCHAR2(30) NOT NULL,

      Fname VARCHAR2(10) NOT NULL,

      Lname VARCHAR2(10) NOT NULL,

      Phone VARCHAR2(12) NOT NULL,

      Email VARCHAR2(40),

      HireDate DATE DEFAULT GETDATE(),

        • 1. Re: Create constraint to enforce proper email domain
          Smaipady

          Hi,

           

          In Oracle, to handle such requirements we have constraints. These constraints can either be added while creating the table or modifying the table structure.

           

          While creating table, constraint can be added as below. For example, I have created a table called as Test_Tab with two columns. And I added a constraint on the email column.

          1. A check constraint is added which checks whether the values coming into the table has @google.com pattern or not. If not then, such email id would not be inserted into the table.

          2. And this check is done through a function instr which checks a particular pattern and returns the position when found.

           

          create table Test_tab

          (

          name varchar2(30),

          email varchar2(30) constraint email_check check(instr(email,'@google.com')>0)

          )

          If you have already created the table, then you can use :

           

          Alter table test_Tab

          Add email constraint email_check check(instr(email,'@google.com')>0)

          Hope that is what you needed.

          • 2. Re: Create constraint to enforce proper email domain
            Ishan

            Smaipady,

             

            A minor additional handling would be required.

             

            User can enter email_id in UPPER, LOWER or MIXED case. I guess, it should be handled. Else, it would accept something like abc.google.com but not abc.GOOgle.com and likewise.

             

            Hence, the check would be more fine tuned as below

             

            create table Test_tab

            (

            name varchar2(30),

            email varchar2(30) constraint email_check check(instr(lower(email),'@google.com')>0)

            );

             

            Thanks,

            Ishan

            • 3. Re: Create constraint to enforce proper email domain
              Frank Kulash

              Hi,

               

              INSTR, as used above, tells if '@google.com' occurs anywhere in email.

              If you want to insist that it comes at the end of the string (i.e., if you don't allow 'foo@google.common.org') then one way is:

               

              email  VARCHAR2 (30)

                     CONSTRAINT email_check

                     CHECK (LOWER (email) LIKE '%_@google.com')