Oracle constraint belongs to an object of database

In order to facilitate the maintenance of constraints, Oracle saves all constraints as independent database objects, and these information are also stored in the data dictionary, so each constraint needs its own noun to maintain.

When the user does not set a name for the constraint, the system automatically assigns a name to the constraint. The system automatically assigns a constraint name, for example:

c0010201 is the system allocation.

When building a table, you can specify a name for the constraint through constrain.

Nonempty Constraints (NK)

Resolution: If a field cannot be null and data must exist, a non-null constraint can be used; keyword: not null
Example 1: Define the member table to set non-null constraints for the name field

DROP TABLE member PURGE ;
CREATE TABLE member(
    mid     NUMBER   ,
    name    VARCHAR2(200)   NOT NULL 
) ;

Example 2: Add the correct data to the member table to insert

INSERT INTO member(mid,name) VALUES (1,'hey') ;


Example 3: Adding incorrect data to the member table (the same results are obtained for both statements)

- Clearly set the name field to null:	

INSERT INTO member(mid,name) VALUES (3,null) ;
--Not set up name The content of the field:	

INSERT INTO member(mid) VALUES (3) ;


In violation of the non-null constraint, the error message will prompt "user name", "table name", "field name" and other clear information to inform the user where the error occurred.

Unique constraint

Resolution: Data in tables is not allowed to duplicate, keyword unique

DROP TABLE member PURGE ;
CREATE TABLE member(
    mid     NUMBER   ,
    name    VARCHAR2(200)   NOT NULL ,
    email   VARCHAR2(50)    UNIQUE
) ;

Error prompt: If duplicate data is inserted, the prompt does not identify the specific location.

Reason analysis: This is because the current program does not specify a specific name for the constraint

Example 2: Specify a name for a unique constraint

The suggested name format is: "Constraint Abbreviation Field"

DROP TABLE member PURGE ;
CREATE TABLE member(
    mid     NUMBER   ,
    name    VARCHAR2(200)    NOT NULL ,
    email   VARCHAR2(50)  ,
    CONSTRAINT uk_email      UNIQUE (email)
) ;

Primary key constraint

Primary key constraint = non-null constraint + unique constraint, keyword: primary key

DROP TABLE member PURGE ;
CREATE TABLE member(
    mid     NUMBER          PRIMARY KEY ,
    name    VARCHAR2(200)   NOT NULL ,
    email   VARCHAR2(50)  ,
    CONSTRAINT uk_email UNIQUE (email)
) ;

Check constraint

Primary and foreign key constraints

Insert the correct data -- insert two member information into the member table

INSERT INTO member (mid,name) VALUES (1,'hey') ; 
INSERT INTO member (mid,name) VALUES (2,'yan') ;
COMMIT ;

Insert the correct data -- insert five new records into the advice table

INSERT INTO advice (adid,content,mid) VALUES (1,'Internal communication mechanism should be advocated and CEO's mailbox should be set up.',1) ;
INSERT INTO advice (adid,content,mid) VALUES (2,'In order to make a sound development within the company, all department leaders should re-apply for posts.',1) ;
INSERT INTO advice (adid,content,mid) VALUES (3,'More staff training activities should be carried out to make employees feel more belonging.',1) ;
INSERT INTO advice (adid,content,mid) VALUES (4,'We should carry out diversified business to meet market demand more',2) ;
INSERT INTO advice (adid,content,mid) VALUES (5,'Vigorously develop technical departments to design their own for our company ERP Systems to meet the requirements of the development of electronic information',2) ;
COMMIT ;

After inserting the data, we need to verify the validity of the above data through a complex query of the data table.

SELECT m.mid,m.name,COUNT(a.mid)
FROM member m,advice a 
WHERE m.mid=a.mid 
GROUP BY m.mid,m.name ;

Suggestions are put forward by members, so the range of mid values in the suggestion table should be determined by the content of mid fields in the member table, that is, the mid set in the advice table must be the content in the mid column of the member table. However, there is no such restriction in the table relationship set up at this time, so it is suggested that the table can insert the wrong data, that is, there is no mid record in the membership table;

INSERT INTO advice (adid,content,mid) VALUES (6,'Transparency of post responsibilities',99) ;

In the membership table, there is no 99 field value. To solve this problem, primary and foreign key constraints are needed.

The preceding example adds primary and foreign key constraints:

Change the table structure to specify primary-foreign key constraints: through foreign Key sets a foreign key constraint (mid)

Primary foreign key constraint abbreviation:
If you do not need to specify the name of the constraint when writing a foreign key constraint, you can abbreviate it as follows:
mid number referencesmember(mid),

Insert the wrong data into the advice table -- there is no mid=99 data in the member table at this time

INSERT INTO advice (adid,content,mid) VALUES (6,'Transparency of post responsibilities',99) ;