Shulou(Shulou.com)05/31 Report--

This article mainly explains the definition and usage of the primary key of MySQL. The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn the definition and usage of the primary key of MySQL.

The primary key does not have a clear conceptual definition, it is a kind of index, and it is a kind of unique index, and it must be defined as "PRIMARY KEY". It is something that can only be understood and indescribable. Let me give you a brief introduction to the primary key of MySQL in popular and even vulgar language.

Brief description:

The primary key cannot be repeated, just like the user name of QQ, there are N netizens called "bug zi", but their QQ number is different, that is to say, the identity that really identifies a QQ is "QQ number". There are all kinds of evil ID cards, no matter "Zhang San, Li Si, Wang er pockmarked", these can not represent a person, what can really represent a person's exact identity is the "evil ID card with Chinese characteristics", which is equivalent to stamping a pig when killing a pig, saying that this pig has been tested and has an identity, but the person was born with a "certificate" to confirm it to you, although it will not be issued to you until the age of 18.

But the primary key is different from the QQ number, because a table can only have one primary key, while a person can have multiple QQ numbers, which is why people like to fool each other in the virtual world, because you really don't know that there is a pig sitting on the other side of the computer.

It seems to have gone a little too far, and then let's take a look at our mysql primary key.

The method of declaring the primary key:

You can add a primary key to the table when you create it, such as:

CREATE TABLE tbl_name ([field description omitted...], PRIMARY KEY (index_col_name))

You can also add a primary key to the table when updating the table structure, such as:

ALTER TABLE tbl_name ADD PRIMARY KEY (index_col_name, …)

Example of primary key:

The primary key is considered to be the best combination of NOT NULL and UNIQUE constraints. If these columns are not explicitly defined as NOT NULL,MySQL, they are implicitly defined.

/ *

Create a qq table with qq_id as the primary key without NOT NULl constraints

, /

Create table qq (

Qq_id int (10)

Nick_name varchar (255) not null

Primary key (qq_id)

)

/ *

Insert a piece of data, set the qq number to 10000 (let's also fantasize about it), and set the nickname to "simaopig"

, /

INSERT INTO qq (qq_id, nick_name)

VALUES (

'10000 dollars, 'simaopig'

);

/ *

Insert a piece of data, and the qq number is still 10000, because data like 10000 already exists in the database.

And the most important thing is that its QQ number is the primary key, so the error is reported, the information is as follows

# 1062-Duplicate entry '10000' for key 'PRIMARY'

, /

INSERT INTO qq (qq_id, nick_name)

VALUES (

'10000 dollars, 'chongpig'

)

/ *

Although the qq number field is not constrained by NOT NULL, it cannot be NULL because it is a primary key

# 1048-Column 'qq_id' cannot be null

, /

INSERT INTO qq (qq_id, nick_name)

VALUES (

NULL, 'chongpig'

)

The primary key is also the index:

As I just said, the primary key is actually an index, and even in MySQL terms, "key" equals "index", so "foreign key" must be set to "index" first, which we will discuss in the next log. So the primary key, like the index, can act on both individual fields and multiple fields.

Take Jane as an example. I live in Unit 3, Room 501, and my name is Little Boy, so only Unit 3, Room 501 can only identify my home in the list of this community. Because unit 2, room 501 may also live in a kid, so only two fields can uniquely identify me, that is to say, a combination of the two can be used as the primary key. For the combined primary key, each column implicitly defines the NOT NULL constraint, and together the two define the UNIQUE unique constraint.

The example does not write what you want, but the example cited in the book is more appropriate, that is, a firewall is determined by the combination of host and port. The code example is as follows:

/ *

Create the fire wall table and set the combination of host and port as the primary key. Notice that I did not set the NOT NULL constraint on port.

, /

Create table firewall (

Host varchar (11) not null

Port smallint (4)

Access enum ('deny',' allow') not null

Primary key (host,port)

)

/ *

Insert a new record, there's no problem.

1 row (s) inserted.

, /

INSERT INTO firewall (

Host

Port

Access

)

VALUES (

'202.65.3.87 percent,'21 percent, 'deny'

);

/ *

Insert failed because the primary key value of host plus port 202.65.3.87-21 already exists

# 1062-Duplicate entry '202.65.3.87-21' for key 'PRIMARY'

, /

INSERT INTO firewall (

Host

Port

Access

)

VALUES (

'202.65.3.87 percent,'21 percent, 'allow'

);

/ *

NOT NULl port is not declared and cannot be NULL.

# 1048-Column 'port' cannot be null

, /

INSERT INTO firewall (host, port, access)

VALUES (

'192.168.0.1, NULL, 'deny'

)

In this example, both host and port can be repeated, but not at the same time, because they are combined primary keys. And neither can be inserted into the NULL because it is the primary key.

We can take a look at phpmyadmin, see that the default value of the port field is 0, which is the same as the index rule we talked about yesterday, NOT NULL and set DEFAULT, because it is an integer, so it is 0, if it is a string, the default value is "

Thank you for your reading, the above is the content of "definition and usage of MySQL primary key". After the study of this article, I believe you have a deeper understanding of the definition and usage of MySQL primary key, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.