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.