我看到了一些关于如何使用sql server使用复制函数运行检查的文档。看起来MY SQL没有复制函数,但它们有一个类似的函数,叫做repeat。但是,当我尝试实现检查约束时,它会出错。
create table Customer_Dim(
customer_ID int not null,
FirstName varchar(30) not null,
LastName varchar(30) not null,
Gender char(1) not null CHECK (gender in ('m','f')),
Street1 varchar(100) not null,
Street2 varchar(100) not null,
City varchar(30) not null,
StateAbbrev char(2) not null,
ZipCode char(5) not null,
PrimaryPhone varchar(10) not null,
EmailAddress varchar(50) default null,
constraint custkey Primary Key(customer_ID),
CONSTRAINT zipchk CHECK (ZipCode LIKE repeat('[0-9]',5));
我得到错误
错误代码:1064。您的SQL语法有错误;请查看与MySQL服务器版本相对应的手册,以获得在第14行"附近使用的正确语法,但红色x位于约束行旁边。
Like函数起作用了,只是最后遗漏了一些语法。
create table Customer_Dim(
customer_ID int not null,
FirstName varchar(30) not null,
LastName varchar(30) not null,
Gender char(1) not null CHECK (gender in ('m','f')),
Street1 varchar(100) not null,
Street2 varchar(100) not null,
City varchar(30) not null,
StateAbbrev char(2) not null,
ZipCode char(5) not null CHECK (ZipCode LIKE repeat('[0-9]',5)),
PrimaryPhone varchar(10) not null CHECK (PrimaryPhone LIKE repeat('[0-9]',10)),
EmailAddress varchar(50) default null,
constraint custkey Primary Key(customer_ID)
); <----