Complete constraint 3 (learning notes): constraint learning notes

Complete constraint 3 (learning notes): constraint learning notes
Primary foreign key constraint FK

Foreign key (FK)

CONSTRAINT fk _ foreign key name foreign key (column name) REFERENCES table 2 (column name) -- General table 2 primary KEY

 

Delete drop table member purge first;

 

Create Table-member

Create table member (mid NUMBER, NAME VARCHAR2 (20) not null, -- CREATE a primary key constraint PK_mid primary key (mid ));

Add data

Insert into member (mid, NAME) VALUES (1, 'zhang Wuji '); insert into member (mid, NAME) VALUES (2, 'zhang Sanfeng ');

Create another table suggestion table

CREATE TABLE advice(      adid NUMBER,      content CLOB NOT NULL,      mid NUMBER,      CONSTRAINT PK_adid PRIMARY KEY(adid));

Add data for avvice

Insert into advice (adid, content, mid) VALUES (1, 'internal communication mechanism should be promoted and the President's mailbox should be set', 1); insert into advice (adid, content, mid) VALUES (2, 'all department leaders should take up their posts for the good internal development of the company ', 1); insert into advice (adid, content, mid) VALUES (3, 'You need to carry out more employee training activities to give employees a more sense of belonging ', 1); insert into advice (adid, content, mid) VALUES (4,' should carry out diversified businesses, more satisfying market demands ', 2); insert into advice (adid, content, mid) VALUES (5,' vigorously develop the technical department and design your own ERP system for the company, adapt to electronic information development requirements ', 2); COMMIT; -- Query advice table SELECT * FROM advice;

 

Query the complete information of each member and the number of comments

SELECT m. mid, m. name, COUNT (. mid) from member m, advice aWHERE m. mid =. midGROUP BY m. mid, m. name; we can see that the correct statistics show that the value range of MID in advice should be determined by the mid content in the member table.

Add a data entry in advice that is not in the mid range of member.

Insert into advice (adid, content, mid) VALUES (6, 'Post responsibility transparent ', 99); -- Query advice table SELECT * FROM advice; -- can be added successfully

Specify the master-Foreign key constraint

--Delete first

DROP TABLE MEMBER PURGE;DROP TABLE advice PURGE;

Create the same table

Create table member (mid NUMBER, NAME VARCHAR2 (20) not null, -- CREATE a primary key constraint PK_mid primary key (mid )); -- create table advice (adid NUMBER, content clob not null, mid NUMBER, CONSTRAINT PK_adid primary key (adid), CONSTRAINT FK_mid foreign key (mid) references member (mid); -- add data insert into member (mid, NAME) VALUES (1, 'Liu nance'); insert into member (mid, NAME) VALUES (2, 'Dong Fangzhuo '); -- Query SELECT * from member; -- add data insert into advice (adid, content, mid) VALUES (1,' internal communication mechanism should be promoted, set the president's mailbox ', 1); insert into advice (adid, content, mid) VALUES (2,' for the sound internal development of the company, all department leaders should take up their posts again ', 1 ); insert into advice (adid, content, mid) VALUES (3, 'more employee training activities should be carried out to make employees more interested. ', 1); insert into advice (adid, content, mid) VALUES (4, 'should carry out diversified businesses to better meet market demand ', 2); insert into advice (adid, content, mid) VALUES (5, 'vigorously develop the technical department, design your own ERP system for the company, and adapt to the requirements of electronic information development', 2); COMMIT; -- Query advice table SELECT * FROM advice;

Add a data entry in advice that is not in the mid range of member.

Insert into advice (adid, content, mid) VALUES (6, 'transparent job responsibilities ', 99 );

The error message cannot be added.

When deleting a parent table, you must first Delete the data of the child table.

Example: delete data numbered 1 in member

 

DELETE FROM MEMBER WHERE mid=1;

 

An error is reported.

Cannot be deleted

You need to DELETE the data of mid = 1 in the sub-Table advice and then DELETE the data of mid = 1 in the parent table member FROM advice WHERE mid = 1; delete from member where mid = 1;

You can delete

You can also set cascade when creating a table.

You can set cascading deletion when creating a table -- set on delete cascade after setting the foreign key

--Delete first

DROP TABLE advice PURGE;DROP TABLE MEMBER PURGE;

Create table member (mid NUMBER, NAME VARCHAR2 (20) not null, -- CREATE a primary key constraint PK_mid primary key (mid )); -- create table advice (adid NUMBER, content clob not null, mid NUMBER, CONSTRAINT PK_adid primary key (adid ),CONSTRAINT FK_mid foreign key (mid) references member (mid) on delete cascade -- primary foreign key constraint add cascading Deletion);

In this way, you can directly Delete the records in the parent table.

However, when querying the sub-table, the records in the sub-table are also deleted.

You can use

Add on delete set null after the foreign key

-- Drop table advice; drop table member; -- create table member (mid NUMBER, NAME VARCHAR2 (30) not null, CONSTRAINT PK_mid primary key (mid )); create table advice (adid NUMBER, content clob not null, mid NUMBER, CONSTRAINT PK_adid primary key (adid), CONSTRAINT FK_mid foreign key (mid) references member (mid) on delete set null -- SET the column related to the sub-table records to be deleted as NULL );

In this way, you can directly Delete the records in the parent table.

However, when querying the sub-table, the records in the sub-table are not deleted, but the related field is NULL.

Force Delete table

Drop from member where mid = 1 cascade constraint;

The parent table is deleted directly, and the sub-table content still exists.

View Constraints

SELECT constraint_name, constraint_type, table_name FROM user_constraints; -- view the constraints of the EMP table SELECT constraint_name, constraint_type, table_nameFROM user_constraintsWHERE table_name = 'emp'

View the user_cons_columns data dictionary select * from user_cons_columns;

Modify Constraints

 

Add constraint syntax

 

ALTER TABLETable Name

 

ADD CONSTRAINTConstraint nameConstraint type (Constraint field)

Drop table advice; drop table member; -- create table member (mid NUMBER, NAME VARCHAR2 (30); create table advice (adid NUMBER, content clob, mid NUMBER); -- add the primary key constraint alter table memberadd constraint PK_mid primary key (mid) for the mid field of the member TABLE ); -- add CHECK constraints to the member age field -- first add the age field alter table memberadd (age NUMBER); SELECT * from member; alter table memberadd constraint ck_age CHECK (age BETWEEN 0 AND 200 ); -- check the SELECT constraint_name, constraint_type, table_nameFROM User_Constraints WHERE table_name = 'member'; -- set non-empty alter table membermodify (name not null) for the NAME field );

Enable and disable Constraints

 

Enable and disable Constraints

 

ALTER TABLETable Name ENABLE | DISABLE CONSTRAINTConstraint name [CASCADE]

-- Cascade disable constraints with integrity relationships (mainly foreign keys)

-- DISABLE the primary key constraint alter table advice disable constraint pk_adid in advcie; -- DISABLE the primary key constraint alter table member disable constraint pk_mid in the memeber TABLE; -- enable constraint alter table name enable constraint name -- ENABLE the pk_mid primary key constraint alter table member enable constraint pk_mid -- delete constraint alter table Name drop constraint name [CASCADE] -- delete the pk_adid CONSTRAINT in advcie-independent foreign key alter table advice drop constraint pk_adid; -- delete pk_mid in member-alter table member drop constraint pk_mid CASCADE;