SQL & PL/SQL :: ORA-02298 Cannot Validate - Parent Keys Not Found
Mar 6, 2012
i need to delete a record from table but it showed a error for foreign key constraint so i disabled the constraint and again deleted, now the row is deleted.
Again inserted another values instead the deleted value. after that i tried to alter the constraint but i received the error ORA-02298 cannot validate - Parent keys not found
i am running the query from which i am getting below mentioned error how can i find the record which is not there in parent table ora02291 integrety constrain violated and parent key not found.
I have a parent table EMPLOYEE which includes columns (sysid, serviceno,employeename...) sysid is Primary key, serviceno is Unique key and I have child table DEPENDENT includes columns (sysid,employee_sysid,name,dob...) here again SYSID is primary key for DEPENDENTS table, employee_sysid is Foreign key of EMPLOYEE table.
Now I want to change SYSID (using sequence) in EMPLOYEE table which need to be update in DEPENDENTS table as well
Note: I have 10000 records in EMPLOYEE table as well as I have 5 more child tables which need to update new SYSID.
I have an Image Type on a forum page. I want a default "not-found" image to display if the BLOB column value is null or if there is no data for that search value. The image is stored with the app: #APP_IMAGES#not-found.png
Trying to delete duplicate rows from a table. The problem is, they aren't exactly duplicate rows. Let me explain.
I am migrating data from a Oracle 8.1.7 db to a 10.2.1 db. In the older db, this certain table does not have a PK/Unique Index, but in the new db there is a unique index. The fields that the index is unique on are:
In the old db, when I run this query I get 1229 rows. With a count of 2 each.
select SUBSCR_NO, SUBSCR_NO_RESETS, EXTERNAL_ID, EXTERNAL_ID_TYPE, ACTIVE_DATE, count(*) from customer_id_equip_map group by SUBSCR_NO, SUBSCR_NO_RESETS, EXTERNAL_ID, EXTERNAL_ID_TYPE, ACTIVE_DATE having count(*)>1;
They are duplicates on those fields, but they are not totally duplicate rows because there is a field called is_current that has 0 in one row and has 1 in the other. What I need to do, is delete the 1229 rows with is_current=0.
In my sql developer I have created a table. Created primary keys. Using alter table I have also created the foreign key. Now I need to add one more foreign key to the same table. Do I have to drop the existing foreign key and then create the 2 new foreign keys using the alter table condition or can I retain the existing FK and just use alter table to add another FK.
the relationship on my script.What I want to do is make it possible to assign many courses to a student.But I have read a few books on foreign keys and primary keys, and I still dont get how to go on doing this....
This is how the table creation with the keys looks right now...
CREATE TABLE CAMPUS ( CAMPUSID NUMBER(4) NOT NULL, NAME VARCHAR2(80) NOT NULL, CONSTRAINT CAMPUS_PK PRIMARY KEY (CAMPUSID));
I have few BDs replicated using Advanced Replication, some tables are read only (Basic Replication) and another ones are Updatable (Advanced Replication).
The infraestucture is Materialized View Replication, my trouble is, I do not know how to should treat the updatable tables that have foreign keys, I read in another FAQs that you should replicate the indexes using the same way to replicate tables (dmbs_repcat.create_master_repobject)
Which is the correct manner to treat the foreign keys with this kind of updatable snapshots,
I need to copy the changed and deleted data in an other table. I have searched this site ,asktom and other sites also. I found the following solution from asktom website. But it gives me the changed columns data only and i need the primary key with changed data and deleted rows also.
DROP TABLE emp; CREATE TABLE emp AS (SELECT * FROM scott.emp); CREATE TABLE audit_table
I have to create indexes on foreign key columns ,now if composite index is already there with foreign key column then that will work or i will have to create a single column index.
Inside procedure,I need to validate all the columns (Ex:col1 should not accept more than 40 chars) and update status(containing error or not) of these columns into another column in the same table.For this,I mentioned only 'UPDATE' statements.So 'WHERE' clause of some update statements not using Primary keys.
In that table composite primary key was created.This procedure is successfully complied & executed now.But this procedure took more than 10 mins to execute.I need to reduce the time to less than a min.
Every time I hit up, down, left, and right arrow keys to return to a previous sql command I got ^[[D, fix this issue? The backspace works fine with me.
How can I create a list of items in a field, for instance lets say I have a table called car and one of the sub categories is parts. How can I make it so that parts can be any number of pre-defined entities? Or even table, for instance if I make a table called parts how can I use that in the car table in place of parts?
My second question is about using foreign keys as primary keys. If I am writing an email sql DB and I decided to use the members usrname as the primary key in the member table but then made another table that lists all the emails and decided to make the foreign key member username the primary key there.
Is that safe to do or should i create a sequence in which to identify a primary key for the email list table? Also what if I extend member to several other tables and use it as a primary key there too, seems like a kind of dangerous way to do things...
I have two oracle 9i databases A and B with complete equal schema. Only the data is different. I want to import all table data from A to B. The problem is that there are duplicate primary keys. Therefore I want to insert data with new primary keys (all referencing tables are concerned too).
I was reading the documentation for oracle 11gr2, with reference to URL>.....
The following examples show how to correctly choose the cluster key and set the HASH IS, SIZE, and HASHKEYS parameters. For all examples, assume that the data block size is 2K and that on average, 1950 bytes of each block is available data space (block size minus overhead).Note that 34 hash keys are assigned for each data block
how they arrive at 34 hash keys because another portion of the document states
This space determines the maximum number of cluster or hash values stored in a data block. If SIZE is not a divisor of the data block size, then Oracle Database uses the next largest divisor.
if that is the case, then number of hash keys should be 1900/55 = 34.55 which should have rounded up to 35.
table was defined as below & indexes are also created on name /dept columns, data is also available :
create table test ( name varchar2(10), version NUMBER(12), dept varchar2(10) [code].....
Now the requirement is that the parition keys has to be changed to 'dept' from the existing 'version' . How to accomplish this without any implication on the indexes and other constraints.
I have taken database backup using exp command and when I try to import in other pc the foreign keys are not imported. It saying error message that no matching unique key or primary key for this column.
how will i take backup including with primary keys?
I am using Oracle 10g Express Edition on Fedora core 5 32+ bit os. The problem is when I use the SQL Plus command line to make SQL statements I can not get the previously typed command back at the prompt when I use the up and down arrow keys on my keyboard. This is quite easy when I am using a shell, but here with this Oracle command line interface it is not working at all. Here is the example as what actually is happening whe I press the up or down arrow keys.
I have, for example, two tables: COMPANIES and EMPLOYEES. COMPANIES has a primary key on column COMP_ID. EMPLOYEES has a foreign key EMP_COMP_ID. I always do query COMPANIES with WHERE clause on COMP_ID and COMP_ADDRESS. What indexes on COMPANIES should I create in this case? First unique on COMP_ID, second composite on COMP_ID and COMP_ADDRESS? Or one unique index on COMP_ID and COMP_ADDRESS will be enough?
I need to populate a column called Parent Cd with the values that sits at the base level of the hierarchy. So, if the code A sits at the base level then its parent is NULL and the parent for code AA, AB, and AC will be A and parent for AAA, AAG will be AA and so on.The level_IDs are upto 10 and there are close to 400,000 records.
Just wanted to pose a question Can a (Unique and Not nullable)Alternate key of the parent table be part of child Table in an Identifying relationship i.e part of primary key of a child table????
Is there a rule that a primary keys of a parent table can be used to build a identifying relationship with its child?
I've the following data and i need the parent and leaf most child,including their id's
Create table par_chld (id,p_id,c_id) as (select 1,900,501 from dual union all select 2,900,502 from dual union all select 3,900,503 from dual union all select 4,100,900 from dual union all select 5,200,900 from dual union all select 6,300,400 from dual union all select 7,101,500 from dual union all select 8,102,500 from dual union all select 9,103,500 from dual union all select 10,201,600 from dual union all select 11,201,601 from dual union all select 12,201,602 from dual )
In the above data p_id =100 has c_id 900 , and this 900 acts as a parent which has child 501 , 502 ,503
And the output should be in the following format , Where i don't need the middle level data. I need parent and its leaf child along with the id's of parent and child
I tried the following query where i got the p_id,id_c,c_id columns , but unable to get the column id_p
select connect_by_root p_id p_id ,id id_c,c_id from par_chld where connect_by_isleaf =1 start with p_id not in (select c_id from par_chld where c_id is not null) connect by nocycle p_id =prior c_id
Example: I expect the rows with H8889,H9955 & P6666 & P5555 to be sub-category values value for product hierarchy H555888.
If there are rows with H8888987 as Product_hierarchy, we will pull up those rows too for product hierarchy H555888. The extra condition is we drill down only on 7 character mod_prod_number not on 5 character mod_prod_number. We pull out all sub category mod_prod_number for all distinct Product hierarchy.
