Andrew J Winkler Andrew J Winkler - 9 months ago 18
SQL Question

How do I insert logical operators into SQL constraints?

Using SQL Server I'm trying to do something really simple, but the solution is escaping me.

My database is throwing an error upon creation. I really just wanted to check if the

customer.type
is
'Student'
that there is a
'@'
within there corresponding
customer.email
column.

Vice versa, if the
customer.type
is
'Faculty'
that their
customer.email
column ends with a
'@d.umn.edu'
.

I've spent about half an hour working on this, and I stupidly can't get it to work.

Create table Customer
(
CID int identity(1,1) primary key,
F_name char(25),
M_name char(25),
L_name char(25),
type char(7),
street varchar(50),
city varchar(25),
state char(2),
zip numeric(5),
password varchar(25) not null,
email varchar(25) UNIQUE Not null,

Constraint CK_Customer_type check (type in ('Student','Faculty')),
Constraint CK_Customer_email check((type='Student' AND email like '%@%') OR (type='Faculty' AND email like'%@d.umn.edu'))-- this is throwing an error
)

INSERT INTO Customer (F_name, M_name, L_name, type, street, city, state, zip, password, email)
VALUES
('Jarvis', 'Marvin', 'Vinton', 'Student', '3525 Metz Lane', 'Runnemede St.', 'NJ', 08078, 'vint0934@d.umn.edu', 'Kohque4Oo'),
('Olivia', 'Audrey', 'Keele', 'Faculty', '2850 Snowbird Ln.', 'Waco', 'NE', 68460, 'keel@d.umn.edu', 'Blackdiamond26')


Note: it does not need to be in one constraint. Thanks in advance.

Answer

You're missing a closing parenthesis.

Create table Customer(
        CID int identity(1,1) primary key,
        F_name char(25),
        M_name char(25),
        L_name char(25),
        type char(7),
        street varchar(50),
        city varchar(25),
        state char(2),
        zip numeric(5),
        password varchar(25) not null,
        email varchar(25) UNIQUE Not null,
    Constraint CK_Customer_type check (type in ('Student','Faculty')),
    Constraint CK_Customer_email check((type='Student' AND email like '%@%') OR (type='Faculty' AND email like'%@d.umn.edu'))-- this is throwing an error
) -- <<-- you are missing the closing parenthesis from create table Customer (

In your insert statement you have the password and email addresses reversed. I swapped them in the list of columns and it works like this:

INSERT INTO Customer (F_name, M_name, L_name, type, street, city, state, zip, email, password)
VALUES
('Jarvis', 'Marvin', 'Vinton', 'Student', '3525 Metz Lane', 'Runnemede St.', 'NJ', 08078, 'vint0934@d.umn.edu', 'Kohque4Oo'),
('Olivia', 'Audrey', 'Keele', 'Faculty', '2850 Snowbird Ln.', 'Waco', 'NE', 68460, 'keel@d.umn.edu', 'Blackdiamond26')

(Consider avoiding storing passwords in clear text)