Primary Key in SQL Integrity Constraints What does a foreign key constraint mean

Let's take an example. For example, a province table has a province ID and a province name, and the city table has a city ID, city name and province ID, then the foreign key in the city table is the province ID, when you set their constraints, this plays a role in data integrity, when you delete a province, if there is a city below, then you can't delete it directly, which is a constraint to prevent data incompleteness.

There are so many stickers upstairs, dizzy, let me explain briefly, the so-called main key, is equivalent to our ID card, unique.

Second, play the role of a unique identity, and foreign keys, such as when you are studying in school, the class will have your records, when you accidentally drop out, the original class will delete your information, so as to ensure the integrity of the data, other or what foreign key constraints, unique constraints, are established for the standardization and integrity of the database, completely personal understanding, good luck,,

Database foreign key constraint: This is the implementation of the constraint between the table, and the field value of the slave table must be in the primary table. The existence of a foreign key constraint is, after all, a constraint, just a means to ensure data integrity.

The concept of foreign keys: A foreign key is one of the five constraints of the three integrity constraints of a relational database, and it is a relationship established between tables. In Scott Tiger users of Oracle Database, tables dept and emp are associated by foreign keys.

Here, the dpt table is called the master table, the emp table is called the slave table, and the foreign key is the field deptno.

Condition for adding a foreign key:

1. The two tables have the same attribute fields and have the same value range.

2. The foreign key is the primary key or the only field in the primary table.

3. A foreign key can be a combination of multiple fields.

The database system itself is a more reliable means of constraints. For development, it may be unnecessary to establish a foreign key relationship, but in the later maintenance or upgrade phase, if there is no such relationship, it may not be conducive to the improvement of maintenance work. The establishment of table relationships also elaborates on specific business logical relationships and increases readability.

When the correlation is relatively strong, you may wish to add it. In other cases, simple foreign key constraints are also possible, and there is no need to add them as soon as they are related, but there must be other mechanisms to ensure data integrity, after all, foreign keys are sometimes limited to development. In general, the early development can be ignored, and the later maintenance can be transferred to the constraints of the database itself as much as possible to establish relationships.

Note that send and accept are foreign keys of the user table.

constraint f_key foreign key(send) references user(username);

constraint f_key2 foreign key(accept) references user(username);

For the messagebox primary key table itself, the foreign key table is naturally the user table, that is, the user table is the parent table of the messagebox table.

I don't understand what you mean, but the primary key is better to use an item that is not related to the business (add an ID), and the foreign key is the ID associated with another table

The landlord is good, if you want to know the primary key constraints, you need to know what the primary key is. The so-called primary key, you can understand it as a unique flag that can identify the data, for example, we set the ID, the ID can never be repeated, this is the primary key. When you add a primary key, you have a primary key constraint.

For example, your primary key is the login name, what data type this column is, whether it is auto-incrementing, and so on.

The concept of a primary table can be understood as a table that is at the most basic level in a relationship, which can be understood as a master table. For example, if I set up a sales hierarchy table, one table stores the region information, another table stores the dealer information, and another table stores the point-of-sale information. The dealer has a subordinate region, and the point of sale has a subordinate dealer, so in this relationship, the regional information becomes the main table.

A foreign key constraint is a foreign key constraint that requires relying on the upper-layer data to operate after the external relationship is set. For example, in the above example of sales hierarchy, if a dealer must have a region, if you set the region as a foreign key in the dealer table, then the region stored in your dealer table must be the region that exists in the region table, otherwise the dealer's region data cannot be inserted, modified, or deleted.

The above-mentioned relationship is in fact the master-slave relationship, and the established three table area tables become the master table, and the two tables of the dealer and the point of sale become the slave table, also called the sub-table.

For how to create a primary table, you must first create a primary table, then create a slave table, and create relationships in descending order. There are usually a lot of project development tools for designing these things, such as PointDesigner, Table Structure Design projects in VS, and so on.

In fact, it is very simple, you can know the time of the moment, and it will be enough in the future work.

