Forms :: Table B Has Foreign Key Reference To Table A
Jan 11, 2012
There are 2 tables : Table A and Table B.Table B has foreign key reference to Table A.There are 2 forms in the application based on table A (form 1) and table B (form 2).
Now when we open form 2, the functionality is such that it acquires a lock on table B for the selected record during the search criteria. Lock is acquired by using "select 1 from table_B where column = :column for update no wait".So when the form 2 is opened by any other user/session and same record is tried to be selected, then an exception is raised to the user that the current record is being edited by some other user and does not allow him to edit that record.
Now imagine if User has opened screen 2 (One record in Table B has been locked). With this lock existing, we open form 1, and click a button which performs a COMMIT_FORM. At this point the form hangs. On checking the locked objects, there is a lock on both table A and table B. When the Form 2 is closed, then the Form 1 which was hanging a while ago starts responding.
When the foreign key relationship is dropped and the above scenario is tried, there is no issue encountered. Form 1 works fine even if form 2 is open.We are not supposed to drop the Foreign key relationship as well.
Is this because req_id is also defined in the Primary key definitions on these two tables, thus making the field not valid for referential constraint definition?
I am trying to use reference partitioning the parent table records moved using the row movement . but the child table records are not moving below is my sample .
CREATE TABLE ref_parent1 ( table_name VARCHAR2(30), order_date DATE, num_rows NUMBER); INSERT INTO ref_parent1 VALUES ('TEST',SYSDATE,100); INSERT INTO ref_parent1 VALUES ('TEST1',SYSDATE,1000); INSERT INTO ref_parent1 VALUES ('TEST2',SYSDATE,1000); COMMIT;
[code]....
ORA-14652: reference partitioning FOREIGN KEY IS NOT supported
Table1:event_channel Table2:event_inst I have to update event_channel one column with 4 record(channel_type_id,values 1,2,3,4) with respect to one record of event_inst table column(event_instance_id). event_inst table column(event_instance_id) has respective 4 records in event_channel
I have two tables A and B. In table A there is a field which contains a string of 20 characters; this essentially holds 5 codes of 4 characters each.
Table B is a reference table. It holds the 4 character code and the description.
I am trying to run a select query to bring back the description of the code for the first 2 codes in table A but i am not sure how to bring back the descriptions! The below is what i am trying to achieve.
I have a table DW_ORDER_CHANNEL and I need to know what are the other objects accessing this table. As i need to alter this table so the dependent objects get invalid. how to get the dependent object on this table?
CREATE TABLE test_A(member_id NUMBER(2) PRIMARY KEY, MEMBER_name VARCHAR2(20), MEMBER_parent NUMBER(2) DEFAULT NULL); INSERT ALL INTO test_A VALUES (1, 'mem1', NULL) INTO test_A VALUES (2, 'mem2', NULL) INTO test_A VALUES (3, 'mem3', NULL) INTO test_A VALUES (4, 'mem4', 1) INTO test_A VALUES (5, 'mem5', 1)
[code]....
As the actual data is huge, I need to know the best (least expensive) way to select each parent and then all its direct child ordered by member_id the output should look like:
I have an employee table which has a primary key and a self referencing foreign key, as shown here
create table employee ( id not null, name not null, department not null, supervisor_id not null ,constraint constraint_1 primary key (id) ,constraint constraint_2 foreign key (supervisor_id) references employee (id));
Now if i make the primary key composite, as shown below -
create table employee ( id not null, name not null, department not null, supervisor_id not null ,constraint constraint_1 primary key (id, name) ,constraint constraint_2 foreign key (supervisor_id) references employee (id));
Oracle is throwing the following error -
ORA-02270: no matching unique or primary key for this column-list
How can this error be fixed without changing the composite primary key?
I need to implement the foreign key on a column of a table from 2 tables. My requirement is in bellow.
drop table t1; create table t1 (slno number, acc_no number);
drop table t2; create table t2 (acc_no number primary key, acc_name varchar2(100));
drop table t3; create table t3 (acc_no1 number primary key, acc_name1 varchar2(100)); [code]...
It is provided that the values of acc_no in t2 and acc_no1 in t3 are unique.Now it required that while inserting into t1 , the system will check either t2 or t3 tables.
I have now created my tables and now I want to populate them with my data but I get errors below is my full sql command. From last time I learned that one foreign key can be implemented in one table and cannot be duplicated to work around this I changed my foreign key as you can see to "fkone" . but why am I getting errors when trying to add information I mean I have not gotten errors in the first two tables so what am I "doing wrong/ not understanding"
i have a field in my table office i got field office_code ,this field is been used in diffirent tables as foreign key is there a sql i can wirte to see all tables who have used this field as foreign key.
I have csv file which having parent and child related data.i need to load parent data in to parent table and get parent reference id and store in child table with child data. i am not able to find how to get parenet refernce id using control file. In my csv file i have 2 parent name rows. I need to create one A record in parent table and get that parent primary(P_ID) for 'A' record and put into child table for c_name test1 and test2 records.
My controll file ------------------- LOAD DATA INFILE 'mydata.data' APPEND INTO TABLE parent when (1:1) = 'p' fields terminated by ',' optionally enclosed by '"' trailing nullcols
[code].....
able to load parent data and generate P_ID, but i am not able to get P_ID for child records.
Am creating a table based on some integrity constraints, but it's not working.
CREATE TABLE member ( member_id NUMBER(10), last_name VARCHAR2(25) NOT NULL, first_name VARCHAR2(25),
[code],,,
Error:
Error report: SQL Error: ORA-02270: no matching unique or primary key for this column-list 02270. 00000 - "no matching unique or primary key for this column-list" *Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement gives a column-list for which there is no matching unique or primary key constraint in the referenced table. *Action: Find the correct column names using the ALL_CONS_COLUMNScatalog view
Create table Car (Car_cd VARCHAR2(5), Car_Desc VARCHAR2(50) ); alter table Car add constraint Car_PK primary key (Car_CD); INSERT INTO Car (Car_Cd, Car_Desc) VALUES ('CORLA','COROLLA'); Commit; [code]....
The requirement necessitates a new table to map car to manufacturer. This mapping table may later be expanded to contain other attributes Engine, MPG, etc to hold details specific to a car.But this is in future.
--Not able to create this as Car_cd is already a PK in this table and therefore has Unique Index ALTER TABLE Car_Mapping_Details ADD CONSTRAINT Car_Mapping_Details_fk1 FOREIGN KEY (Car_Cd)REFERENCES Car (Car_Cd); [code]....
But in this case the Car_Mapping_Details.Car_cd is itself is a primary key and therefore has Unique index.Although I was able to create foreign key constraint on Car_mapping_details.car_cd column (which is also Primary Key), I was not able to create Foreign Key Index on this column. It gives me Quote:ORA-01408: such column list already indexed.In other words, not creating foreign index for foreign key column lead to table-level lock? Or will the Unique Index in that primary key column be sufficient to avoid table-level lock?
I have got two tables emp_dtl and iou_tab. i have already made entries i.e booking no, emp_cd, emp_name etc in emp_dtl snc its my master table. I want to retrieve the booking nos through lov in iou_tab which are generated in emp_dtl and corresponding info of emp_cd and emp_name should come in the respected fields in iou_tab.
I was about to move some tables from one table space to another but it seems it is not possible to move partitioned tables between table spaces of different block sizes.
So far the only option I have is to export and then import back the data.
know if there is any way to move a partitioned table between table spaces of different block size?
I have a requirement to import text files which are generated from 3d modelling software xsteel where it records all geometric information and i want to import this information into oracle table.
primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.
CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 ( dt DATE ) IS v_sql_error VARCHAR2 (100); -- added by sanjiv v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv
There is an interface table and there is an normal transcational table..interface table is being compared with normal table and if match found the result is dumped into another normal table.
I am using two cursors one is to query the interface table and in a for loop pass the results to the second cursor..The interface table is having 5000 + rows and the transcation table is having more than 3.7 millions ..and the program is taking lots of time to execute..took almost 35-45 minutes..
