Constraint is a kind of restriction , It limits the data of a table's rows or columns , To ensure the data integrity of the table 、 Uniqueness .

MYSQL in , Several common constraints :

Constraint type : Primary key The default value is only Foreign keys Non empty



Primary key (PRIMARY KEY) Is used to constrain a row in a table , As the identifier for this line , In a table, you can accurately locate a row through the primary key , So the primary key is very important . The primary key requires that the data in this row cannot be duplicated and cannot be empty .

There is also a special primary key —— Composite primary key . A primary key can be more than just a column in a table , It can also be identified by two or more columns in the table


Default constraint (DEFAULT) Regulations , When there is DEFAULT Bound columns , What to do when the insert data is empty .

DEFAULT Constraints will only be used INSERT sentence ( The last experiment introduced ) When it comes to ,INSERT In the sentence , If you are DEFAULT The position of the constraint has no value , So this position will be DEFAULT The value of


Unique constraint (UNIQUE) Relatively simple , It specifies that the values of a column specified in a table must not have duplicate values , That is, each value in this column is unique .

When INSERT Statement when the newly inserted data and the existing data are repeated , If there is UNIQUE constraint , be INSERT Failure .


Foreign keys (FOREIGN KEY) To ensure data integrity , Can also show the relationship between tables .

A table can have multiple foreign keys , Every foreign key must REFERENCES( Reference resources ) The primary key of another table , Columns constrained by foreign keys , The value must have a corresponding value in the column it references .

stay INSERT when , If the value constrained by the foreign key does not correspond in the reference column , For example, the following command , Reference column (department Tabular dpt_name) There is no dpt3, be INSERT Failure


Non empty constraint (NOT NULL), Listen to the name to understand , Columns constrained by non empty constraints , Must be non empty when inserting value .

stay MySQL Violation of nonempty constraint in , No mistake. , There will only be warnings .

attach : SQL sentence

 CREATE DATABASE mysql_shiyan;
use mysql_shiyan;
CREATE TABLE department
dpt_name CHAR(20) NOT NULL,
people_num INT(10) DEFAULT '',
CONSTRAINT dpt_pk PRIMARY KEY (dpt_name)
); CREATE TABLE employee
name CHAR(20),
age INT(10),
salary INT(10) NOT NULL,
phone INT(12) NOT NULL,
in_dpt CHAR(20) NOT NULL,
UNIQUE (phone),
CONSTRAINT emp_fk FOREIGN KEY (in_dpt) REFERENCES department(dpt_name)
); CREATE TABLE project
proj_num INT(10) NOT NULL,
proj_name CHAR(20) NOT NULL,
start_date DATE NOT NULL,
end_date DATE DEFAULT '2015-04-01',
of_dpt CHAR(20) REFERENCES department(dpt_name),
CONSTRAINT proj_pk PRIMARY KEY (proj_num,proj_name)

