Table of Contents
设置表的主键
单字段主键
多字段主键
设置表的外键
设置表的非空约束
设置表的唯一性约束
设置表的属性值自动增加
设置表中属性的默认值
Home Database Mysql Tutorial MySQL学习笔记4:完整性约束_MySQL

MySQL学习笔记4:完整性约束_MySQL

Jun 01, 2016 pm 01:37 PM
information Database systems identification number

bitsCN.com

完整性约束是对字段进行限制,从而符合对该属性进行操作的特定要求

通俗点说:如果你的数据不满足我这一字段的要求,数据库系统就拒绝执行操作

 

设置表的主键

主键能够标识表中每条信息的唯一性,如同身份证号码和人的关系

人可以同名,但是身份证号码却是唯一的,

创建主键的目的在于快速查找到表中的某一条信息

 

单字段主键
mysql> create table student(    -> id int primary key,    -> name varchar(20),    -> sex boolean    -> );Query OK, 0 rows affected (0.09 sec)
Copy after login

创建了三个字段,其中id为主键

 

多字段主键

多字段主键由多个属性组合而成,在属性定义完之后统一设置主键

mysql> create table student2(    -> id int,    -> course_id int,    -> score float,    -> primary key(id,course_id)    -> );Query OK, 0 rows affected (0.11 sec)
Copy after login

student2表有三个字段,其中id和course_id的组合可以确定唯一的一条记录

 

设置表的外键

表的外键与主键是相对应的,比如表A中的id是外键,表B中的id是主键

那么就可以称表B为父表,表A为子表

设置表外键的作用在于建立与父表的联系,比如表B中id为123的学生删除后,表A中id为123的记录也随着消失

这样做的目的在于保证表的完整性

mysql> create table student3(    -> id int primary key,    -> course_id int,    -> teacher varchar(20),    -> constraint fk foreign key(id,course_id)    -> references student2(id,course_id)    -> );Query OK, 0 rows affected (0.12 sec)
Copy after login

这里创建student3表,constraint后面的fk是外键别名,foreign key也就是设置外键的字段

references后的内容表示父表,和父表中的主键

需要注意的是,父表中的主键不能为空,并且主键和外键的数据类型要一致

 

设置表的非空约束

非空性很好理解,就是设置表中字段的值不能为空(NULL)

如果在已经设置此约束性条件的字段中插入空值,数据库系统则会报错

mysql> create table student4(    -> id int not null,    -> name varchar(20),    -> sex boolean    -> );Query OK, 0 rows affected (0.10 sec)
Copy after login

这里的not null就是约束条件

 

设置表的唯一性约束

唯一性是指表中该字段的值不能重复出现,设置表的唯一性约束

也就是给表中某个字段加上unique

mysql> create table student5(    -> id int unique,    -> name varchar(20)    -> );Query OK, 0 rows affected (0.10 sec)
Copy after login

此处id字段便不可重复

 

设置表的属性值自动增加

auto_increment主要用于为表中插入的新记录自动生成唯一的ID

一个表只能有一个字段使用auto_increment约束

并且该字段必须为主键的一部分

mysql> create table student6(    -> id int primary key auto_increment,    -> name varchar(20)    -> );Query OK, 0 rows affected (0.12 sec)
Copy after login

这里的id是主键,并且会自动增加id值,比如1,2,3,4……

需要注意的是,auto_increment约束的值必须是整数类型

 

设置表中属性的默认值

在表中插入一条新的记录时,如果没有为该字段赋值

那么数据库系统会自动为该字段赋上一条默认值

mysql> create table student7(    -> id int primary key,    -> score int default 0    -> );Query OK, 0 rows affected (0.10 sec)
Copy after login

此处的score字段便会默认为0

 

 

bitsCN.com
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
1 months ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to remove author and last modified information in Microsoft Word How to remove author and last modified information in Microsoft Word Apr 15, 2023 am 11:43 AM

Microsoft Word documents contain some metadata when saved. These details are used for identification on the document, such as when it was created, who the author was, date modified, etc. It also has other information such as number of characters, number of words, number of paragraphs, and more. If you might want to remove the author or last modified information or any other information so that other people don't know the values, then there is a way. In this article, let’s see how to remove a document’s author and last modified information. Remove author and last modified information from Microsoft Word document Step 1 – Go to

