Oracle Notes (10) constraints

Oracle Notes (10) constraints

Although the table is completed, but the data in the table can not be checked, and if you want to do some filtering for the data in the table, it can be done by the constraints, the main function of the constraint is to ensure the data in the table legitimacy, according to the classification of constraints, there are five constraints: non-null constraints, UNIQUE constraints, PRIMARY KEY constraints Check constraints, FOREIGN KEY constraints.

one, non-null constraints ( Not NULL ): NK

You can specify with NOT NULL when the contents of a field on a datasheet do not want to be set to null.

Example: Defining a data table

DROP TABLE member PURGE;

CREATE TABLE Member (

Mid number,

Name VARCHAR2 (not NULL)

);

Because the "not NULL" constraint exists at this point, two sets of data are inserted below.

Example: the right data

INSERT into member (Mid,name) VALUES (1, ' Zhang San ');

INSERT into member (MID,NAME) VALUES (null, ' John Doe ');

INSERT into member (name) VALUES (' Harry ');

Example: inserting the wrong data

INSERT into member (Mid,name) VALUES (9, NULL);

INSERT into member (mid) VALUES ();

At this point, the error message appears:

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

In this procedure, the "user" is indicated directly. Table name "." Field "An error occurred.

Second, the only constraint ( UNIQUE ): UK

The only constraint is that the data on each column is not allowed to be duplicated, for example: The email address is definitely not duplicated for each user, so it is done with a unique constraint.

DROP TABLE member PURGE;

CREATE TABLE Member (

Mid number,

Name VARCHAR2 () not NULL,

Email VARCHAR2 (UNIQUE)

);

Example: inserting the correct data

INSERT into member (Mid,name,email) VALUES (1, ' Zhang San ', ' [email protected] ');

INSERT into member (Mid,name,email) VALUES (2, ' John Doe ', NULL);

Example: inserting the wrong data--repeating data

INSERT into member (Mid,name,email) VALUES (3, ' Harry ', ' [email protected] ');

The following error message appears:

ORA-00001: Violates the unique constraint (SCOTT. sys_c005272)

However, this time the error hint compared with the previous non-empty constraint is not perfect, because now only give a code, this is because the constraint is not assigned a name at the time of the constraint, so the system is assigned by default, and the constraint of the name of the proposed format "constraint type _ Field", for example: "Uk_ Email ", specify the constraint name to complete using constraint.

DROP TABLE member PURGE;

CREATE TABLE Member (

Mid number,

Name VARCHAR2 () not NULL,

Email VARCHAR2 (+),

CONSTRAINT uk_email UNIQUE (email)

);

When you add the error data again later, you are prompted with the following information:

ORA-00001: Violation of the unique constraint (scott.uk_email)

The location of the user error can already be clearly indicated.

third, the PRIMARY KEY constraint ( Primary Key ): PK

PRIMARY KEY constraint = non-null constraint + UNIQUE constraint, the discovery can be set to NULL when a unique constraint was previously set, and if the primary KEY constraint is now used, it cannot be empty, and the primary key usually appears as a unique token of the data, for example: ID of the person.

Example: establishing a PRIMARY KEY constraint

DROP TABLE member PURGE;

CREATE TABLE Member (

Mid number PRIMARY KEY,

Name VARCHAR2 (not NULL)

);

Example: adding the right data

INSERT into member (Mid,name) VALUES (1, ' Zhang San ');

Example: bad data--primary key set to NULL

INSERT into member (MID,NAME) VALUES (null, ' Zhang San ');

The error message is the same as the error message in the previous non-null constraint;

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

Example: bad data--primary key duplication

INSERT into member (Mid,name) VALUES (1, ' Zhang San ');

Error message, this error message is the only constraint error message, but the information is ambiguous, because the name is not.

ORA-00001: Violates the unique constraint (SCOTT. sys_c005276)

Therefore, in order to facilitate the use of constraints, the following primary key constraints a name.

DROP TABLE member PURGE;

CREATE TABLE Member (

Mid number,

Name VARCHAR2 () not NULL,

CONSTRAINT Pk_mid PRIMARY KEY (mid)

);

At this point, the data is repeatedly inserted, the error message is as follows:

ORA-00001: Violates the unique constraint (SCOTT. PK_MID)

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

DROP TABLE member PURGE;

CREATE TABLE Member (

Mid number,

Name VARCHAR2 () not NULL,

CONSTRAINT pk_mid PRIMARY KEY (mid,name)

);

In the use of composite primary keys, only the contents of two fields are the same, which is called duplicate data.

Example: inserting the correct data

INSERT into member (Mid,name) VALUES (1, ' Zhang San ');

