SQL Server和MySQL主外键写法对比

数据库   发布日期:2025年06月16日   浏览次数:158

SQL Server主键的写法:

  1. --列级
  2. create table dept
  3. (
  4. dept_no int primary key,
  5. dept_name nvarchar(20) not null
  6. )
  7. --表级
  8. create table dept
  9. (
  10. dept_no int not null,
  11. dept_name nvarchar(20) not null,
  12. primary key (dept_no)
  13. )
  14. --修改
  15. create table dept
  16. (
  17. dept_no int not null,
  18. dept_name nvarchar(20) not null
  19. );
  20. alter table dept add primary key (dept_no)
  21. --添加个主键名字
  1. alter table dept add constraint dept_no_pk primary key (dept_no)
  2. --测试
  3. insert into dept values
  4. (10,'IT'),(20,'Finance'),(30,'Engineer');
  5. insert into dept values(10,'IT2')

MySQL主键的写法:

  1. --列级,同SQL Server一样
  2. create table dept
  3. (
  4. dept_no int primary key,
  5. dept_name varchar(20) not null
  6. )
  7. --表级,同SQL Server也一样
  8. create table dept
  9. (
  10. dept_no int ,
  11. dept_name varchar(20) not null,
  12. primary key(dept_no)
  13. )
  14. --修改,同SQL Server也一样
  15. create table dept
  16. (
  17. dept_no int not null,
  18. dept_name varchar(20) not null
  19. );
  20. alter table add primary key (dept_no);
  21. --另一种修改的方法,就是加个名字
  22. alter table add constraint dept_no_pk primary key(dept_no)
  1. --测试
  1. insert into dept values (10,'IT'),(20,'Finance'),(30,'Engineer');
  1. insert into dept values(10,'IT2')

为毛都是一样啊,难道是标准SQL?

SQL Server外键的写法:

  1. --列级
  2. create table employee
  3. (
  4. employee_id int primary key,
  5. employee_name nvarchar(20) not null,
  6. dept_id int foreign key references dept(dept_no)
  7. )
  8. --表级
  9. create table employee
  10. (
  11. employee_id int primary key,
  12. employee_name nvarchar(20) not null,
  13. dept_id int,
  14. constraint dept_no_fk foreign key(dept_id) references dept(dept_no)
  15. )
  16. --修改
  17. create table employee
  18. (
  19. employee_id int primary key,
  20. employee_name nvarchar(20) not null,
  21. dept_id int
  22. );
  23. alter table employee add foreign key (dept_id) references dept(dept_no);
  24. alter table employee add constraint dept_no_fk foreign key (dept_id) references dept(dept_no)
  25. --测试
  26. insert into employee(1001,'zhangsan',10);
  27. insert into employee(1002,'lisi',50)

MySQL外键的写法:

  1. --这下终于和SQL Server 不一样了,好像没有列级的了哦
  2. create table employee
  3. (
  4. employee_id int primary key,
  5. employee_name varchar(20) not null,
  6. dept_id int,
  7. foreign key (dept_id) references dept(dept_no)
  8. );
  9. create table employee
  10. (
  11. employee_id int primary key,
  12. employee_name varchar(20) not null,
  13. dept_id int,
  14. constraint dept_no_fk foreign key (dept_id) references dept(dept_no)
  15. )
  16. --测试
  17. insert into employee values(1001,'zhangsan',10);
  18. insert into employee values(1002,'lisi',50)

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

删除主键和外键

SQL Server(好像必须有主外键别名的才行):

  1. create table employee
  2. (
  3. employee_id int,
  4. employee_name nvarchar(20) not null,
  5. dept_id int ,
  6. constraint employee_id_pk primary key(employee_id),
  7. constraint dept_no_fk foreign key(dept_id) references dept(dept_no)
  8. );
  9. alter table employee drop constraint employee_id_pk;
  10. alter table employee drop constraint dept_no_fk;
  11. --测试
  12. insert into employee values(1001,'zhangsan',10);
  13. insert into employee values(1003,'wangwu',20);
  14. insert into employee values(1001,'niuliu',30);
  15. insert into employee values(1002,'lisi',50);

MySQL:

  1. create table employee
  2. (
  3. employee_id int primary key,
  4. employee_name varchar(20) not null,
  5. dept_id int ,
  6. constraint dept_no_fk foreign key (dept_id) references dept(dept_no)
  7. );
  8. alter table employee drop primary key;
  9. alter table employee drop foreign key dept_no_fk;
  10. --测试语句
  11. insert into employee values(1001,'zhangsan',10);
  12. insert into employee values(1003,'wangwu',20);
  13. insert into employee values(1001,'niuliu',30);
  14. insert into employee values(1002,'lisi',50);

 

单独测试下MySQL的check约束

  1. create table employee
  2. (
  3. employee_id int primary key,
  4. employee_name varchar(20) not null,
  5. age int check (18<=age and age<=55),
  6. dept_id int,
  7. constraint dept_no_fk foreign key(dept_id) references dept(dept_no)
  8. );
  9. --测试
  10. insert into employee values(1001,'zhangsan'10,10);
  11. 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主外键写法对比的资料请关注九品源码其它相关文章!