数据库系统中用户定义的完整性规则的示例？
数据库系统中用户定义的完整性规则有哪些示例？
这意味着为列设置某些条件，插入数据时需要满足这些条件。
例如：如果我设置一个规则，ID 只需要由 5 个整数组成，那么当我插入 ID 由整数和一些字符组成的行时，它不会接受它并返回错误。
What are some examples of user-defined integrity rules in a database system?
It means setting up certain conditions for the columns and when data is inserted it needs to fulfill these conditions.
For example: If I set up a rule that an ID needs to consist of 5 integers ONLY then when I insert a row with an ID which is made up of integers and some chars then it won't accept it and will return an error.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
是的，这就是数据库的整体概念，而不是不受控制的数据存储位置。
它们不是用户定义的，而是由数据建模者或数据库管理员或数据库所有者（所有特定术语）定义的。如果你把它留给用户或开发人员，他们会阅读免费或互联网上可用的垃圾，并实现一个怪物。
30 多年来，数据库的理念是数据库的整个定义内部。在此期间，出现了许多标准（来自国际标准机构，而不是来自单个供应商或软件销售商），这些标准逐渐确定了各种标准以提高数据库的完整性。 SQL 供应商已经实现了它们（诚然，不是免费软件数据库系统，但他们不提供 ISO/IEC/ANSI SQL；从技术上讲，他们在这些产品中使用标准术语“SQL”是错误的）。
在标准 SQL 中，我们有以下内容作为声明（即不数据库外部的代码，不触发器）：
引用完整性。子行引用的父行必须存在，然后才能插入子行。这恰好是关系定义，因此不会重复。
数据完整性。多种形式：
首先确保使用正确的数据类型。始终如一。这样就无需将字符值放入整数列中； 4 位列中的 10 位值等。
规则。这些可确保遵守该列的所有规则（例如您的示例）。
检查约束。这些允许在允许插入之前对照其他列检查列值。它们可以简单也可以复杂。
（使用触发器，您可以实现上述声明未提供的任何进一步约束）
.
交易诚信。这确保了不同表中行之间的完整性，并且数据可以信赖。
我再说一遍，如果它没有（在每一列上）实现这些基础知识，它就不是一个数据库，它只是一个存储位置，用于存储不完整的数据。
您可以通过检查一些标准 SQL 手册或 SQL 标准本身（两者都可以在互联网上免费获得）来轻松确认这一点。
在互联网上找到的任何与上述相反的信息都不值得回应。
与标准争论是浪费时间；他们有一个目的；它们是由比我们更伟大的思想创造的；而且它们早已（30 年！）被更大的组织和政府所接受（要求）。小型组织可以通过遵循这些标准来节省时间，而不是坚信它们在某种程度上“不同”。我们这个行业处理的是物理科学技术，而不是魔法思维。
应用层的业务规则
嗯，如果你把规则放在服务器里，它们是由服务器保证的。我相信任何一天，任何应用程序层都能完美执行，尤其是那些不了解数据库或其用途的人编写的应用程序层。
数据库应该完全独立于使用它们的应用程序（注意复数）。将这些规则放在五个应用程序中而不是放在数据库中的一个位置是愚蠢的。当规则发生变化时会发生什么，谁能保证所有应用程序都会正确且一致地升级？
还请记住，数据是公司资产，而不是部门资产。公司实施标准，这样他们就可以避免部门管理人员因 30 年前在部门外解决的愚蠢争论而浪费时间。
同样，人们发布他们的想法，就好像他们是第一个经历这个思考过程的人，没有任何资格（这意味着他们了解标准）或理解或实际经验（这将很快改变他们的信念）。
因此：与数据相关的所有业务规则，无论以任何方式或形式，都应与数据一起放置在数据库的 IEC/ISO/ANSI 标准目录中。
任何业务逻辑（取决于应用程序和使用情况）都可以愉快地放置在业务决定的任何地方。这样，公司资产的完整性就得到了保护，而损害也仅限于拥有神奇思维的部门。
Yes, that's the whole idea of a database, as opposed to an uncontrolled data storage location.
They are not user-defined, they are Data Modeller or Database Administrator or Database Owner (all specific terms) defined. If you left it up to the users or the developers, they would read the rubbish available free or the internet, and implement a monster.
The idea of a database, for over 30 years, is that the entire definition for it is inside the database. Over that period, there have been many Standards (from international standards bodies, not from single vendors, or sellers of software), that have incrementally identified various standards to elevate the integrity of databases. The SQL vendors have implemented them (admittedly, not the freeware database systems, but they do not provide ISO/IEC/ANSI SQL; technically they are in error in using the standard term "SQL" in those products).
In standard SQL, we have the following, as Declarations (ie. not code outside the database, not triggers):
Referential Integrity. The parent row referred to by the child must exist before the child row can be inserted. That happens to be the Relation definition, so there is no doubling up.
Data Integrity. Various forms:
First ensure the correct Datatype is used. Consistently. That eliminates putting character values in integer columns; 10-digit values in 4-digit columns, etc.
RULES. These ensure that all rules for the column (such as your example) are observed.
CHECK Constraints. These allow column values to be checked against other columns, before insertion is allowed. They can be simple or complex.
(with triggers, you can implement any further constraints that the above Declarations do not supply)
.
Transactional Integrity. This ensures that the integrity between rows in different tables is maintained, and the data can be relied upon.
I repeat, if it does not have these basics implemented (on every column), it is not a database, it is merely a storage location, for data that has no integrity.
You can confirm this easily, by checking some Standard SQL manuals, or the SQL Standard itself (both available free on the internet).
Any information contrary to the above, found on the internet, is not worth responding to.
It is a waste of time to argue with Standards; they have a purpose; they were created by minds greater than ours; and they have long (30 years!) been accepted (demanded) by larger organisations and governments. Small organisations can save time by following these standards, instead of holding the belief that they are somehow "different". We are dealing with physical science and technology in this industry, not magical thinking.
Business Rules in the Application Layer
Well, if you put the rules in the server, they are guaranteed by the server. I would trust that any day, over the faultless execution of any application layer, particularly one written by people who do not understand databases, or their purpose.
Databases are supposed to completely independent of the apps (note the plural) that use them. It would be silly to place those rules in five apps, instead of in one place, in the database. What happens when the rules change, can anyone guarantee that all apps will be upgraded correctly, and in unison ?
Remember also that data is a corporate, not a departmental asset. The corporation implements standards, so that they can avoid the time wasted by departmental administrators having silly arguments that were resolved 30 years ago, outside their department.
Again, people posting what they think, as if they were the first person to go through this thought process, without any qualifications (which would mean they are aware of standards) or understanding or actual experience (which would change their beliefs real fast).
Therefore: all business rules that pertain to data, in any way shape or form, should be placed with the data, in the IEC/ISO/ANSI Standard catalogue, in the database.
Any business logic (app and usage dependent) can happily be placed anywhere the business decides. That way, the integrity of the corporate asset is preserved, and the damage is limited to the department that entertains magical thinking.
这些是 Oracle 中的 CHECK 约束，您可以这样定义它们：
These are CHECK constraints in Oracle, you define them like this:
MySQL 尚未遵守 Check 约束。它在 Create Table 语句中解析它们，但不尊重它们。因此，在 MySQL 中，您必须实现触发器中提到的那种数据完整性规则。
检查约束是实现您提到的数据完整性检查的正确 SQL 工具，大多数较大的供应商（DB2、SQL Server、Postgres 和 Oracle）都实施并遵守检查约束。但是，某些类型的约束（例如可能需要查询另一个表或复杂逻辑的约束）只能在触发器中实现。
通过约束维护数据完整性
数据完整性
MySQL does not as yet honor Check constraints. It parses them in a Create Table statement but does not honor them. Thus, in MySQL, you must implement data integrity rules of the sort you mentioned in a Trigger.
Check constraints are the proper SQL tool to implement data integrity checks of the sort you mentioned and most of the larger vendors (DB2, SQL Server, Postgres, and yes Oracle), implement and honor Check constraints. However, some types of constraints such as ones that might require a query to another table or complex logic, can only be implemented in Triggers.
Maintaining Data Integrity Through Constraints
Data Integrity