Database master-Slave table, relationship, principal, foreign key relationship and role

from the database is a backup of the primary database, when the primary database changes from the database to be updated, these database software can design the update cycle. This is a means of improving information security. The master-slave database server is not geographically located, and the database can be saved when an accident occurs.
relationship structure of the primary foreign key:
1, one-to-one, without reference to the primary foreign key, put them in a table can
For example: A student can have only one card number, then the student and the card number in a table can be
2, one-to-many, referring to the primary foreign key, ' a ' equivalent to the primary key, ' more ' is the foreign key referencing the primary key.
For example: A class can have more than one student, and a student can only belong to a class, this is a one-to-many relationship;
3, many-to-many relationship, need to create a table, the table requires two fields, respectively, is a reference, two many-to-many field primary key foreign key.
For example:
Student's form (school number, name, ...). ) PRIMARY key: study number
Class Schedule (course number, course name, ...). ) Primary key: Course number
A student can choose multiple courses, a class can be selected by multiple students, that is, many-to-many relationships,
so the score table is the body of the relationship between them,
that is to cite the score table (school number, course number, score). Here the student number and the course number are the foreign keys of the students ' table and the curriculum number and the school number.
in our present phase, we are mainly using a one-to-many relationship between the main foreign keys
primary key primary key, which uniquely identifies an entity. is to ensure the entity integrity of the database, ensure the correctness and rationality of data, the value of non-null unique.
foreign key foreign, which is used to connect tables to tables. To ensure the referential integrity of the database, the foreign key must be taken from the Reference table Reference column value, either empty or blank.
the role of foreign keys:
maintain data consistency, integrity, the primary purpose is to control the data stored in the Foreign key table. Make two tables associative, and foreign keys can only refer to the values of columns in the outside!

first, what is the primary key, foreign key
There are several properties in a record in a relational database if one of the attribute groups (note that
group) can uniquely identify a record the attribute group can be a primary key
like
Student Table (School number name sex Class)
Each student's school number is the only school number is a primary key
Curriculum (Course number, course name, credits)
where the course number is unique, the course number is a primary key
Score Table (School number, course number, score)
a single attribute in the score table cannot uniquely identify a combination of a record number and a course number to
to uniquely identify a record so the attribute group for the study number and course number is a primary key
the school number in the score table is not the primary key of the score table, but it corresponds to the student number in the students table and
The student's form is the primary key of the student table, and the student number in the table is the foreign key of the students ' table.
The course number in the results table is the foreign key of the curriculum.
defining primary keys and foreign keys is primarily to maintain the integrity of the relational database.
1. A primary key is a unique identifier that can determine a record, such as a record, including the identity plus name.
name age.
The ID number is the only thing that can make sure you're the other one.
is the primary key.
2. A foreign key is used to associate with another table. is a field that determines that another table record is used to hold
consistency of data.
For example, a field in table A is the primary key of table B so he can be a foreign key to table A.
ii. differences between primary key, foreign key, and index
What is the difference between a primary key, a foreign key, and an index?
primary key foreign key index
defines a foreign key that uniquely identifies a record that cannot have duplicates that are not allowed to be empty table is another table
the primary key, the foreign key can be duplicated, can be null the field does not have duplicate values but can have a null value
function is used to ensure that data integrity is used to establish contact with other tables by increasing query ordering
Speed
The number of primary keys can have only one single table may have multiple foreign keys a table can have multiple unique indexes
What is the difference between a clustered index and a nonclustered index?
a clustered index must be a unique index. But a unique index is not necessarily a clustered index.
The clustered index holds the data directly in the index page and the non-clustered index is stored in the index page .
Index These indexes to point to data on specialized data pages.
Design principle of primary key and foreign key in database
primary keys and foreign keys are binders that organize multiple tables into an effective relational database. Primary KEY and outer
The design of keys has a decisive impact on the performance and usability of the physical database.
The database schema must be converted from a theoretical logical design to an actual physical design. While the primary key and
The structure of the foreign key is the crux of the design process. Once the designed database is used in the production ring
It is difficult to modify these keys, so designing the primary and foreign keys at the development stage is very
necessary and worthwhile.
PRIMARY Key
The database relies on the primary key---it is the cornerstone of the database physics model.
There are only two uses for the primary key on a physical level
1. Uniquely identify a row.
2. As an object that can be effectively referenced by a foreign key.
based on the above two purposes, the following shows the one I followed when I designed my primary key at the physical level .
some principles
1. The primary key should not be meaningful to the user. If a user sees a number that represents a many-to-many relationship
connecting the data in the table and complaining that it is useless is proving that its primary key is well designed.
2. The primary key should be single-column to improve the efficiency of the connection and filtering operations.
note people who use composite keys usually have two reasons to justify themselves and both of these reasons are wrong.
the. The first is that the primary key should have a real meaning, but making the primary key meaningful is only artificially
the destruction of the database provides convenience. The second is to use this method to describe the connection table of many-to-many relationships
use two foreign keys as primary key I also object to the reason that composite primary keys often lead
A bad foreign key is when the join table becomes another from the table's main table and is based on the second method above
become part of the primary key of this table and it is possible for the table to become the main table from the table.
The key is also likely to be another part of the primary key from the table so passed down from the table its main
The more columns will be included in the key.
3. Never update the primary key. In fact, because the primary key is uniquely identifying a row
There is no other use, so there is no reason to update it. If the primary key needs to be updated, say
The democracy key should be violated by the user's meaningless principle.
Note This principle is important for those who often need to complete data during data conversion or multi-database merging
not apply to the data.
4. The primary key should not contain dynamically changing data such as timestamps, creation time columns, modification time columns, and so on.
5. The primary key should be automatically generated by the computer. If a person is to intervene in the creation of a primary key, it will
make it meaningful except for a single line of identifiers. Once you cross this line, you may have a thought
the motivation to modify the primary key so that the system used to link record lines, Management records line of the key means of
will fall into the hands of those who do not understand the design of the database.
Iv. Database primary key selection policy
We need to specify a primary key for each table when we build the database the so-called primary key is the ability to
a property or property group that uniquely identifies a row in a table a table can have only one primary key but can have multiple
a candidate index. Because a primary key uniquely identifies a row of records, you can ensure that data updates are performed,
There is no pigtailed error when deleting. Of course, other fields can assist us in executing this
To eliminate the sharing conflict in some operations but it's not discussed here. The primary key in addition to the above function is often
A referential integrity constraint with foreign keys prevents data inconsistencies. So the database is in the design-time Master
The key plays a very important role.
Common Database Primary key selection methods are
• Auto-Grow field

Many database designers prefer to use the auto-grow field because it is simple to use. Automatic growth
Type field allows us to add data to the database without taking into account the value of the primary key after the insertion of the record
It is automatically assigned a value by the library system to ensure that there is absolutely no duplication. If you use SQL
Server database, we can also use the @identity global variable to get the system after the record is inserted.
The primary key value of the system assignment.

• Manually Grow Fields
· uniqueidentifier
·" Comb (Combine) "type

Database master and slave tables, relationships, primary, foreign key relationships, and roles