SQL Server和MySQL主外键写法对比
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.
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。