Shulou(Shulou.com)06/01 Report--

The country has the law, the family has the rules.

In fact, most of the time, technology is closely related to life, and what kind of needs will be solved.

Database is also such a magical thing, after all, it is a relational database, data is independent and can be associated with tables, sometimes it needs constraints, and at some times you have to behave and do things in order to be more valuable.

Let's talk about constraints first.

The beginning is small talk, the fundamental constraint is to protect the integrity of the data in the database, integrity is the accuracy and reliability of the data, the data in those databases are in line with certain definition rules, when they do not comply with these rules, the operation will fail.

What's the primary key?

The primary key prevents the same records on both sides of the same data table, ensures the integrity of the data, returns only one row of data through the primary key query, and there is only one primary key in a table (don't be confused with multiple fields and a primary key! For people who create primary keys, their consciousness is likely to be somewhat different from our real life in order to ensure the unique representation of the data. We say that a class can have duplicate names, but the will of the primary key is not allowed, and it prompts you to repeat, so generally for multiple people who can clearly reflect the real needs and violate the will of the primary key, we build the primary key on multiple columns (still a primary key), so we can distinguish it. Our ideal primary key is the id number, which can grow automatically, but the data type can be the primary key.

Format:

Create table teacher (id number (10) not null primary key)

Explanation:

Create a primary key and grow automatically without adding options. Then there can be no duplicate values in the id column!

Format:

Create table teacher1 (id number (10) not null,name varchar2 (20), constraint pk_one primary key (id,name))

Explanation:

This is the primary key to establish multiple fields, but still a primary key, then insert the name is different, id can be the same, such as a book publishing house is the primary key, the book title is different is also possible, there is always a way to meet our needs.

Format:

Select table_name,constraint_name,constraint_type,status from user_constranints where table_name='TEACHER';-- indicates uppercase, sensitive and case sensitive

Select constraint_name,table_name,status,column_name from user_cons_columns where table_name = 'TEACHER'

Explanation:

One query constrains basic information, and the other view query constraint is based on that column

Format:

Alter table teacher add constraint pk_add primary key (id,name)

Explanation:

The table has been created, and alter knows at a glance that the language for operating the existing table is to add the primary key of multiple fields.

Format:

Alter table teacher modify (id number (10) primary key)

Explanation:

Add a primary key to a single-column field

Format:

Alter table teacher rename constraint pk_one to fk_teacher;-- because when the primary key is created, if you specify the primary key name without the constraint keyword (display name), it will automatically generate the primary key name, which is meaningless to us, and we need to modify it according to our own needs.

Alter table teacher drop primary key;-- primary key is only one table, so you don't have to specify the primary key name after the keyword.

Alter table teacher disable primary key;-- temporarily disables primary key enbale on

Where on earth is the primary key used?

Of course, we have higher data integrity requirements, it is better to have a primary key, the essence of the primary key is to ensure the integrity of the data, and the second is that when we always query with a field, the primary key is the right choice. some tables need to be associated with other tables and sometimes need to be updated synchronously to consider whether it is convenient for foreign keys!

What is a foreign key?

Outside the primary and external, just like father and son, the better secondary primary key of the foreign key ensures the integrity of the data, and the important thing is the constraint relationship between the table and the table. Inserting the foreign key table will check the data of the primary key field, delete and update will also be checked.

Format:

Alter table student add constraint fk_student foreign key (student_id) references teacher (id)

explain

Create a foreign key named fk_student to associate the id of the student table on the incoming id, just like an one-to-one tutor, a teacher id can id multiple students

Modify foreign keys some operations rename ah, delete and modify are roughly the same as the primary key format.

Then we will fail to update and delete the teacher table, because it has been constrained, so there is no way to delete the teacher table before the student table is deleted, then the value of the foreign key can be empty and can be repeated. If the teacher id updates, students should also be able to update id, and cascading updates / deletes will come out in this context.

Cascading update deletion

What is the cascade update and delete operation? on the whole, it depends on each other, and the corresponding foreign key table will be updated when the main table is modified to ensure the integrity of the data.

Cascade update involves data proofreading: 1, timely proofreading 2, delaying proofreading actually there is no direct update in Oracle, then I can set it to delay proofreading when setting up a foreign key, note that it does not mean that there is no proofreading, it will still be checked after the commit transaction is submitted.

Format:

Alter table student add constraint fk_two foreign key (id) references teacher (id) deferable initially deferred

Explanation:

Then modify the table student to add the foreign key set to deferable to control whether to delay the check, initially keyword selection has two parameters 1, immediate 2, deferred two.

Format:

Alter table student add constraint fk_three foreign key (id) references teacher (id) on delete cascad

Explanation:

So this is a cascading delete operation on delete cascad is the keyword

Format:

Alter table student modify constraint id enable novalidate

Explanation:

This is actually very important, because when we sometimes disable foreign keys and insert some data, we find that we cannot open foreign keys because the data integrity is inconsistent, so we use the novalidate keyword to only verify the modified data generated later, skip the previously inserted data, and it is only a temporary state.

Alter table student drop constraint foreign key name;-- delete foreign key

What on earth is the use of foreign keys? so long as it is an ideal choice from the above sentences, as long as it is an ideal choice in some parent-son tables with relatively strict specifications, similarly, in the application program, some data constraints that foreign keys can do well should be transferred to the foreign key constraints of the table as much as possible, because it is difficult to control the changes brought about by foreign key constraints in the development application, and it is also necessary to prohibit excessive use of foreign keys, which will affect the readability of the design. And a large number of will also affect the performance of the database, for a large number of data objects, but also for sub-objects to establish objects and deal with them accordingly.

Uniqueness constraint

The primary key is the only guarantee in the table, so why should there be a uniqueness constraint?

So there are some scenarios, such as our ideal table where the id code will automatically grow as the primary key, but there is only one primary key, so extra requirements are needed for business and other logical relationships. Strictly speaking, the primary key is designed to uniquely identify a record, and uniqueness is designed to constrain to ensure the unique value of the column itself.

Format:

Alter table student add constraint un_one unique (column)

Explanation:

Keyword unique actually read so many SQL constraint statements, we will find that the format is more or less the same, disable turn on, change the name is a box to drive out

The uniqueness constraint is supplemented by the primary key

Check constraint

The last constraint introduced, which is better understood by friends who have learned programming, is the control of the code flow. If you check the car when operating the database according to my wishes, if it does not conform to the requirement, the operation will fail, then the car inspection operation in Oracle is to calculate the Boolean value to judge whether it is true or false.

Format:

Create table student (id number not nul primary key,grade varchar (20), sal number (20) not null,constraint chk_one chkeck (grade in ('boss', 'manager', 'technical worker') and (grade=' boss' and sal > = 10000 or grade=' manager 'and sal

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.