Oracle basic knowledge note (10) Constraints

Although the table has been created, it cannot be checked whether the data in the table is legal. If you want to filter the data in the table, you can use the constraints, the main function of a constraint is to ensure the validity of the data in the table. According to the constraints, there are five types of constraints: non-empty constraints, unique constraints, primary key constraints, check constraints, and foreign key constraints.

1. non-NULL constraint (not null): NK

If you do NOT want to set the content of a field in the data table to null, you can use not null to specify the content.

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 wu ');

Example: insert incorrect data

INSERT INTO member(mid,name) VALUES(9,null);INSERT INTO member(mid) VALUES(10);

The following error message is displayed:

ORA-01400: cannot insert NULL ("SCOTT". "MEMBER". "NAME ")

In this program, the "user". "Table Name". "field" error occurs.

Ii. UNIQUE constraint (UNIQUE): UK

The unique constraint means that data in each column cannot be duplicated. For example, if each user of an email address is unique, 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

Insert 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 is displayed:

ORA-00001: violation of unique constraints (SCOTT. SYS_C005272)

However, the error message at this time is not perfect compared with the previous non-empty constraint, because only a code is provided, because no name is specified for the constraint when defining the constraint, therefore, it is allocated by default by the system, and the suggested format of the constrained name is "CONSTRAINT type _ field", for example, "UK_email". The specified CONSTRAINT name is completed using 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 later, the following message is displayed:

ORA-00001: violation of unique constraints (SCOTT. UK_EMAIL)

You can clearly indicate the location of a user error.

3. Primary Key constraint (Primary Key): PK

Primary key constraint = non-empty constraint + unique constraint. It can be set to null when you set a unique constraint. If the primary key constraint is used now, it cannot be blank, in addition, a 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 correct data

Insert into member (mid, name) VALUES (1, 'Zhang san ');

Example: incorrect data -- the primary key is set to null

Insert into member (mid, name) VALUES (null, 'zhang san ');

Error message, which is the same as the error message prompts with non-empty constraints;

ORA-01400: Unable to insert NULL ("SCOTT". "MEMBER". "MID ")

Example: incorrect data -- duplicate primary key

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 unique constraints (SCOTT. SYS_C005276)

Therefore, to facilitate the use of the constraints, the following is a name for the primary key constraints.

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 as follows:

ORA-00001: violation of unique constraints (SCOTT. PK_MID)

From the perspective of normal development, a table generally has only one primary key, but from the perspective of SQL syntax, a table can have multiple primary keys, which is called a composite primary key, for example, refer to the following code:

DROP TABLE member PURGE;CREATE TABLE member(    mid NUMBER,    name VARCHAR2(50) NOT NULL,    CONSTRAINT pk_mid PRIMARY KEY(mid,name));

In the use of compound primary keys, duplicate data is called only when the content of both fields is the same.

Example: insert correct data

Insert into member (mid, name) VALUES (1, 'Zhang san'); insert into member (mid, name) VALUES (1, 'Lee 4'); insert into member (mid, name) VALUES (2, 'Li si ');

Example: insert incorrect data

Insert into member (mid, name) VALUES (1, 'Zhang san ');

Error message:

ORA-00001: violation of unique constraints (SCOTT. PK_MID)

However, from the actual perspective of development, compound primary keys are 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: CK

Check constraints are used to add filtering conditions for table data, such:

• The age range is: 0 ~ 200;
• Set the gender to male and female;

  Example: Set check Constraints

  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 (age0AND200));

  Example: Add correct data

  Insert into member (mid, name, sex, age) VALUES (1, 'Zhang san', 'male ','26');

  Example: Add wrong gender -- ORA-02290: Violation check constraints (SCOTT. CK_SEX)

  Insert into member (mid, name, sex, age) VALUES (2, 'Lee 4', 'af ','26');

  Example: add the wrong age-ORA-02290: violation of check constraints (SCOTT. CK_AGE)

  Insert into member (mid, name, sex, age) VALUES (2, 'Lily', 'female ','260');

  The check operation is to filter the input data.

  V. Primary-foreign key constraints

  The preceding four constraints are implemented 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 sub-table is determined by the parent table.

  For example, it is now required to indicate a relationship. If each person has multiple books, two data tables should be defined: member (primary) and 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));

  In this case, two independent data tables are created as required. Insert the following data records:

  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 developing ',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

  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 who owns the book.

  SELECT m.mid,m.name,b.titleFROM member m,book bWHERE m.mid=b.mid;

  That is to say, the current book. mid field should be associated with the member. mid field, but the following data can also be added because there is no restriction set in this program:

  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 saved in the data table, but the two records are meaningless 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.

  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, only a constraint is added, so if the input data is incorrect, the following prompt will appear:

  ORA-02291: violating the complete constraint condition (SCOTT. FK_MID)-parent keyword not found

  Because no data is specified for member. mid, if the data in book. mid is incorrect, the update operation cannot be performed.

  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 master 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: a complete Constraint Violation (SCOTT. FK_MID)-subrecord found ".

  In this case, you can only delete the sub-Table record before deleting the parent table record:

  DELETE FROM book WHERE mid=1;DELETE FROM member WHERE mid=1;

  However, this operation is obviously inconvenient. If you want to delete the data in the primary table, the corresponding data in the subtable can also be deleted, you can specify a cascade delete function when setting up a foreign key constraint and modify the database 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 CASCADE);

  In this case, the data in the corresponding sub-table is also deleted after the data in the master table is deleted.

  2. When deleting data, set the data in the subtable to null.

  After the data in the primary table is deleted, you also want to set the data related items in the corresponding sub-table to null instead of deleting the data. In this case, you can continue to modify the data 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); 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 developing ',1);

  3. before deleting a parent table, you must first Delete the corresponding sub-table; otherwise, the sub-table cannot be deleted.

  DROP TABLE book PURGE;DROP TABLE member PURGE;

  However, 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, if you do not care whether the Sub-table exists, you can 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 Constraints

  The constraint itself is also a database object, so it can certainly be modified, and as long as it is modified, the ALTER command is used. The constraint modification mainly refers to the following two operations:

  • Add constraints to the table:

    Alter table name add constraint name CONSTRAINT type (field );

    • Delete constraints in a table:

      Alter table Name drop constraint name;

      We can find that to maintain constraints, a correct name is required. 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(    mid NUMBER,    name VARCHAR2(50) NOT NULL,    age NUMBER(3));

      Example: Add a primary key constraint to a table

      ALTER TABLE member ADD CONSTRAINT pk_mid PRIMARY KEY(mid);

      Add data:

      Insert into member (mid, name, age) VALUES (1, 'Zhang san ',30); Insert into member (mid, name, age) VALUES (2, 'Li si ',300);

      Invalid age data already exists in the member table. Therefore, the following section adds check constraints for the member table:

      ALTER TABLE member ADD CONSTRAINT ck_age CHECK(age BETWEEN 0 AND 250);

      At this time, there is already data in the table that violates the constraints, so it cannot be added.

      Example: Delete the primary key constraint on the mid in the member table

      ALTER TABLE member DROP CONSTRAINT pk_mid;

      However, like the table structure, it is best not to modify the constraints. Remember that when creating a table, you must define the constraints. We recommend that you do not change the constraints in future use.

      VII. query Constraints

      All objects in Oracle are 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;

      However, the query constraint only tells you the name, but does not tell which field has the 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;

      Most of the maintenance work is undertaken by a dedicated DBA.