Home » SQL & PL/SQL » SQL & PL/SQL » foreign key
foreign key [message #213139] Tue, 09 January 2007 11:17 Go to next message
durai
Messages: 38
Registered: December 2006
Member
here is my doubt, i tried the following
i have 2 tables sample and sample1
sql>delete from sample where a = 100;
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SYS_C003144) violated - parent key not
found

SQL> select table_name , constraint_name , constraint_type from user_constraints -
> where table_name = 'SAMPLE';

TABLE_NAME CONSTRAINT_NAME C
------------------------------ ------------------------------ -
SAMPLE SYS_C003135 P
SAMPLE S_UNIQ U
SAMPLE SYS_C003143 U


when i tried to drop the primary key, it shows as follows
SQL> alter table sample drop constraints SYS_C003135;
alter table sample drop constraints SYS_C003135
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys

i didn't remeber the table name in which i created a foreign key refering this table's primary key
is there any other thing possible to know whether a table is being referred by another key,
for example a column empid in emptable may be referred by many dept tables,

also tell me is it possible to delete or drop a table which is been refered by some other table

[Updated on: Tue, 09 January 2007 12:02]

Report message to a moderator

Re: foreign key [message #213146 is a reply to message #213139] Tue, 09 January 2007 11:43 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

by the way u are mistaken one thing that is
ur statement is

sql>delete from sample1 where a = 100;
/* here table name is sample1 */
and after that u are doing

select table_name , constraint_name , constraint_type from user_constraints -
> where table_name = 'SAMPLE';

correct statement become if the table name is changed and it becomes
select table_name , constraint_name , constraint_type from user_constraints -
where table name = 'SAMPLE1';

now fire this statement and find out the constraint..if now happened the there is option i.e. on delete cascade

user it at the end of the statements .
try and reply is it works or not !
Re: foreign key [message #213155 is a reply to message #213146] Tue, 09 January 2007 12:22 Go to previous messageGo to next message
durai
Messages: 38
Registered: December 2006
Member
hai rajat_chaudhary, i am sorry
it is sample only(i entered missingly)
SQL> select * from sample;

A B C
---------- ---------- ----------
101 201 301
102 202 302
103 203 303

SQL> select * from sample1;

no rows selected

when i tried to delete a row from sample(parent table)
it shows the below error, even though there is no rows in the table sample1
SQL> delete from sample where a = 101;
delete from sample where a = 101
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SYS_C003144) violated - parent key not
found

also i like to know how one could come to know that one table is being referred by another key,
for example a column empid in emptable may be referred by many dept tables such as cse_dept, ece_dept, eee_dept etc
how one can come to know that emp table has been refered by other tables

[Updated on: Tue, 09 January 2007 12:26]

Report message to a moderator

Re: foreign key [message #213163 is a reply to message #213155] Tue, 09 January 2007 12:51 Go to previous message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

so,u want to know is there any constraint apply on this table or not or one table is being referred by another key ...........................................................

fire the command for constraints

select * from user_constraints
where table_name = 'your_table_name(caps)';
Previous Topic: Column count (merged)
Next Topic: How to get dates with six months difference
Goto Forum:
  


Current Time: Sun Dec 04 16:42:44 CST 2016

Total time taken to generate the page: 0.15969 seconds