SQL Server主键的写法：

--列级create table dept(dept_no int primary key,dept_name nvarchar(20) not null)--表级create table dept(dept_no int not null,dept_name nvarchar(20) not null,primary key (dept_no))--修改create table dept(dept_no int not null,dept_name nvarchar(20) not null);alter table dept add primary key (dept_no)--添加个主键名字

alter table dept add constraint dept_no_pk primary key (dept_no)--测试insert into dept values(10,‘IT‘),(20,‘Finance‘),(30,‘Engineer‘);insert into dept values(10,‘IT2‘)

MySQL主键的写法：

--列级，同SQL Server一样create table dept(dept_no int primary key,dept_name varchar(20) not null)--表级,同SQL Server也一样create table dept(dept_no int ,dept_name varchar(20) not null,primary key(dept_no))--修改，同SQL Server也一样create table dept(dept_no int not null,dept_name varchar(20) not null);alter table add  primary key (dept_no);--另一种修改的方法，就是加个名字alter table add constraint dept_no_pk primary key(dept_no)

--测试

insert into dept values (10,‘IT‘),(20,‘Finance‘),(30,‘Engineer‘); 

insert into dept values(10,‘IT2‘)

为毛都是一样啊，难道是标准SQL？

SQL Server外键的写法：

--列级create table employee(employee_id int primary key,employee_name nvarchar(20) not null,dept_id int foreign key references dept(dept_no))--表级create table employee(employee_id int primary key,employee_name nvarchar(20) not null,dept_id int,constraint dept_no_fk foreign key(dept_id) references dept(dept_no))--修改create table employee(employee_id int primary key,employee_name nvarchar(20) not null,dept_id int);alter table employee add foreign key (dept_id) references dept(dept_no);alter table employee add constraint dept_no_fk foreign key (dept_id) references dept(dept_no)--测试insert into employee(1001,‘zhangsan‘,10);insert into employee(1002,‘lisi‘,50)

MySQL外键的写法：

--这下终于和SQL Server 不一样了,好像没有列级的了哦create table employee(employee_id int primary key,employee_name varchar(20) not null,dept_id int,foreign key (dept_id) references dept(dept_no))；create table employee(employee_id int primary key,employee_name varchar(20) not null,dept_id int,constraint dept_no_fk foreign key (dept_id) references dept(dept_no))--测试insert into employee values(1001,‘zhangsan‘,10);insert into employee values(1002,‘lisi‘,50)

其实主外键写法就是“constraint 主键名/外键名”这个的区别，我理解就是加了个别名。

删除主键和外键

SQL Server（好像必须有主外键别名的才行）：

create table employee(employee_id int,employee_name nvarchar(20) not null,dept_id int ,constraint employee_id_pk primary key(employee_id),constraint dept_no_fk foreign key(dept_id) references dept(dept_no));alter table employee drop constraint employee_id_pk;alter table employee drop constraint dept_no_fk;--测试insert into employee values(1001,‘zhangsan‘,10);insert into employee values(1003,‘wangwu‘,20);insert into employee values(1001,‘niuliu‘,30);insert into employee values(1002,‘lisi‘,50)；

MySQL:

create table employee(employee_id int primary key,employee_name varchar(20) not null,dept_id int ,constraint dept_no_fk foreign key (dept_id) references dept(dept_no));alter table employee drop primary key;alter table employee drop foreign key dept_no_fk;--测试语句insert into employee values(1001,‘zhangsan‘,10);insert into employee values(1003,‘wangwu‘,20);insert into employee values(1001,‘niuliu‘,30);insert into employee values(1002,‘lisi‘,50)；

单独测试下MySQL的check约束

create table employee(employee_id int primary key,employee_name varchar(20) not null,age int check (18<=age and age<=55),dept_id int,constraint dept_no_fk foreign key(dept_id) references dept(dept_no));--测试insert into employee values(1001,‘zhangsan‘10,10);insert into employee values(1002,‘lisi‘,29,10);

真的可以insert into 进去，使用show create table employee查看，果真没有check约束

在网上搜的：

“所有的存储引擎均对CHECK子句进行分析，但是忽略CHECK子句。”
The CHECK clause is parsed but ignored by all storage engines.