INSERT into member (Mid,name) VALUES (1, ' John Doe ');

INSERT into member (Mid,name) VALUES (2, ' John Doe ');

Example: inserting the wrong data

INSERT into member (Mid,name) VALUES (1, ' Zhang San ');

Error message:

ORA-00001: Violates the unique constraint (SCOTT. PK_MID)

However, from the actual point of view of development, generally do not use composite primary key, so this knowledge only as its related content to do an introduction. As long as it is a data table, always set only one primary key.

iv. CHECK Constraints ( Check ): CK

The check constraint refers to adding some filtering criteria to the data in the table, for example:

• When setting the age range is: 0~200;
• When setting the gender, it should be: male, female;

Example: setting a CHECK Constraint

DROP TABLE member PURGE;

CREATE TABLE Member (

Mid number,

Name VARCHAR2 () not NULL,

Sex VARCHAR2 (ten) not NULL,

Age Number (3),

CONSTRAINT Pk_mid PRIMARY KEY (mid),

CONSTRAINT ck_sex CHECK (Sex in (' Male ', ' female ')),

0 )

);

Example: adding the right data

INSERT into member (Mid,name,sex,age) VALUES (1, ' Zhang San ', ' male ', ' +');

Example: increasing the wrong gender--ora-02290: Violating CHECK constraints (SCOTT. Ck_sex)

INSERT into member (Mid,name,sex,age) VALUES (2, ' John Doe ', ' non ', ' n');

Example: increase the age of the error--ora-02290: violation of the CHECK constraint (SCOTT. Ck_age)

INSERT into member (Mid,name,sex,age) VALUES (2, ' John Doe ', ' female ', '260 ');

The check operation is to filter the input data.

v. Main - FOREIGN KEY Constraints

The previous four constraints are all in a single table, while the primary-foreign KEY constraint is in two tables that 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 show a relationship where each person has more than one book, and two data tables should be defined: Member (master), books (son);

DROP TABLE member PURGE;

DROP TABLE book PURGE;

CREATE TABLE Member (

Mid number,

Name VARCHAR2 () not NULL,

CONSTRAINT Pk_mid PRIMARY KEY (mid)

);

CREATE TABLE Book (

Bid number,

Title VARCHAR2 (not NULL),

Mid number,

CONSTRAINT pk_bid PRIMARY KEY (BID)

);

At this point only two separate data tables have been created as required, then a few data are inserted below:

INSERT into member (Mid,name) VALUES (1, ' Zhang San ');

INSERT into member (Mid,name) VALUES (2, ' John Doe ');

INSERT into book (bid,title,mid) VALUES (101, ' Java development ',1);

INSERT into book (bid,title,mid) VALUES (102, ' Java Web Development ',2);

INSERT into book (bid,title,mid) VALUES (103, ' EJB development ',2);

INSERT into book (bid,title,mid) VALUES ("Android Development",1);

INSERT into book (bid,title,mid) VALUES (107, ' Ajax development ',1);

The simplest way to verify that the data is meaningful is to write two queries.

Example: count the number of books each person owns

SELECT M.mid,m.name,count (b.bid)

From member M,book b

WHERE M.mid=b.mid

GROUP by M.mid,m.name;

Example: Query the number of each person, name, name of the owner of the book

SELECT M.mid,m.name,b.title

From member M,book b

WHERE M.mid=b.mid;

That is, the current Book.mid field should be associated with the Member.mid field, but since this program does not set constraints, the following data can now 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);

Two new records are now added, and the records can be saved in the data table, but these two records are meaningless because the contents of the Member.mid field are not 3 and 8, and to solve this problem you have to rely on foreign key constraints.

The value of the Book.mid field is determined by member.mid, and if the Member.mid data is true, it can be updated.

DROP TABLE member PURGE;

DROP TABLE book PURGE;

CREATE TABLE Member (

Mid number,

Name VARCHAR2 () not NULL,

CONSTRAINT Pk_mid PRIMARY KEY (mid)

);

CREATE TABLE Book (

Bid number,

Title VARCHAR2 (not NULL),

Mid number,

CONSTRAINT pk_bid PRIMARY KEY (BID),

CONSTRAINT Fk_mid FOREIGN KEY (mid) REFERENCES member (mid)

);

At this point, only one constraint is added, so if the input data has errors, the following prompt appears:

ORA-02291: Violation of the full constraint (SCOTT. FK_MID)-parent keyword not found

Because Member.mid does not have the specified data, Book.mid cannot perform the update operation if the data has errors.

The biggest advantage of using foreign keys is that it controls the range of values in the child tables, but it also brings a lot of problems.

