Keys inside the database (SQL Server)

Introduction:

• The first few days of learning the project found a bit of basic insight into SQL.
• About the primary key in SQL, foreign key.

Primary key:

• There are 2 primary keys for the primary key. One is a business primary key and one is a logical primary key.
• The business primary key is usually the data in the table that is used as the primary key. Common is not easy to duplicate the data, such as: Social Security number, etc.
• The logical primary key is the original data does not have this field, but we worry about the duplication of data, we generate a data to make this the only important field, in a table very obvious primary key establishment is an int field marked him and let him self-growth (commonly referred to as the identity column)
• The function of the general primary key is in terms of "entity integrity".
• Entities: Records in a table, an entity refers to a record in a table.
• Entity integrity: You cannot have exactly the same records in a table, and each record must have a non-null and distinct primary key value.
• Using logical primary keys another advantage is that he has better physical properties. In the physical storage of SQL He is added down, and the data are arranged in a consistent way, so that any operation will be more convenient and fast.
• The GUID,GUID algorithm is an efficient algorithm that can produce a unique representation, which is calculated using the network card Mac, address, nanosecond time, and chip ID code, so that the GUID generated every time will never be duplicated, whether it is the same computer or a different computer. The GUID that was generated prior to A.D. 3400 is not the same as any other GUID that has been generated. Usually we use logical primary keys more reasonably.
• GUID If the primary key we usually have to note that he does not grow as the int field, he is random, so the use of GUIDs will also cause data storage physical variation, so that a table will be a lot of changes, of course, there is no absolute, there is a way to make the GUID of the sorting and data collation consistent.
• Methods in sql: NEWID ();

FOREIGN key:

• Foreign keys We usually cannot explain separately, my understanding is that when there are 2 interconnected tables to be combined to facilitate various operations, foreign keys play a big role, that is, the term "referential integrity"
• When you update, delete, and insert data from a table, check that data is being manipulated correctly on the table by referencing the data in the other table that is associated with it. Referential integrity requirements are not allowed to reference entities that do not exist in the relationship. Referential integrity and entity integrity are the integrity constraints that a relational model must meet.
• Simple is the relationship between the primary key foreign key in the table.
• Referential integrity is a constraint between the two tables that are associated, specifically, that the value of the foreign key from each record in the table must be present in the primary table, so if an association relationship is established between the two tables, the operation on one relationship affects the records in the other table.

Now there are two tables, the Users table has a field of "UserId" and a Field "Carid", "username", and the Cars table has a field of "Carid" and "Carname", and a foreign key association is added between them. So if we want to know one of the user's car name we can use this SQL to query

Select  from  as Inner Join  as  on U.carid=where u.userid=@UserId

Please note that the U.carid=c.carid here will have two sheets connected. We also found the results of the 2 tables we wanted.

Defining primary and foreign keys is primarily to maintain the integrity of the relational database, summarizing:

A primary key is a unique identifier that can determine a record, for example, a record includes a positive sign, name, and age. The ID number is the only person who can identify you, others may have duplicates, so the ID number is the primary key.

A foreign key is used to associate with another table. is a field that determines the record of another table, which is used to maintain data consistency. For example, a field in a table is the primary key of table B, so he can be a foreign key to table A.

Second, the difference between the primary key and the foreign key

• Defined:

1. Primary Key-uniquely identifies a record, cannot be duplicated, is not allowed to be empty
2. Foreign key--the foreign key of the table is the primary key of another table, the foreign key can have duplicate, can be a null value

• Role:

1. Primary key--to ensure data integrity
2. Foreign key--used to establish contact with other tables.

• Number:

1. Primary key--the primary key can only have one
2. Foreign key--a table can have multiple foreign keys

Keys inside the database (SQL Server)