MYSQL database - More articles on constraints

  1. MYSQL Database constraint types

    07.14 Self summary MYSQL Database constraint types One . Primary key constraint (primary key) The primary key constraint requires that the data in the primary key column is unique , And can't be empty . There are two types of primary keys : Single field primary key and multi field joint primary key . 1. Single field primary key How to write it ...

  2. MySql Database Constraints

    <MySQL Technology insider :InnoDB Storage engine > This book from the perspective of source code depth analysis of InnoDB Architecture of . Realization principle . Working mechanism , And give a lot of practice , In line with the idea of reading books thinly , Step by step record the reading notes of this book ...

  3. linux And -mysql Database Constraints 3

    stay MySQL in , There are usually these kinds of constraints : DROP DATABASE mysql_shiyan; Delete database Primary key (PRIMARY KEY) Is used to constrain a row in a table , As a unique identifier for this line , In a table, through the primary key ...

  4. MySQL When there are foreign key constraints truncate The way to order

    MySQL Database operation ,Delete And Truncate Both commands can delete all data in a data table , How to use them : DELETE FROM t_question TRUNCATE TABLE t_que ...

  5. Mysql There are three paradigms in database settings Five constraints of database Database basic configuration

    There are three paradigms in database settings 1. First normal form ( Make sure that each column remains atomic ) The first paradigm is the most basic paradigm . If all field values in the database table are atomic values that cannot be decomposed , It shows that the database meets the first paradigm . The reasonable following of the first paradigm needs to be based on the actual needs given by the system ...

  6. MySQL database -- Foreign key constraints and foreign key usage

    What is a primary key . There are several properties in a record in a foreign key relational database , If one of the attribute groups ( Notice it's a group ) Can uniquely identify a record , This property group can be a primary key . such as : Student list ( Student number , full name , Gender , class ) The student number of each student is the only one ...

  7. MySql Introduction to database data types and constraints

    1,MySql Introduction to data types in Integer and floating point data type Storage range byte TINYINT There are signed values :-2^ To 2^7-1 Unsigned value :0 To 2^8-1 2 SMALLINT There are signed values :-2^15 To 2^15-1 ...

  8. MySQL Advanced : constraint , Multi table design , Multi-table query , View , Database backup and restore

    MySQL Advanced Sort out the knowledge points One . constraint 1. Foreign key constraints Why have foreign key constraints for example : One user surface , One orderlist If you want to delete it now id by 1 Zhang San , however orderlist There are also users id by 1 Order of ...

  9. mysql Database engine

    One . Database engine The database engine is for storage . Core services that process and protect data . With the database engine, you can control access and process transactions quickly , This meets the requirements of most applications within the enterprise that need to process large amounts of data . Use the database engine to create a database for online transaction processing or ...

Random recommendation

  1. Use pngquant To compress png Resources are shrinking apk

    Recently we found a new one called pngquant Tools for , Can effectively compress the resource file in png file , So as to reduce the amount of apk Size . I found that this tool has two characteristics : 1. It's not damaged , Compressed and rerun my app There's no difference in finding 2 ...

  2. MongoDB Regular expressions

    Example MongoDB Use  $regex  Operator to set the regular expression that matches the string . > db.col.find() { "_id" : ObjectId("56c6bb ...

  3. MyEclipse Next Tomcat How to solve the problem of slow startup

    MyEclipse   Next  Tomcat How to solve the problem of slow startup Project use debug Sometimes the startup will suddenly become very slow . Not only is it slow to start , It's very slow to open the project page after startup , Everyday is 4,5 times . There are several solutions : 1. ...

  4. javascript Layout The first 20 section

    <html> <head> <title> The page layout </title> <style type="text/css"> bo ...

  5. Restore the deleted procedure

    If 10 I just deleted one by mistake procedure, I didn't save the script , Now how to recover ? drop procedure necessarily delete dba_source,delete Of course, I think of flashback queries sql>c ...

  6. lesson6:jmeter and badboy In combination with

    because jmeter Script recording is not supported , It can only be set manually , In some integrated stress tests , It's not very convenient to use , At this time, we can communicate with badboy Used together ,badboy Support the recording function of web operation , And can export the recorded operation as jmeter Feet of ...

  7. note

    1.  review a)         WebForm Front and back page (aspx, aspx.cs) When a file is accessed , Will be compiled into classes , The foreground class inherits from the background class b)         When interviewed , The server will create [ Front page class ] Yes ...

  8. Linux The kernel exports symbolic macro definitions EXPORT_SYMBOL Source code analysis

    resources : <include/linux/moudule.h> --. #ifndef MODULE_SYMBOL_PREFIX #define MODULE_SYMBOL_PREFIX &q ...

  9. P2P Direct connection ? Transfer through the server ?

    When two clients of the same system A.B When sending messages to each other , If there's something between them P2P passageway , So there are two ways to send messages : Direct channel P2P Channel transmission . Or transfer through the server . As shown in the figure below : Generally speaking, for general application , If P2P Channels can ...

  10. #centos7 Create Intranet yum Source OpenStack Source deployment

    #centos7 Create Intranet yum Source #centos7 Automatic installation Local Intranet web Source creation . to update createrepo http OpenStack Source deployment Elven original  http://www.cnbl ...