How do I insert logical operators into SQL constraints?

Question

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, '[email protected]', 'Kohque4Oo'),
('Olivia', 'Audrey', 'Keele', 'Faculty', '2850 Snowbird Ln.', 'Waco', 'NE', 68460, '[email protected]', 'Blackdiamond26')

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

What is the error? This worked in oracle, after closing paranthesis at the very end of the statement. Closed for Customer( — I_am_Batman
– I_am_Batman, Commented Apr 17, 2016 at 6:10
Worked in sqlserver too. Tried in sqlfiddle. Close the paranthesis.OR (type='Faculty' AND email like'%@d.umn.edu'))) — I_am_Batman
– I_am_Batman, Commented Apr 17, 2016 at 6:12
when I try inserting data I get the following error: The INSERT statement conflicted with the CHECK constraint "CK_Customer_email". The conflict occurred in database "ZZ16AndrewW", table "dbo.Customer". The statement has been terminated. — Andrew J Winkler
– Andrew J Winkler, Commented Apr 17, 2016 at 6:45

I_am_Batman · Accepted Answer · 2016-04-17 07:34:05Z

2

As pointed out earlier in comments, and by user212514, you had missed out the closing paranthesis.
Secondly, in your inserts, email id is last. As per your values, email id is being entered into password field, and password is being entered into email id field.

Your statements should be :

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, '[email protected]', 'Kohque4Oo')   --interchanged email and password.

INSERT INTO Customer (F_name, M_name, L_name, type, street, city, state, zip, email, password)
values
   ('Olivia', 'Audrey', 'Keele', 'Faculty', '2850 Snowbird Ln.', 'Waco', 'NE', 68460, '[email protected]', 'Blackdiamond26')

For more details : http://www.w3schools.com/sql/sql_insert.asp

edited Apr 17, 2016 at 7:34

answered Apr 17, 2016 at 7:14

I_am_Batman

9159 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user212514 · Accepted Answer · 2016-04-17 07:15:17Z

1

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, '[email protected]', 'Kohque4Oo'),
('Olivia', 'Audrey', 'Keele', 'Faculty', '2850 Snowbird Ln.', 'Waco', 'NE', 68460, '[email protected]', 'Blackdiamond26')

(Consider avoiding storing passwords in clear text)

edited Apr 17, 2016 at 7:15

answered Apr 17, 2016 at 6:33

user212514

3,1381 gold badge17 silver badges11 bronze badges

4 Comments

Andrew J Winkler Over a year ago

The missing parenthesis was a typo. Sorry. It's still throwing an error.

user212514 Over a year ago

Are you able to create a SQL Fiddle example. I created a working example here: sqlfiddle.com/#!3/12b7b

Andrew J Winkler Over a year ago

I tried using the MS SQL Server 2014 option on SQL fiddle. My create customer block works, but when I try to insert data into it I get an error. I have just added my insert block testing both student and faculty types to the original question. thanks

Andrew J Winkler Over a year ago

THANK YOU. I will likely be hashing passwords or something of the sort. but at this point we're just creating a prototype.

Collectives™ on Stack Overflow

How do I insert logical operators into SQL constraints?

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related