Oracle 10g primary key latency

Source: Internet
Author: User

 

-- Create Table try as select * from student basic information; -- add the deferred primary key alter table try add constraint pk_try primary key (student ID) deferrable initially immediate; -- set the primary key delay set constraint pk_try deferred; -- Insert rows with duplicate primary keys

-- Create a table

Create Table try as select * from student basic information;

-- Add a delayed primary key

Alter table try add constraint pk_try primary key (student ID) deferrable initially immediate;

-- Set primary key latency

Set constraint pk_try deferred;

-- Insert rows with duplicate primary keys

Insert into try values ('20170101', 'zhang san', 'mal', 'Han nationality ');

-- Delete a dependency with a foreign keyPrimary Key

Alter table student basic information drop constraint pk_baseinfo cascade;

-- Add back

Alter table student basic information add constraint pk_baseinfo primary key (student ID );

-- AddDelayedForeign key

Alter table Student Score add constraint fk_info_grade foreign key (student ID) References student basic information (student ID) deferrable initially immediate;

-- Set the foreign key latency

Set constraint fk_info_grade deferred;

-- The insert operation does not meet the foreign key requirements.ConstraintsRows

Insert into student Score values ('2017010', 'c010', 80 );

-- EnableConstraintsInvalid

Alter table Student Score disable constraint fk_info_grade;

-- Insert again

Insert into student Score values ('2017010', 'c010', 80 );

-- EnableConstraintsEffective

Alter table Student Score enable constraint fk_info_grade;

-- Delete a column

Delete from student achievement where student ID = '000000 ';

-- Create a student renewal table

Create Table try2 as select * from Student Score;

-- Add a foreign key for cascading Deletion

Alter table try2 add constraint fk_try2 foreign key (student ID) References try (student ID) on Delete cascade;

-- Delete the last record in try

Delete from try where student ID = '123 ';

-- Run StorageConstraintsLine System script

@ D: \ oracle \ product \ 10.2.0 \ db_1 \ RDBMS \ admin \ utlexcpt. SQL

-- Search for ViolationConstraintsCondition row

Create Table student as select * from student basic information;

Alter table student add constraint pk_student primary key (student ID );

Alter table student disable constraint pk_student;

Insert into student values ('20140901', 'zhang san', 'male', 'Han nationality ');

Alter table student enable constraint pk_student;

Alter table student enable constraint pk_student exceptions into exceptions;

Select * from exceptions;

Select * from student where rowid = 'aaam7saaeaaaaccabl'

Alter table Student Score disable constraint fk_info_grade;

Insert into student Score values ('20170101', 'c011', 80 );

Commit;

Alter table student scores enable constraint fk_info_grade exceptions into exceptions;

Select * from exceptions;

5. Host md d: \ oracle \ ext_file

Host Copy D: \ oracle \ student.txt D: \ oracle \ ext_file \*.*

Create or replace directory stud_ext as 'd: \ oracle \ ext_file ';

Conn sys/AAA as sysdba

Grant read on directory stud_ext to Hu;

Conn Hu/AAA

Create Table student_ext

(SNO varchar2 (20 ),

Name varchar2 (8 ),

Sex varchar2 (2 ),

Nation varchar2 (8 ))

Organization external (

Type oracle_loader

Default directory stud_ext

Access parameters (fields terminated ',')

Location('student.txt ')

)

Reject limit Unlimited

 

 

Initially released by wabjtam123
[B] ALTER TABLE employee add constraint pk_employee_empno primary key (empno) deferrable initally immediate;

This indicatesYesYesDelayed primary key constraint

Heard of this delayYesIndicates that the check is performed after the transaction is committed, and the check is not performed after the transaction is committed without delay. For example, if an update is inserted, the check is performed immediately if the transaction is not committed.

I think there is a latency constraint.WhatMeaning: I don't feel any meaning at all. Instead, the function is used to harm people. If I have done more than N tasks, I will submit the tasks again. When I submit the tasks, I will make all the previous tasks in vain, it's better to remind me early that I can't do this. I have constraints. What do you think? [/B]

If youYesIt is useful to input data in form mode. If there is no delay, the input order must be strictly defined because foreign key connections have been established in several tables, otherwise, an error that violates integrity constraints will occur.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.