What is the core differences between constraints like PK vs Indexes

问题: What is the core differences between a table that is created with constraints like PK vs a table created or added indexes created on it only (that don't have PK) when it co...

问题:

What is the core differences between a table that is created with constraints like PK vs a table created or added indexes created on it only (that don't have PK) when it comes which route would anyone prefer to implement when creating a table? I have work on them but I am just curious to know what separate them Thank you


回答1:

They are quite different.

Constraints check the data that is being inserted and updated meet some criteria (for example "not null"). If the data does not meet the criteria, the INSERT or UPDATE is rejected, and fails. Constraints help you to maintain the quality of the data.

Indexes improve (most of the time) the speed of a query, and are usually beneficial to SELECT, UPDATE, and DELETE operations. Indexes improve database performance.


回答2:

An index has no effect on how a query behaves nor the schema definition. It only effects performance. Although some SQL servers implement features using indexes, particularly the unique constraint. The SQL standard doesn't even mention them because they're considered an implementation issue.

A primary key constraint very much does have an effect on behavior and schema definition. It says this column must be unique and not null. Most databases also happen to index it for obvious performance reasons.

Declaring primary key rather than manually saying unique not null also lets the person reading your schema know that this is the primary key. They will know what its purpose it. It also lets the database know this is the primary key which might allow it to do some extra optimizations.

  • 发表于 2019-01-15 04:59
  • 阅读 ( 191 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除