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.
以上就是SQL Server和MySQL主外键写法对比的详细内容,更多关于SQL Server和MySQL主外键写法对比的资料请关注九品源码其它相关文章!