"FOREIGN key--Query" SELECT ' Parent table by child table: ' | | User_indexes. table_name| | ' ' | | ' Parent table column: ' | | User_ind_columns. column_name| | ' ' | | ' Child table: ' | | User_cons_columns. table_name| | ' ' | | ' Child table column: ' | | User_cons_columns. column_name From User_constraints,user_cons_columns,user_indexes,user_ind_columns where user_constraints. Constraint_name = User_cons_columns. Constraint_name and User_constraints. R_constraint_name = user_indexes. Index_name and User_indexes. Index_name = User_ind_columns. Index_name and Constraint_type = ' R ' and user_cons_columns. Table_name= ' Yx_prize_win_area ';
"FOREIGN key--Query" SELECT ' Parent table by parent table: ' | | User_indexes. table_name| | ' ' | | ' Parent table column: ' | | User_ind_columns. column_name| | ' ' | | ' Child table: ' | | User_cons_columns. table_name| | ' ' | | ' Child table column: ' | | User_cons_columns. column_name from User_constraints,user_cons_columns,user_indexes,user_ind_columns where USER_CONSTRAINTS. Constraint_name = User_cons_columns. Constraint_name and User_constraints. R_constraint_name = user_indexes. Index_name and User_indexes. Index_name = User_ind_columns. Index_name and Constraint_type = ' R ' and user_indexes. Table_name= ' Yx_ac_lottery_model ';
"Query primary Key" SELECT ' Constraint name: ' | | User_cons_columns. constraint_name| | ' ' | | ' Table name: ' | | User_cons_columns. table_name| | ' ' | | ' Column name: ' | | User_cons_columns. column_name| | ' ' | | ' Location: ' | | User_cons_columns. POSITION from User_constraints,user_cons_columns WHERE user_constraints. Constraint_name = User_cons_columns. Constraint_name and Constraint_type = ' P ' and user_cons_columns. Table_name= ";
Other constraints Select ' Constraint name: ' | | User_cons_columns. constraint_name| | ' ' | | ' Table name: ' | | User_cons_columns. table_name| | ' ' | | ' Column name: ' | | User_cons_columns. column_name| | ' ' | | ' Location: ' | | User_cons_columns. position| | ' ' | | Constraint_type, Search_conditionfrom user_constraints JOIN user_cons_columns on (user_constraints. Constraint_name = User_cons_columns. constraint_name) WHERE Constraint_type in (' C ', ' V ', ' O ');
"Let the Covenant expire and take effect"
ALTER TABLE EMP disable constraint pk_emp_empno;
ALTER TABLE EMP enable constraint pk_emp_empno;
"Query a table under constraint" SELECT ' constraint_name: ' | | constraint_name| | ', ' | | ' Constraint_type: ' | | constraint_type| | ', ' | | ' TABLE_NAME: ' | | table_name| | ', ' | | ' STATUS: ' | | STATUS from all_constraints WHERE table_name = ' yx_activity ' and owner = ' huodongplatnew ';
-----------Remove the foreign KEY constraint---------------
Select ' ALTER TABLE ' | | U1. table_name| | ' disable constraint ' | | U1. constraint_name| | '; ' from User_cons_columns u1,user_constraints U2 where U1. Constraint_name=u2. Constraint_name and U2. Constraint_type= ' R ' and U1. Table_name= ' B '; CREATE TABLE A (ID int,name varchar2 (2));
CREATE TABLE B (ID int,name varchar2 (2));
ALTER TABLE b ADD CONSTRAINT fk_name FOREIGN KEY (ID) REFERENCES a (ID); ALTER TABLE B add constraint Pk_student1 primary key (ID); ALTER TABLE A ADD constraint Pk_student2 primary key (ID); ---------------------production business-to-business------------------------dump_dir2/export/home/oracle/b2b_test Grant Read, write on direct Ory Dump_dir2 to Eygle; -----------------------63 Test-----------------------Dump_dir2/export/home/oracle/b2b_test Grant Read, write on Directory Dump_dir2 to Eygle;
From for notes (Wiz)
Day5_ viewing table primary foreign key relationships
