[Oracle] constraints and Data Pump Import and Export

--: Create a user. The following operations are performed under the current user (for example, the user name is test) createusertestidentifiedbytest; --: Enter the sys user and create the emp and dept tables createtabletest under test respectively. empasselect * fromscott. emp; createtabletest. deptasselect * fromscott.

--: Create a user. The following operations are performed under the current user (for example, the user name is test) create user test identified by test; --: Enter the sys user, create table test for emp and dept tables under test respectively. emp as select * from scott. emp; create table test. dept as select * from scott.

--: Create a user. The following operations are performed under the user (for example, the user name is test)
Create user test identified by test;

--: Enter the sys user and create the emp and dept tables under test respectively.
Create table test. emp as select * from scott. emp;
Create table test. dept as select * from scott. dept;

-- Use the command to create a primary foreign key for the table above.
Alter table emp add constraints pk_empno primary key (empno );
Alter table dept add constraints pk_deptno primary key (deptno );
Alter table emp add constraints fk_de_em foreign key (deptno) references dept (deptno );

-- Export this user
-- 1. Connect to the Oracle database
SQL> conn/as sysdba

-- 2. Create an operation directory
SQL> create directory chenfeng_dir as 'd: \ North China 2014 \ 7.1 \ dump ';

-- Note that you also need to use the operating system command to create this physical directory on the hard disk.

-- The directory has been created.

-- 3. Assign the directory object my_dir permission to the user to export
SQL> grant read, write on directory chenfeng_dir to chenfeng;

C: \> expdp chenfeng/chenfeng directory = chenfeng_dir dumpfile = 20140701_schema_chenfeng.dmp schemas = chenfeng logfile = chenfeng. log;

--: Delete this user
SQL> drop user chenfeng cascade;

--: Import the user to check whether the user is successful.
-- User import ----------
-- 1. Connect to the Oracle database
SQL> conn/as sysdba

-- 2. Create an import operation directory
SQL> create directory chenfeng_dir as 'd: \ North China 2014 \ 7.1 \ dump ';

-- 3. Create a user and assign Permissions
SQL> create user chenfeng identified chenfeng;
SQL> grant connect, resource to chenfeng;
SQL> grant read, write on directory chenfeng_dir to chenfeng;

-- 4. Import users
C: \> impdp chenfeng/chenfeng directory = chenfeng_dir dumpfile = 20140701_schema_chenfeng.dmp schemas = chenfeng;

--------------------------------------------

-- Familiar with commands for Constrained operations:
-- Create a column, create a table, and create a table.

-- 1. primary key constraint
-- 1-when creating a column
Create table student (
Sno number primary key,
Sname varchar2 (20)
);
Drop table student;

-- 2-Table Creation Time
Create table student (
Sno number,
Sname varchar2 (20 ),
Primary key (sno)
);

-- 3-create a table.
Create table student (
Sno number,
Sname varchar2 (20)
);
Alter table student add constraints pk_s primary key (sno );

-- II. Foreign key addition Constraints
-- 1-when creating a column
Drop table teacher;

Create table teacher (
Tno number references student (sno ),
Tname varchar2 (20 ),
Primary key (tno)
);

-- 2-Table Creation Time
Create table teacher (
Tno number,
Tname varchar2 (20 ),
Constraints fk_t foreign key (tno) references student (sno)
);

-- 3-create a table.
Create table teacher (
Tno number,
Tname varchar2 (20)
);
Alter table teacher add constraints fk_t foreign key (tno) references student (sno );

-- 2. non-null Constraint
-- 1-when creating a column
Drop table teacher;

Create table teacher (
Tno number,
Tname varchar2 (20) not null
);

-- 2-after creating a table
Create table teacher (
Tno number,
Tname varchar2 (20)
);
Alter table teacher modify tname not null;

-- 2. Independent Constraint
-- 1-when creating a column

Drop table teacher;

Create table teacher (
Tno number unique,
Tname varchar2 (20)
);

-- 2-Table Creation Time
Create table teacher (
Tno number,
Tname varchar2 (20 ),
Constraints uq_t unique (tno)
);

-- 2-after creating a table
Create table teacher (
Tno number,
Tname varchar2 (20)
);
Alter table teacher modify tname unique;


-- 3.5 check constraints: Custom conditions that must be met by the user's table column data.

-- A. Define columns:
Create table tab (
Tid number,
Tage number constraint ck_tage check (tage> = 18)
);
-- B. Define a table:
Create table tab (
Tid number,
Tsex number (1 ),
Constraint ck_tage check (tsex in (0, 1 ))
);
-- C. Define the table and then define it:
Create table tab (
Tid number,
Tsex number (1 ));

Alter table tab add constraint ck_tage check (tsex in (1, 0 ));



-- View Constraints
Select constraint_name, constraint_type
From user_constraints where table_name = 'emp ';

-- Set "Cascading deletion" and "Cascading null" for foreign key constraints

Create table dept2 as select * from dept;
Alter table dept2 add constraint pk_dept2 primary key (deptno );
Create table emp2 as select * from emp;
Alter table emp2 add constraint pk_emp2 primary key (empno );

-- Delete Constraints
Alter table emp2 drop constraint fk_e2_d2;

-- Sets the external key constraint for cascading deletion.
Alter table emp2 add constraint fk_e2_d2 foreign key (deptno) references dept2 (deptno) on delete cascade;
-- Set the external key constraint for cascading null
Alter table emp2 add constraint fk_e2_d2 foreign key (deptno) references dept2 (deptno) on delete set null;

Delete dept2 where deptno = 20;



-- 4. View -------------------------

-- Grant scott user view permissions.
Grant create view to scott;
-- View: without physical data, only a virtual data source for query is bound.

-- Create a view:
Create or replace view v_dept as select * from dept;


Update dept set dname = lower (dname );


-- View classification:
-- 1 simple view: A subquery involves only one table and has no data variations.
-- View data can correspond to the ROWID of physical data

-- Simple view: you can modify physical data by performing DML operations on The View.

Update v_dept set dname = upper (dname );

-- 2 complex view: A subquery involves multiple tables or data variants.

-- For a complex view, you cannot modify physical data by performing DML operations on The View.