Customer email address

Question

I am new to sql programming. This is for a homework assignment for my database class. I have made all the table I need for the assignment I am just hung up one part. The line in the homework reads as follows:

If type is 'faculty' the email address must end up with '@xxx.edu'

This is what the table looks like:

create table customer
(
CID# char(10) primary key IDENTITY (1,1) NOT NULL,
F_name varchar(20),
M_name varchar(20),
L_name varchar(20),
type varchar(20),
street varchar(20),
city varchar(20),
state varchar(20),
zip char(5),
password varchar (20) NOT NULL,
email varchar(20) NOT NULL
Constraint CK_customer_type check (type in ('student', 'faculty'))
)

Any help someone could lend would be greatly appreciated!

I think the question is: how to put the constraint for e-mail address column? — Alexei - check Codidact, Feb 28 '16 at 20:46

TT. · Answer 1 · 2016-02-28T20:37:52.937

This constraint would check if the email column ends with @xxx.edu.

Constraint CK_email_faculty check (
   type<>'faculty' OR 
   CHARINDEX('@xxx.edu',email)=LEN(email)-LEN('@xxx.edu')+1
)

Remark 1: Better than checking the type being appropriate in a CHECK constraint, make a table customer_type with possible types ('student', 'faculty' ...), and have a foreign key in customer pointing a the type.

CREATE TABLE customer_type(id INT NOT NULL PRIMARY KEY,desc VARCHAR(128));
INSERT INTO customer_type(id,desc)VALUES(1,'student');
INSERT INTO customer_type(id,desc)VALUES(2,'faculty');

Have a foreign key in your customer table to point to the customer_type table:

CREATE TABLE customer(
    -- ...
    type INT NOT NULL,
    -- ...
    CONSTRAINT FK_type_customer_type FOREIGN KEY(type) REFERENCES customer_type(id)
)

You would then not insert the type description but the type identifier:

INSERT INTO customer(...,type,...)VALUES(...,1,...); -- for student
INSERT INTO customer(...,type,...)VALUES(...,3,...); -- fails, type doesn't exist

That way you save disk space, and memory when these tables are cached by SQL Server.

Remark 2: The widths of your varchar fields are very small. An email address of only 20 characters?

score 1 · Answer 2 · edited May 23 '17 at 10:28

For your particular case a constraint like this might be ok:

Constraint CK_customer_email check (
   type <> 'faculty' OR
   email LIKE '%_%@_%.edu'  
   CHARINDEX('@xxx.edu',email)=LEN(email)-LEN('@xxx.edu')+1
)

This will allow 1+ characters, followed by @, followed by 1+ characters, followed by .edu.

However, in real life (where mean people try to insert bad e-mail addresses), validation is more complex (not all characters are allowed), so a custom function can be used. One that seems to be almost complete is provided here:

CREATE FUNCTION [dbo].[fnAppEmailCheck](@email VARCHAR(255))   
--Returns true if the string is a valid email address.  
RETURNS bit  
as  
BEGIN  
     DECLARE @valid bit  
     IF @email IS NOT NULL   
          SET @email = LOWER(@email)  
          SET @valid = 0  
          IF @email like '[a-z,0-9,_,-]%@[a-z,0-9,_,-]%.[a-z][a-z]%'  
             AND LEN(@email) = LEN(dbo.fnAppStripNonEmail(@email))  
             AND @email NOT like '%@%@%'  
             AND CHARINDEX('.@',@email) = 0  
             AND CHARINDEX('..',@email) = 0  
             AND CHARINDEX(',',@email) = 0  
             AND RIGHT(@email,1) between 'a' AND 'z'  
               SET @valid=1  
     RETURN @valid  
END

Then, your constraint would be like this:

Constraint CK_customer_email check (
   type <> 'faculty' OR
   [dbo].[fnAppEmailCheck] (email) = 1
)

score 0 · Answer 3 · answered Feb 28 '16 at 20:40

0

Add Constraint

Constraint CK_email check (email like case when type in ('faculty') then '%@xxx.edu' else email end )

answered Feb 28 '16 at 20:40

DimaSUN

921
8
13

Customer email address

3 Answers3