Python Foundation _mysql constraints

Default constraint (defaults)

CREATE TABLE TB (    ID int default 'a',    name varchar);

When inserting data, if the field is not explicitly assigned a value,
The default value is automatically assigned

The default value is NULL if no default value is set

Non-null constraint (NOT NULL)

CREATE TABLE TB (    not   null,    name varchar);

Limit the value of a field to not be empty.
The field must be assigned a value when the insert is
Null character not equal to NULL

Unique constraint (unique key)

CREATE TABLE TB (    Unique key,    name varchar);

Limit the value of a field to not repeat,
The data for this field cannot appear in duplicate
Make sure that the value in the field is unique

PRIMARY KEY constraint (primary key)

CREATE TABLE TB (    primary key,    name varchar);

Typically, each table requires a primary key to represent uniqueness
There can only be one primary key in each table
Primary KEY = non-null + unique

Self-growth constraint (auto_increment)

CREATE TABLE TB (    auto_increment,    name varchar);

Specify the location from which to start the increment:

CREATE TABLE TB (    auto_increment,    name varchar)auto_increment  =100;

Automatic numbering, combined with primary key,
A table can only have one self-growth
Auto_increment required on the primary key

FOREIGN KEY constraint (foreign key)

CREATE TABLE A (    primary key,    name varchar);

Keep your data consistent
I have you must have, you do not, I absolutely did not

CREATE TABLE B (    primary key,    name varchar (),    foreign key  References A (id_a));

1. The Id_b field in table B can only add data that is already in the id_a.
2. Id_a referenced data in a table cannot be modified or deleted

So here, the constraints on these, we write simple table structure game;

For example, students have a school number, name, college, but students have some such as telephone, home address and other more private information, this information will not be placed in the student table, will create a new student's details table to store.
The relationship between the student table and the student's detail table is a one-to-one relationship, because a student has only a single piece of detailed information. This relationship is achieved by using the primary key plus the primary key.

# Student Table (one field for each pair)

primary Key auto_increment, name varchar (not   null);

# Student Details Table (one field for each pair)

primary key, sex varchar ( not  null,foreign keyreferences student (ID));

Usually, a college can have a lot of students, and a student belongs to only one college.
The relationship between college and students is a one-to-many relationship, which is achieved through a foreign key association.

# College Tables (one-to-many field associations)

CREATE TABLE Department (    d_idprimary key auto_increment,      #  Academy ID Not   null                        # College name );

# Student Table (one-to-many field associations)

CREATE TABLE student (   primary key auto_increment,      #  student IDnot   null ,                     #  student name   dept_idnotnull,                       #   Affiliated Academy ID   foreign key(dept_idReferences Department (d_id)   # foreign key );

Many-to-many relationships (student elective courses)

Students have to enroll in elective courses, one student can enroll in more than one course, and a lot of students enroll in a course, so the student table and curriculum form a many-to-many relationship.
For many-to-many relationships, you need to create an intermediate table implementation.

#建立课程表:

CREATE TABLE Cours (    primary key auto_increment,    cours_name varchar (not   null  );

# Selected Timetable (intermediate table)

CREATE TABLE Select (    s_id int,            # used to record student ID    cours_id int,       #  Used to record course ID    primary key(s_id,cours_id),                #    foreign key  references student (s_id),       #   Associate student ID    foreign key references cours (cours_id)  #  associated Course ID);

So now: College table, Student table, timetable, schedule, 4 tables have been linked together, we can insert parameters.

The above as a summary of learning, a bit messy thinking.

Python Foundation _mysql constraints