Now I want to change the deptno from 10 to 20 and 20 to 10 in dept table . So the ouput will be like
deptno| dname|dseq|
20 | CS | 1
10 | SC | 2
when i am deleting that row I am getting error "ORA-02292: integrity constraint (ROP_FK) violated - child record found".IF i am trying to update i am getting unique key contractin failure.
(Even I cannot update the rest of the column values in dept table as it is also having foreign key dependency) i need to put a sql script in production so that it will interchange.
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?
While there's numerous QAs about inserting an image into the DB using the Data Block, how does one remove an image? Obviously there's the "UPDATE ... SET X = EMPTY_BLOB();", however, that kills the Data Block/Form's flow, and it doesn't update.
I've tried a few things:
* :CONTROL.IMG1 := NULL; (bad bind variable) * :CONTROL.IMG1 := EMPTY_BLOB(); (some other error, probably as above)
the literature equates dimension hierarchies with fuctional dependencies between the levels. I like to tst the strength of this assumption with the implementation of 'CREATE DIMENSION' which allows you to create roll-up hierarchies.
My question to put it simply is this: Given:
CREATE DIMENSION location_dim LEVEL location IS (location.loc_id) LEVEL city IS (location.city) LEVEL state IS (location.state) HIERARCHY geog_rollup ( location CHILD OF city CHILD OF state CHILD )
Can I insert the following rows into the dimension: loc_id, city, state 1, Epping, NSW 2, Epping, VIC
Please note that the two Eppings are different cities.
Given the roll-up hierarchy City -> State, will it require that for every city there can be only one state in which case the FD between City and State cannot hold. Or, is it that the roll-up hierarchy defined here has nothing to do with FD.
The second part of the question is if the answer to the above question is that the roll-up is not the same as FD, then is the ATTRIBUTE clause meant to define the n:1 (functional dependency) instead?
suggest on the dependenecy between the database service and the database instance?
srvctl start instance -i db1 will this start service as well for which the above instance is either preferred or available instance? will it start the listener as well?
srvctl stop instance -i db1 will this stop service as well for which the above instance is either preferred or available instance? will it stop the listener as well?
srvctl start service -d db -s dbserve will this start service as well for which the above instance is either preferred or available instance?
srvctl stop servive -d db -s dbserve will this stop service as well for which the above instance is either preferred or available instance?
srvctl start database -d db will it start instance, listener and service as well?
srvctl stop database -d db will it stop instance, listener and service as well?
It's been a while since I worked with SQL Plus . I am using Oracle 11g. We are working on a legacy data migration project. I have a table of records with circular dependency records. i am trying to identify the records. I have the foll. columns- Product,Source,target. I want o identify the records which form a loop. For e.g.
Source Target A B B C C D D A
Last record forms a loop-I need to identify these records. My query is below-
SELECT DISTINCT SOURCE,TARGET FROM RULESELIB WHERE CONNECT_BY_ISCYCLE=1 CONNECT BY NOCYCLE SOURCE=PRIOR TARGET;
I ran this query on 2 tables- one with 75000 records and the other with 25000 records. It works fine on the table with 75000 records completes within a minute but it does not complete on the other table. I can't seem to be able to figure out the issue with the query or is there something about the data that is causing this query to loop infinitely?
I have an already populated table that refers to a wrong foreign key in another table. I have table abc that has the column fk_id. this column currently refers to column x_id1 in the table abc while it should refer to column x_id2 in xyz. I am trying to replace the data but I guess I miss something in the logic or the right way of doing it:
commit; insert all into abc values (10,'ab1',1) into abc values (11,'ab2',2) into abc values (12,'ab3',5) into abc values (13,'ab4',7) into abc values (14,'ab5',9) into xyz values (1,1,'d1') into xyz values (2,2,'d2') into xyz values (5,3,'d3') into xyz values (7,4,'d4') into xyz values (9,5,'d5') select * from dual; commit;
--this following select returns 5 rows
select * from abc, xyz where abc.fk_id = xyz.x_id1;
-- the following update only updates 3 rows and sets the other 2 -- to null!
update abc set fk_id = (select x_id2 from xyz where xyz.x_id1 = abc.fk_id); commit;
I'm not really sure why oracle is not finding my Foreing Key, I'm creating an easy set of table for a company and I'm declaring all Primary keys and foreing keys as necessary and this is my
I have a parent table called dept and one child table emp which is referenced on deptno and now both have data . Now i want to truncate the dept table and i have truncated emp table after it when i am trying to truncate dept table then oracle throws a error that is-
"ORA-02266: unique/primary keys in table referenced by enabled foreign keys" ,
but when i tried to delete some records 1 by 1 then these have delete but truncate is not still working. why? if i have 10 millon records.
You should almost always index foreign keys because they are frequently used in joins. In addition, if you intend to delete or update unique or primary keys on the parent table, you should index the foreign keys to improve the locking of child records.what I don't understand are, I should create index on foreign key column in parent table or child table or both ?
Is there any view in oracle which gives the foreign key mapping.
E.g.
Table DEPT has DEPTNO as PK. Table Emp has DEPTNO as FK to DEPT.DEPTNO.
I can get the information for DEPT table from R_CONSTRAINT_NAME column of all_constraints table.My requirement is to get the the which column of Emp table refers to DEPT.DEPTNO column .
Say we have an employee(id_emp) table with a primary key on id_emp. We have also some history tables emp_stuff with columns say (id_emp, dat_event, some_stuff) with primary key id_emp, dat_event.
This means that we have a unique index on (id_emp,dat_event). We also have a foreign key id_emp that references employee(id_emp). When we update id_emp on employee, we still have a lock on emp_stuff. According to this (end of the page) :
Quote:So, in short, with releases prior to Oracle Database 11g Release 1, you will want an index on the foreign key of the child table if you do any of the following:
Update the parent table primary key Delete from the parent table Merge into the parent table
So is id_emp in emp_stuff considered as indexed (through the unique index of the primary key) or do we have to add an explicit index like this CREATE INDEX emp_stuff ON emp_stuff(id_emp) to avoide child table locks?
I am trying to establish PK-FK relationship between 2 tables. Table 1 has VACC_ID as a primary key. Table 2 has VACC_ID and SCH_ID as the primary keys. I am trying to add VACC_ID from Table 2 as a FK to Table 1. This is the code I used
I also tried to add it without writing the query and editing the Table 1. By default, I am getting 2 columns(both VACC_ID) in the local column being referenced to the 2 primary keys(VACC_ID and SCH_ID) of table 2. I also uploaded the screeshot of the error, when I run the query.
In my point of view, deferral FK (DFK) are stronger than non-deferral FK (NDFK). In other words, every NDFK can be moved to DFK. Is there a performance issue ?
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.
I am working on a new project in OBIEE. I am asked to do the data modeling in the database using oracle sql developer. I have to create the joins based on the requirements. I have the tables created already. But the primary keys for few tables are not defined for few tables. PK-FK joins are also not done properly.
My questions are (1) If I have to define the primary key for the existing tables can I do that using the alter table command or should I create the table all over again and then define it? (2) If I have to make the changes in the existing PK-FK joins how do i go about doing that?
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 have a central entity Address. I have other entities that could have many addresses (Organization, Person for eg). The relation between Organization and Address is one to many and the relation between Person and Address is also one to many.When I modelled it, I ended up having two foreign keys in Address (Organization ID and Person ID).The question that is being raised by the client is "As only one of the foreign key would be populated on each record, the other would be null". The client is not comfortable having nullable foreign keys as they won't be able to maintain integrity. One of the requirement is that No orphan records in Address. By modeling the way above, there are chances for having some orphanned records. I tried to explain that this would be controlled by the program to ensure that no orphan records would be there but their question is that what if someone goes directly and enters a record in Address table without any foreign key value (I dont know who would have an insert/update access on the tables other than the dba's).
I am trying to get some pros and cons on using the nullable foreign keys.
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 have to create six tables as part of my assignment. I have created 5 tables. I am having trouble with the 6th. How can I reference a foreign key to a composite primary key? I have colored red the areas that I think are giving me trouble which is in the 6th table, the Registration Table.
Create Table Student ( StudentID number (6,0) NOT NULL, Student_Name varchar2 (20) NOT NULL, Constraint Student_PK Primary Key (StudentID) );
Create Table Faculty ( FacultyID number (5,0) NOT NULL, Faculty_Name varchar2 (20) NOT NULL, Constraint Faculty_PK Primary Key (FacultyID) );
Create Table Course ( CourseID varchar2 (10) NOT NULL, Course_Name varchar2 (20) NOT NULL, Constraint Course_PK Primary Key (CourseID) );
Error starting at line 1 in command: Create Table Registration ( StudentID number (6,0) NOT NULL, Section_No number (5,0) NOT NULL, Semester varchar2 (7) NOT NULL, Constraint Registration_PK Primary Key (StudentID, Section_No, Semester), Constraint Registration_FK1 Foreign Key (StudentID) references Student (StudentID), Constraint Registration_FK2 Foreign Key (Section_No) references Section (Section_No, Semester, CourseID), Constraint Registration_FK3 Foreign Key (Semester) references Section (Section_No, Semester, CourseID) )
Error at Command Line:8 Column:104 Error report: SQL Error: ORA-02256: number of referencing columns must match referenced columns 02256. 00000 - "number of referencing columns must match referenced columns" *Cause: The number of columns in the foreign-key referencing list is not equal to the number of columns in the referenced list. *Action: Make sure that the referencing columns match the referenced columns.
I have 3 tables (Prospect,customer,user) and I have a contact_dtls table .
In prospect table Prospect_id is the primary like this customer_id in customer table and user_id in the user table
in the contact_dtls table I have a column contact_id and other cols.The contact_id can be from prospect_id,customer_id and user_id in other words we can tell the prospect can be a contact or the customer can be a contact and the user also can be a contact also.
Now the question is "is it possible to maintain primary key and foreign key relationship with this design that means the prospect_id,customer_id and user_id would be the foreign keys to the contact_dtls table.
