CODEWITHSUNDEEP
sqlsql-serverdatabasedatabase-design
Andrew J Winkler
Andrew J Winkler

Reputation: 465

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, '[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.

Upvotes: 0

Views: 648

Answers (2)

I_am_Batman
I_am_Batman

Reputation: 915

  1. As pointed out earlier in comments, and by user212514, you had missed out the closing paranthesis.

  2. 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

Upvotes: 2

user212514
user212514

Reputation: 3130

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)

Upvotes: 1

Related Questions