如何在SQL约束中插入逻辑运算符？

Question

使用Server，我试图做一些非常简单的事情，但解决方案是逃避我。

我的数据库在创建时会抛出一个错误。我只是想检查一下，customer.type是否是'Student'，在对应的customer.email列中是否有一个'@'。

反之亦然，如果customer.type是'Faculty'，则它们的customer.email列以'@d.umn.edu'结尾。

我花了大约半个小时在这上面工作，但我愚蠢地不能让它开始工作。

    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')

注意:它不需要在一个约束中。提前谢谢。

Answer 1

你少了一个结尾的括号。

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 (

在insert语句中，密码和电子邮件地址颠倒。我在列列表中交换了它们，它的工作方式如下：

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')

(考虑避免以明文存储密码)

Answer 2

1. 正如前面在评论中所指出的，以及user212514，您错过了结束时的妄想。
2. 其次，在你的插入，电子邮件id是最后。根据您的值，电子邮件id被输入密码字段，密码被输入电子邮件id字段。

你的发言应该是：

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')   --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, 'keel@d.umn.edu', 'Blackdiamond26')

有关更多详细信息：insert.asp