How to get the GPU in Windows 11 and check the graphics card details How to get the GPU in Windows 11 and check the graphics card details Nov 07, 2023 am 11:21 AM

Using System Information Click Start and enter System Information. Just click on the program as shown in the image below. Here you can find most of the system information, and one thing you can find is graphics card information. In the System Information program, expand Components, and then click Show. Let the program gather all the necessary information and once it's ready, you can find the graphics card-specific name and other information on your system. Even if you have multiple graphics cards, you can find most content related to dedicated and integrated graphics cards connected to your computer from here. Using the Device Manager Windows 11 Just like most other versions of Windows, you can also find the graphics card on your computer from the Device Manager. Click Start and then

How to share contact details with NameDrop: How-to guide for iOS 17 How to share contact details with NameDrop: How-to guide for iOS 17 Sep 16, 2023 pm 06:09 PM

In iOS 17, there's a new AirDrop feature that lets you exchange contact information with someone by touching two iPhones. It's called NameDrop, and here's how it works. Instead of entering a new person's number to call or text them, NameDrop allows you to simply place your iPhone near their iPhone to exchange contact details so they have your number. Putting the two devices together will automatically pop up the contact sharing interface. Clicking on the pop-up will display a person's contact information and their contact poster (you can customize and edit your own photos, also a new feature of iOS17). This screen also includes the option to "Receive Only" or share your own contact information in response.

How to use Python regular expressions to extract ID number How to use Python regular expressions to extract ID number Jun 22, 2023 am 10:35 AM

In the process of data processing, it is often necessary to extract information in a specific format from text. As a relatively common piece of personal information, ID number is often used in data processing. You can use Python regular expressions to easily extract the ID number and perform certain verification on it. The ID card number is composed of 18 digits, including the region, date of birth, check code and other information in the ID card number. In Python, we can use the regular expression function of the re module to extract the ID number. head

PHP regular expression to verify whether the input string is in the format of ID number or passport number PHP regular expression to verify whether the input string is in the format of ID number or passport number Jun 24, 2023 pm 12:11 PM

ID number and passport number are common document numbers in people's lives. When implementing functions involving these document numbers, it is often necessary to perform format verification on the entered numbers to ensure their correctness. In PHP, regular expressions can be used to achieve this function. This article will introduce how to use PHP regular expressions to verify whether the input string is in the format of an ID number or passport number. 1. ID card number verification The ID card number is composed of 18 digits and the last digit may be a letter (check code). Its format is as follows: the first 6

PHP regular expression to verify birthday information of ID number PHP regular expression to verify birthday information of ID number Jun 24, 2023 pm 02:22 PM

The ID number is an identity proof tool that we often use in our daily lives, and the birthday information contained in it is also very important. When using PHP to verify ID numbers, we often need to determine whether the birthday information is correct. This article will introduce how to use PHP regular expressions to verify the birthday information of the ID number. 1. The basic format of the ID number. The ID number is a string composed of 18 digits and letters. The last digit may be a number or a letter, and may be uppercase or lowercase. The first 17 digits are the owner of the ID card

What is the three-level schema structure of a database system? What is the three-level schema structure of a database system? Jul 23, 2021 pm 03:58 PM

The three-level schema structure of the database system means that the database system consists of three levels: external schema, schema and internal schema. The schema, also called the logical schema, is the common data view for all users; the external schema, also called the user schema, is the data view of database users and is a logical representation of data related to an application; the internal schema, also called the storage schema, is the data within the database. of organization.

The single-view NeRF algorithm S^3-NeRF uses multi-illumination information to restore scene geometry and material information. The single-view NeRF algorithm S^3-NeRF uses multi-illumination information to restore scene geometry and material information. Apr 13, 2023 am 10:58 AM

Current image 3D reconstruction work usually uses a multi-view stereo reconstruction method (Multi-view Stereo) that captures the target scene from multiple viewpoints (multi-view) under constant natural lighting conditions. However, these methods usually assume Lambertian surfaces and have difficulty recovering high-frequency details. Another approach to scene reconstruction is to utilize images captured from a fixed viewpoint but with different point lights. Photometric Stereo methods, for example, take this setup and use its shading information to reconstruct the surface details of non-Lambertian objects. However, existing single-view methods usually use normal map or depth map to represent the visible

See all articles