SQL Server and MySQL Master foreign key notation comparison

SQL Server PRIMARY key notation:

--Column levelCreate TableDept (dept_noint Primary Key, Dept_name nvarchar (20) not NULL)--table-levelCreate TableDept (dept_noint  not NULL, Dept_name nvarchar (20) not NULL,Primary Key(Dept_no)) --ModificationCreate TableDept (dept_noint  not NULL, Dept_name nvarchar (20) not NULL);Alter TableDeptAdd Primary Key(DEPT_NO)--Add a primary key name

alter table dept Add constraint DEPT_NO_PK primary KEY (DEPT_NO)--Test insert  dept values  (10, ' IT ' ), (20, Finance ' ), (30, ' Engineer ' ); Insert into  Dept values  (10, ' IT2 ' ) 

MySQL PRIMARY key notation:

--column level, as with SQL ServerCreate TableDept (dept_noint Primary Key, Dept_namevarchar(20) not NULL)--table level, same as SQL ServerCreate TableDept (dept_noint, Dept_namevarchar(20) not NULL,Primary Key(Dept_no)) --Modify, same as SQL ServerCreate TableDept (dept_noint  not NULL, Dept_namevarchar(20) not NULL);Alter Table Add  Primary Key(dept_no);--another way to modify it is to add a nameAlter Table Add constraintDept_no_pkPrimary Key(DEPT_NO)

--Test

 into Values (Ten, 'IT '), ("Finance"), (+, 'Engineer ' )

 into Values (Ten,' IT2 ')

Is the same for Mao, is it standard SQL?

SQL Server FOREIGN key notation:

--Column levelCreate TableEmployee (employee_idint Primary Key, Employee_Name nvarchar (20) not NULL, dept_idint Foreign Key ReferencesDept (DEPT_NO))--table levelCreate TableEmployee (employee_idint Primary Key, Employee_Name nvarchar (20) not NULL, dept_idint,constraintDept_no_fkForeign Key(dept_id)ReferencesDept (DEPT_NO))--ModifyCreate TableEmployee (employee_idint Primary Key, Employee_Name nvarchar (20) not NULL, dept_idint);Alter TableEmployeeAdd Foreign Key(dept_id)ReferencesDept (DEPT_NO);Alter TableEmployeeAdd constraintDept_no_fkForeign Key(dept_id)ReferencesDept (dept_no)--Test insert intoEmployee (1001,' Zhangsan ', insert intoEmployee (1002,' Lisi ', 50)

The syntax for MySQL foreign keys:

--This is not the same as SQL Server, it seems there is no column level of the OHCreate TableEmployee (employee_idint Primary Key, Employee_Namevarchar(20) not NULL, dept_idint,Foreign Key(dept_id)ReferencesDept (Dept_no));Create TableEmployee (employee_idint Primary Key, Employee_Namevarchar(20) not NULL, dept_idint,constraintDept_no_fkForeign Key(dept_id)ReferencesDept (DEPT_NO))--Test insert intoEmployeeValues(1001,' Zhangsan ', insert intoEmployeeValues(1002,' Lisi ', 50)

In fact, the main foreign key writing is "constraint primary key name/foreign key name" This difference, I understand that is added to the individual name.

Delete primary key and foreign key

SQL Server (as if the primary foreign key alias must be in line):

Create TableEmployee (employee_idint, Employee_Name nvarchar (20) not NULL, dept_idint,constraintEmployee_id_pkPrimary Key(employee_id),constraintDept_no_fkForeign Key(dept_id)ReferencesDept (Dept_no));Alter TableEmployeeDrop constraintEMPLOYEE_ID_PK;Alter TableEmployeeDrop constraintdept_no_fk;--Test Insert intoEmployeeValues(1001,' Zhangsan ', insert intoEmployeeValues(1003,' Wangwu ', insert intoEmployeeValues(1001,' Niuliu ', +); insert intoEmployeeValues(1002,' Lisi ', 50);

Mysql:

Create TableEmployee (employee_idint Primary Key, Employee_Namevarchar(20) not NULL, dept_idint,constraintDept_no_fkForeign Key(dept_id)ReferencesDept (Dept_no));Alter TableEmployeeDrop Primary Key;Alter TableEmployeeDrop Foreign Keydept_no_fk;--Test Statement Insert intoEmployeeValues(1001,' Zhangsan ', insert intoEmployeeValues(1003,' Wangwu ', insert intoEmployeeValues(1001,' Niuliu ', +); insert intoEmployeeValues(1002,' Lisi ', 50);

Check constraints for MySQL under separate test

Create Table int Primary Key varchar  not NULL int Check  and int,constraintforeignkeyreferences into Values(1001,' Zhangsan 'intovalues(1002,' Lisi ', 29,10);

You can really insert into and use Show create TABLE employee to see if there are no check constraints

Search on the Internet:

"All storage engines parse the check clause, but ignore the check clause. ”
The CHECK clause is parsed and ignored by all storage engines.