Oracle note (10) Constraints

Oracle notes (10) Constraints related links: Oracle notes (1) Oracle introduction and installation http://www.bkjia.com/database/201209/154049.html#oraclenotes (2) SQLPlus command explain (3) Scott user's table structure explain (4) simple query, limited query, data sorting http://www.bkjia.com/database/201209/154054.html#oraclenotes (5) Single Row function (6) Multi-Table query http://www.bkjia. Com/database/201209/154060 .html; Oracle notes (7) data update, transaction processing, data pseudo columns http://www.bkjia.com/database/201209/154062.html#oraclenotes (8) complex query and summary (9) although the creation and management of the http://www.bkjia.com/database/201209/154316.html table is complete, the data in the table is legal and cannot be checked, if you want to filter the data in the table, the constraints can be used to ensure the validity of the data in the table. According to the constraints, there are five constraints: non-empty constraint, unique constraint, primary key constraint, check constraint, foreign key constraint. 1. non-NULL constraint (NOT null): When the content of a field in the data table does NOT want to be set to NULL, you can use not null to specify. Example: define a data table drop table member PURGE; create table member (mid NUMBER, name VARCHAR2 (50) not null );
Because the "not null" constraint exists, the following two groups of data are inserted. Example: correct data insert into member (mid, name) VALUES (1, 'zhang san'); insert into member (mid, name) VALUES (null, 'Li si '); insert into member (name) VALUES ('wang 5'); example: INSERT the wrong data www.2cto.com insert into member (mid, name) VALUES (9, null ); insert into member (mid) VALUES (10); the error message: ORA-01400: Unable to insert null ("SCOTT ". "MEMBER ". "NAME") in this program, the "user" is directly displayed ". "Table name ". "field" error.
Ii. UNIQUE constraint (UNIQUE): the UNIQUE constraint of UK indicates that data in each column cannot be duplicated. For example, each user of an email address must not be duplicated, then the unique constraint is used. Drop table member PURGE; create table member (mid NUMBER, name VARCHAR2 (50) not null, email VARCHAR2 (50) UNIQUE); example: INSERT correct data INTO member (mid, name, email) VALUES (1, 'zhang san', 'mchina _ tang@qq.com '); insert into member (mid, name, email) VALUES (2, 'Li si', null ); example: INSERT wrong data -- duplicate data insert into member (mid, name, email) VALUES (3, 'wang wu', 'mchina _ tang@qq.com ');
The following error message appears: ORA-00001: A unique constraint violation (SCOTT. SYS_C005272) but the error prompt at this time is not perfect compared with the previous non-empty constraint, because only a code name is provided, this is because no name is specified for the constraint when defining the constraint, so it is assigned by the system by default. The recommended format of the constraint name is "constraint type _ field". For example: "UK_email", specifying the CONSTRAINT name to use CONSTRAINT. Drop table member PURGE; create table member (mid NUMBER, name VARCHAR2 (50) not null, email VARCHAR2 (50), CONSTRAINT UK_email UNIQUE (email )); when the error data is added again after www.2cto.com, the following message is prompted: ORA-00001: violation of the unique constraints (SCOTT. UK_EMAIL) You can clearly indicate the location where the user is wrong.
3. Primary Key constraint (Primary Key): PK Primary Key constraint = non-empty constraint + unique constraint. It can be set to null when the unique constraint is set, however, if the primary key constraint is used, the primary key cannot be blank. The primary key is generally used as a unique identifier of the data. For example, the ID of a person. Example: CREATE a primary key constraint drop table member PURGE; create table member (mid number primary key, name VARCHAR2 (50) not null); example: add the correct data insert into member (mid, name) VALUES (1, 'zhangsan'); example: incorrect data -- set the primary key to nullINSERT INTO member (mid, name) VALUES (null, 'zhangsan'); error message, the error message is the same as that of the non-empty constraint;
ORA-01400: cannot insert NULL ("SCOTT ". "MEMBER ". "MID") Example: incorrect data-primary key repeated insert into member (mid, name) VALUES (1, 'zhang san'); error message, this error message is the unique constraint error message, but the information is not clear, because the name is missing. ORA-00001: violation of the unique constraints (SCOTT. SYS_C005276) So for ease of use of constraints, the following is a name for the primary key constraint. Drop table member PURGE; create table member (mid NUMBER, name VARCHAR2 (50) not null, CONSTRAINT pk_mid primary key (mid ));
When you insert data repeatedly, the error message is: ORA-00001: A unique constraint violation (SCOTT. PK_MID) from the perspective of normal development, a table generally has only one primary key, but from the SQL syntax, a table can have multiple primary keys, this method is called a composite primary key. For example, see the following code: drop table member PURGE; www.2cto.com create table member (mid NUMBER, name VARCHAR2 (50) not null, CONSTRAINT pk_mid primary key (mid, name); in the use of a composite primary key, duplicate data is called only when the content of both fields is the same. Example: INSERT the correct data INTO member (mid, name) VALUES (1, 'zhang san'); insert into member (mid, name) VALUES (1, 'Li si '); insert into member (mid, name) VALUES (2, 'Lee 4 ');
Example: INSERT the wrong data insert into member (mid, name) VALUES (1, 'zhang san'); error message: ORA-00001: violation of the unique constraints (SCOTT. PK_MID) but from the development perspective, the composite primary key is generally not used, so this knowledge is only used as an introduction to its related content. As long as it is a data table, only one primary key is always set. Iv. Check constraints: the CK Check constraint adds some filtering conditions for the data in the table. For example, when the age is set, the range is: 0 ~ 200; set the gender to male and female. Example: Set the check constraint drop table member PURGE; create table member (mid NUMBER, name VARCHAR2 (50) not null, sex VARCHAR2 (10) not null, age NUMBER (3), CONSTRAINT pk_mid primary key (mid), CONSTRAINT ck_sex CHECK (sex IN ('male', 'female ')), CONSTRAINT ck_age CHECK (age BETWEEN 0 AND 200); example: add the correct data insert into member (mid, name, sex, age) VALUES (1, 'zhang san ', 'mal', '26'); example: Add wrong gender-ORA-02290: Violation check constraints (SCOTT. CK_SEX ) Insert into member (mid, name, sex, age) VALUES (2, 'lily', 'non', '26'); example: Increase the wrong age-ORA-02290: violation of check constraints (SCOTT. CK_AGE) insert into member (mid, name, sex, age) VALUES (2, 'Lee 4', 'female ', '123 '); the check operation is to filter the input data.
5. The four types of constraints before the primary-foreign key constraint www.2cto.com are performed in a single table, while the primary-foreign key constraint is performed in two tables, the two tables have a parent-child relationship, that is, the value range of a field in the child table is determined by the parent table. For example, it is now required to indicate a relationship. Each person has multiple books, and two data tables should be defined: member (master), book (sub); drop table member PURGE; drop table book PURGE; create table member (mid NUMBER, name VARCHAR2 (50) not null, CONSTRAINT pk_mid primary key (mid); create table book (bid NUMBER, title VARCHAR2 (50) not null, mid NUMBER, CONSTRAINT pk_bid primary key (bid); at this time, only two independent data tables are created as required, then insert several data entries below: insert into member (mid, name) VALUES (1, 'zhang san'); insert into member (m Id, name) VALUES (2, 'Li si'); insert into book (bid, title, mid) VALUES (101, 'java developer', 1 ); insert into book (bid, title, mid) VALUES (102, 'java Web developer', 2); insert into book (bid, title, mid) VALUES (103, 'ejb developers', 2); insert into book (bid, title, mid) VALUES (105, 'android developer', 1); insert into book (bid, title, mid) VALUES (107, 'ajax developer', 1); the simplest way to verify whether the data is meaningful is to write two queries. Example: count the number of books owned by each person: www.2cto.com SELECT m. mid, m. name, COUNT (B. bid) FROM member m, book bWHERE m. mid = B. midGROUP BY m. mid, m. name; example: query the number, name, and name of each person. SELECT m. mid, m. name, B. titleFROM member m, book bWHERE m. mid = B. mid;
That is, the current book. the mid field should be associated with member. the mid field is associated, but this program does not have any restrictions, so now the following data can be added: insert into book (bid, title, mid) VALUES (108, 'photoshop user Manual', 3); insert into book (bid, title, mid) VALUES (109, 'flex development Manual', 8); now two new records are added, and the records can be stored in the data table, but the two records do not make sense, because member. the content of the mid field is not 3 or 8. To solve this problem, the foreign key constraint must be used to solve the problem. Let the value of the book. mid field be determined by member. mid. If the data of member. mid actually exists, it means it can be updated. Www.2cto.com drop table member PURGE; drop table book PURGE; create table member (mid NUMBER, name VARCHAR2 (50) not null, CONSTRAINT pk_mid primary key (mid )); create table book (bid NUMBER, title VARCHAR2 (50) not null, mid NUMBER, CONSTRAINT pk_bid primary key (bid), CONSTRAINT fk_mid foreign key (mid) REFERENCES member (mid )); at this point, just add a constraint so that if the input data has an error, the following prompt will appear: ORA-02291: violation of the complete constraints (SCOTT. FK_MID)-Not Find the parent keyword because member. mid does not have the specified data, so book. mid cannot be updated if the data is incorrect. The biggest advantage of using a foreign key is that it controls the value range of some data in the subtable, but it also brings a lot of problems. 1. When deleting data, if the data in the primary table has corresponding sub-table data, it cannot be deleted. Example: DELETE data with the mid value of 1 in the member table: delete from member WHERE mid = 1. error message: "ORA-02292: violating complete constraints (SCOTT. FK_MID)-The subrecord has been found ".
In this case, only the sub-table records can be deleted first, and then the parent table records can be deleted: delete from book WHERE mid = 1; delete from member WHERE mid = 1; but this operation is obviously inconvenient, if you want to delete the data in the primary table and the corresponding data in the subtable can also be deleted, you can specify a cascading deletion function when setting up a foreign key constraint, modify the database creation Script: www.2cto.com drop table member PURGE; drop table book PURGE; create table member (mid NUMBER, name VARCHAR2 (50) not null, CONSTRAINT pk_mid primary key (mid )); create table book (bid NUMBER, title VARCHAR2 (50) not null, mid NUMBER, CONSTRAINT Pk_bid primary key (bid), CONSTRAINT fk_mid foreign key (mid) REFERENCES member (mid) on delete cascade); in this case, due to the cascade delete operation, therefore, after the data in the primary table is deleted, the data in the corresponding sub-table is also deleted. 2. When deleting data, set the corresponding data in the subtable to null. When the data in the primary table is deleted, the corresponding data items in the subtable also want to be set to null, instead of deleting the data TABLE, you can continue to modify the TABLE creation Script: drop table member PURGE; drop table book PURGE; create table member (mid NUMBER, name VARCHAR2 (50) not null, CONSTRAINT pk_mid primary key (mid); create table book (bid NUMBER, title VARCHAR2 (50) not null, mid NUMBER, CONSTRAINT pk_bid primary key (bid ), CONSTRAINT fk_mid foreign key (mid) REFERENCES member (mid) on delete set null); www.2cto.com
Insert into member (mid, name) VALUES (1, 'zhang san'); insert into member (mid, name) VALUES (2, 'Li si'); insert into book (bid, title, mid) VALUES (101, 'java developer', 1); insert into book (bid, title, mid) VALUES (102, 'java Web developer', 2 ); insert into book (bid, title, mid) VALUES (103, 'ejb developer', 2); insert into book (bid, title, mid) VALUES (105, 'android developer', 1); insert into book (bid, title, mid) VALUES (107, 'ajax developer', 1 ); 3. before deleting a parent table, you must first Delete the corresponding child table. Otherwise, you cannot delete DRO. P table book PURGE; drop table member PURGE; but this is obviously troublesome, because for an unknown database, if you want to follow this method, you must first know its parent-child relationship, therefore, Oracle provides a special mandatory table deletion operation, that is, you do not care about the constraints. When you delete a table, write "cascade constraint ". Drop table member cascade constraint purge; drop table book cascade constraint purge; in this case, you do not care whether the Sub-TABLE exists or not and delete the parent TABLE forcibly. Reasonable practice: When deleting a data table in the future, it is best to delete the child table before deleting the parent table.
6. Modify the constraint itself is also a database object, so you can certainly modify the constraint. As long as the modification uses the ALTER command, the modification of the constraint mainly refers to the following two operations: ADD constraints to the TABLE: alter table name add constraint name CONSTRAINT type (field); Delete constraints in the TABLE: alter table Name drop constraint name; you can find that, to maintain constraints, you must have a correct name. However, among the five constraints, non-null constraints cannot be operated as a special constraint. The following table is available: drop table member cascade constraint purge; create table member (www.2cto.com mid NUMBER, name VARCHAR2 (50) not null, age NUMBER (3 ));
Example: ADD the primary key constraint alter table member add constraint pk_mid primary key (mid) for the TABLE; ADD data: insert into member (mid, name, age) VALUES (1, 'zhang san ', 30); insert into member (mid, name, age) VALUES (2, 'lily', 300); now there is illegal age data in the member table, therefore, the following section adds a check constraint for the member TABLE: alter table member add constraint ck_age CHECK (age BETWEEN 0 AND 250); at this time, data that violates the constraints already exists in the TABLE, so it cannot be added. Example: Delete the primary key CONSTRAINT www.2cto.com alter table member drop constraint pk_mid on the mid in the member TABLE. However, like the TABLE structure, it is best not to modify the CONSTRAINT. Remember, when creating a table, you must define the constraints. We recommend that you do not change them in future use. VII. query constraints all objects in Oracle will be saved in the data dictionary, and the constraints are the same. Therefore, if you want to know the constraints, you can directly query the "user_constraints" data dictionary: SELECT owner, constraint_name, table_name FROM user_constraints; but the query constraint only tells you the name, but does not tell which field has this constraint, therefore, you can view another data dictionary table "user_cons_columns"; COL owner FOR A15; COL constraint_name FOR A15; COL table_name FOR A15; COL column_name FOR A15; SELECT owner, constraint_name, table_name, column_name FROM user_cons_columns; The DBA is responsible.