1. The primary key is non-empty, that is to say, the column that is set as the primary key cannot be empty when inserting data, and if it is empty, SQL will report an error.

2. The primary key is unique, a table can determine a record through a primary key, and the primary key of a record is sf110, if you insert the record sql of sf110 into it, it will report an error.

3. Sometimes two fields can be used to establish a primary key, which is called a joint primary key. This happens when one field cannot be unique, and another field is used to ensure uniqueness.

Answer: The primary key is generally to select a field that can uniquely represent a record, for example, you have a "student table" with "student number", "student name", "student age", etc.

According to the principle, the "student number" must be the main key.

There is also a principle that fields that are not often modified can also be used as primary keys. The "student number" will certainly not be modified when the table is operated, so it is more consistent.

It is better to have a primary key for a table, because it is a good habit and if you do not set the primary key, you cannot add or delete it in SQL Enterprise Manager. I wonder if these supplements are available?

1. Foreign keys are not directly established.

You can only establish a foreign key when you establish a relationship between a table and other tables, because the so-called foreign key is used to make the association condition with other tables.

2. The foreign key is not related to the type, but the original table field and the corresponding field of the associated table must be of the same type and length when you create the foreign key.

3. The increment mode of the identification seed is in SQL

server

2005 The following are not changeable, sql

server

2005 can be changed. In addition, it can be changed in oracle4、The last example you said is definitely not a year, month and day.,I think it should be a 4-digit sequential number with 0 fill.,The date that comes from 0199.。 This has nothing to do with seed rules, but if you want to use this way as an identifier, the usual method is to write the corresponding function or stored procedure yourself to generate it according to the rules.

.Primary key constraint

1) The primary key is used to uniquely identify each record in the table, and one or more columns can be defined as the primary key.

2) It's impossible (or difficult) to update

3) No two rows on the primary key column have the same value (i.e., duplicate value), null is not allowed

4) The main health can be used as the external health, but the only index cannot be;

2.Uniqueness

1) Uniqueness constraints are used to limit the uniqueness of data on columns that are not constrained by the primary key, and are used as an optional means to access a row, and multiple uniqueness constraints can be placed on a table

2) It can be updated as long as it is unique

3) Any two rows in the table are not allowed to have the same value on the specified column, and null is allowed

4) Multiple uniqueness constraints can be placed on a table.

3.Unique index

Creating a unique index ensures that any attempt to generate duplicate key values will fail.

The difference between a uniqueness constraint and a primary key constraint:

1）.The uniqueness constraint allows null values to exist on the column, while the primary key constraint is more restrictive, not only does not allow duplicates, but also does not allow null values.

2）.You can create clustered and nonclustered indexes when you create uniqueness constraints and primary key constraints, but by default the primary key constraint produces clustered indexes, while the uniqueness constraint produces nonclustered indexes.

Constraints and indexes, the former is used to check the correctness of the data, and the latter is used to optimize the data query, with different purposes.

Uniqueness constraints are different from unique indexes:

1）.Creating a unique constraint creates a constraint in oracle, and also creates a unique index corresponding to that constraint.

2）.Creating a unique index creates only a unique index, not a constraint.

In other words, the only constraint is achieved by creating a unique index.

There is also a certain difference between the two when it comes to deletion:

When you delete a unique constraint, you can only delete the constraint without deleting the corresponding index, so the corresponding column must still be unique, and if the unique index is deleted, you can insert a non-unique value.

The primary key cannot be null, and the only thing that can be null is that duplicates are not allowed.

Primary key: It is the directory of this table, so that you can easily query, update, etc., since it is a "directory", it is impossible to have duplicate definitions, so there is automatically a primary key constraint.

Foreign key: It is the external constraint of the table, which requires that the value of a certain column must be consistent with a certain data outside, and is the constraint of the data between the tables.

Primary key constraint: It is required that the primary key cannot be repeated before the "directory" can be generated.

The only constraint is the requirement that one or several pieces of data cannot be duplicated.