1, when the data is deleted, if the data in the primary table has the corresponding sub-table data, it cannot be deleted;

Example: delete data from mid 1 in the member table

DELETE from member WHERE mid=1;

Error message: "ORA-02292: Violation of the full constraint (SCOTT. FK_MID)-Child records found.

At this point, you can delete only the child table records before you delete the parent table records:

DELETE from book WHERE mid=1;

DELETE from member WHERE mid=1;

However, this operation is obviously inconvenient, if you now want to delete the main table data, the corresponding data in the child table can also be deleted, you can create a foreign key constraint when you specify a cascading delete function, modify the database creation script:

DROP TABLE member PURGE;

DROP TABLE book PURGE;

CREATE TABLE Member (

Mid number,

Name VARCHAR2 () not NULL,

CONSTRAINT Pk_mid PRIMARY KEY (mid)

);

CREATE TABLE Book (

Bid number,

Title VARCHAR2 (not NULL),

Mid number,

CONSTRAINT pk_bid PRIMARY KEY (BID),

CONSTRAINT Fk_mid FOREIGN KEY (mid) REFERENCES member (mid) on DELETE CASCADE

);

The data in the corresponding child table is also deleted after the data in the primary table has been deleted because of the cascade delete operation.

2. When deleting data, let the corresponding data in the child table be set to NULL

When the data in the primary table is deleted, the data related items in the corresponding child table also want to be set to null instead of deleted, and you can continue to modify the creation script of the data table:

DROP TABLE member PURGE;

DROP TABLE book PURGE;

CREATE TABLE Member (

Mid number,

Name VARCHAR2 () not NULL,

CONSTRAINT Pk_mid PRIMARY KEY (mid)

);

CREATE TABLE Book (

Bid number,

Title VARCHAR2 (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, ' John Doe ');

INSERT into book (bid,title,mid) VALUES (101, ' Java development ',1);

INSERT into book (bid,title,mid) VALUES (102, ' Java Web Development ',2);

INSERT into book (bid,title,mid) VALUES (103, ' EJB development ',2);

INSERT into book (bid,title,mid) VALUES ("Android Development",1);

INSERT into book (bid,title,mid) VALUES (107, ' Ajax development ',1);

3, delete the parent table must first delete the corresponding child table, otherwise you cannot delete

DROP TABLE book PURGE;

DROP TABLE member PURGE;

However, this is obviously cumbersome, because for an unknown database, if you want to follow such a way, you must first know its parent-child relationship, so in Oracle dedicated to provide a mandatory delete table operation, that is: no longer concerned about the constraints, in the deletion of the time to write a sentence "CASCADE CONSTRAINT ".

DROP TABLE member CASCADE CONSTRAINT PURGE;

DROP TABLE book CASCADE CONSTRAINT PURGE;

At this point, do not care if the child table exists, the immediate mandatory deletion of the parent table.

Reasonable practice: In the future to delete the data table, it is best to delete the child table before deleting the parent table.

Vi. Modification of constraints

The constraints themselves also belong to the database object, then it is certainly possible to modify the operation, and as long as the modification is using the ALTER Directive, the constraint modification mainly refers to the following two operations:

• To add a constraint to a table:
• To delete a constraint in a table:

ALTER Table table name ADD CONSTRAINT constraint name constraint type (field);

ALTER table name DROP CONSTRAINT constraint name;

It can be found that if you want to maintain the constraints, it is necessary to have a correct name, but in these five constraints, the non-null constraint as a special constraint can not be manipulated, now has the following data table:

DROP TABLE member CASCADE CONSTRAINT PURGE;

CREATE TABLE Member (

Mid number,

Name VARCHAR2 () not NULL,

Age Number (3)

);

Example: adding 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 ',+);

INSERT into member (Mid,name,age) VALUES (2, ' John Doe ',+);

There is now an age-illegal data in the member table, so the following is an additional check constraint for the Member table:

0 );

This time there is already a violation of the constraint in the table data, so certainly cannot increase.

Example: Deleting a PRIMARY KEY constraint on a mid in a member table

ALTER TABLE member DROP CONSTRAINT Pk_mid;

However, as with the table structure, the constraints are best not to modify, and remember, the table is established at the same time must be bound to define the constraints, the future use of the recommendations do not change.

Vii. query constraints

All objects in Oracle are stored in the data dictionary, and the constraints are the same, so if you want to know what constraints you have, you can query the "user_constraints" data dictionary directly:

SELECT owner,constraint_name,table_name from User_constraints;

But the constraint of this query only tells your name, and does not tell you which field has this constraint, so 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 these maintenance work is done by dedicated DBAs.

Oracle Notes (10) constraints