SQL statement Build table, set primary key, foreign key, check, default, UNIQUE constraint

· What is a database?

The warehouse where the data is stored.

· What is the difference between a database and a data structure?

To solve the problem of manipulating data in memory, the database should solve the problem of manipulating data in the hard disk. Data structures study some abstract data models (ADT) and some of the operations defined on the model, the database is composed of tables, relationships, operations.

· What is a primary key?

The primary key is used to identify the uniqueness of the record.

· What is a foreign key?

A foreign key is used to identify a table-to-table connection.

· What is a check constraint?

A check constraint restricts the range of input values.

· What is the default constraint?

Give a property a default value.

· What is a unique constraint?

Restricts the uniqueness of a property.

· What is the difference between a unique constraint and a primary key?

The primary key cannot be null.

Some SQL statements about the above knowledge:

--Department TableCreate TableDept (dept_idint Primary Key, Dept_namenvarchar( -) not NULL, Dept_addressnvarchar( -))--Employee TableCreate TableEMP (--cannot be written as {emp_idint constraintPk_emp_id_hahahaPrimary Key,--set the primary key and nameEmp_namenvarchar( -) not NULL,--the name cannot be emptyEmp_sexnchar(1),    --↓ Set Foreign key, the foreign key is from the Dept table (primary key table)dept_idint constraintFk_dept_id_heiheiForeign Key ReferencesDept (dept_id),)Create TableStudent (stu_idint Primary Key, Stu_salint Check(Stu_sal>=  +  andStu_sal<= 8000),--Check ConstraintStu_sexnchar(1)default('male')--() can be omitted, the string must be enclosed in the database .)--inserting data into the student tableInsert  intoStudent (Stu_id,stu_sal)Values(1, +);--can be insertedInsert  intoStudent (Stu_id,stu_sal)Values(2,10000);--Insert failure, conflict with CHECK constraintInsert  intoStudentValues(2,6000,'female');--can be insertedInsert  intoStudentValues(3,6000);--error, the number of columns does not match. --and rebuild a table .Create TableStudent2 (stu_idint Primary Key, Stu_salint Check(Stu_sal>=  +  andStu_sal<= 8000),--Check ConstraintStu_sexnchar(1)default('male'),--() can be omitted, the string must be enclosed in the database .Stu_namenvarchar( $)Unique--qunique Constraints)Insert  intoStudent2Values(1,6000,'male','Zhang San');--OKInsert  intoStudent2Values(2,6000,'male','Zhang San');--error violates the unique constraintInsert  intoStudent2Values(3,6000,'male','John Doe');--OKInsert  intoStudent2Values(NULL,6000,'male','Harry');--The error primary key cannot be null, the error message is "Cannot insert value null into column ' stu_id '"Insert  intoStudent2Values(4,6000,'male',NULL);--OK indicates that the unique key is allowed to be emptyInsert  intoStudent2Values(5,6000,'male',NULL);--error SqlServer2005 only allows one unique column to be empty--and rebuild a table .Create TableStudent3 (stu_idint Primary Key, Stu_salint Check(Stu_sal>=  +  andStu_sal<= 8000),--Check ConstraintStu_sexnchar(1)default('male'),--() can be omitted, the string must be enclosed in the database .Stu_namenvarchar( $)Unique  not NULL--qunique constraints and NOT NULL constraints can be combined using)Insert  intoStudent3Values(3,6000,'male',NULL);--error proves that a unique can be combined with a NOT null

Note: This article refers to the Hao bin of the teacher's SQL tutorial, but also added some of their own understanding of SQL, there is a wrong place to write the hope that we can point out.

SQL statement Build table, set primary key, foreign key, check, default, UNIQUE constraint