Home » SQL & PL/SQL » SQL & PL/SQL » ALTERING OR MODIFYING FREIGN KEY CONSTRAINT (SQL pLUS oRACLE 11G)
ALTERING OR MODIFYING FREIGN KEY CONSTRAINT [message #621223] Mon, 11 August 2014 15:14 Go to next message
dariusd7
Messages: 24
Registered: August 2014
Junior Member
I AM A STUDENT. AND I AM STUCK ON THIS ONE QUESTION. Modify a constraint so that when a parent row in the CH09DOGSHOW table is deleted, all the related rows in the ch09dogattendance table are deleted.

here are the tables
there is a foreign key constraint on DOGSHOWID in the classmate.dogshowattendance table referencing the parent column/key in classmate.dogshowid.

I have tried the following.
ALTER TABLE CLASSMATE.CH09DOGATTENDANCE
(MODIFY CONSTRAINT DS_ID
FOREIGN KEY(DOGSHOWID)
REFERENCES CLASSMATE.CH09DOGSHOW (DOGSHOWID)
ON DELETE CASCADE);



I also tried
ALTER TABLE CLASSMATE.CH09DOGATTENDANCE
ADD FOREIGN KEY (DOGSHOWID) REFERENCES CLASSMATE.CH09DOGSHOW (DOGSHOWID) 
ON DELETE CASCADE;


got this error

Error starting at line 1 in command:
ALTER TABLE CLASSMATE.CH09DOGATTENDANCE
ADD FOREIGN KEY (DOGSHOWID) REFERENCES CLASSMATE.CH09DOGSHOW (DOGSHOWID)
ON DELETE CASCADE
Error report:
SQL Error: ORA-02275: such a referential constraint already exists in the table
02275. 00000 - "such a referential constraint already exists in the table"
*Cause: Self-evident.
*Action: Remove the extra constraint.


I also tried DROPPING the current constraint FIRST.

ALTER TABLE CLASMATE.CH09DOGATTENDANCE
DROP FOREIGN KEY DOGSHOWID;


GOT THIS ERROR

Error starting at line 1 in command:
ALTER TABLE CLASMATE.CH09DOGATTENDANCE
DROP FOREIGN KEY DOGSHOWID
Error report:
SQL Error: ORA-00905: missing keyword
00905. 00000 - "missing keyword"
*Cause:
*Action:

TRY DROPPING IT THIS WAY.

ALTER TABLE CLASSMATE.CH09DOGATTENDANCE
DROP CONSTRAINT DOGSHOWID;


AND THEN LIKE THIS

ALTER TABLE CLASSMATE.CH09DOGATTENDANCE
DROP CONSTRAINT FOREIGN KEY DOGSHOWID;


GOT THIS ERROR
Error starting at line 1 in command:
ALTER TABLE CLASSMATE.CH09DOGATTENDANCE
DROP CONSTRAINT FOREIGN KEY DOGSHOWID
Error report:
SQL Error: ORA-01735: invalid ALTER TABLE option
01735. 00000 - "invalid ALTER TABLE option"
*Cause:
*Action:

In A NUTSHEEL BEFORE I BOTHERED TO ASK Question here on the forum. I tried to research , and read on how to do it, and I am not having any luck.

So my first question is should I drop the current constraint on Dogshowid in the dogshowattendance table.. if so how?
How do I alter the dogshowattendance table to now include the on delete casecade ?

What am I doing wrong?
Re: ALTERING OR MODIFYING FREIGN KEY CONSTRAINT [message #621228 is a reply to message #621223] Mon, 11 August 2014 15:34 Go to previous messageGo to next message
Littlefoot
Messages: 19648
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
The first error you got (ORA-02275: such a referential constraint already exists in the table) is self-explaining - you can't create a constraint that already exists.

The second (ORA-00905: missing keyword) and the third one (ORA-01735: invalid ALTER TABLE option) are because you used invalid syntax (yes, documentation helps here).

Here's an example of how you could have done that. Take a look, try to write your own code based on it.
SQL> -- create tables and constraints
SQL> create table t_dept as select * from dept;

Table created.

SQL> create table t_emp as select * from emp;

Table created.

SQL> alter table t_dept add constraint pk_tdept primary key (deptno);

Table altered.

SQL> alter table t_emp add constraint fk_temp_tdept foreign key (deptno)
  2  references t_dept (deptno);

Table altered.

SQL>
SQL> -- drop foreign key constraint
SQL> alter table t_emp drop constraint fk_temp_tdept;

Table altered.

SQL>
SQL> -- create the ON DELETE CASCADE constraint
SQL> alter table t_emp add constraint fk_temp_tdept_odc foreign key (deptno)
  2  references t_dept (deptno)
  3  on delete cascade;

Table altered.

SQL>
SQL> -- let's delete DEPTNO = 10 from the T_DEPT table; all employees
SQL> -- (who work in DEPTNO = 10) should be automatically deleted from the T_EMP table
SQL> select count(*) from t_emp where deptno = 10;

  COUNT(*)
----------
         3

SQL> delete from t_dept where deptno = 10;

1 row deleted.

SQL> select count(*) from t_emp where deptno = 10;

  COUNT(*)
----------
         0

SQL>
Re: ALTERING OR MODIFYING FREIGN KEY CONSTRAINT [message #621231 is a reply to message #621228] Mon, 11 August 2014 16:08 Go to previous messageGo to next message
dariusd7
Messages: 24
Registered: August 2014
Junior Member
I think maybe the problem comes from when I first created the table CH09DOGATTENDANCE.
When I created the foreign key I did it like this.

FOREIGN KEY (DOGSHOWID) REFERENCES CLASSMATE.CH09DOGSHOW (DOGSHOWID),


SO MAYBE THE FOREIGN KEY CONSTRAINT WAS NEVER ACTUALLY APPLIED..



However it was successful and the table was created.
But when I try to drop the table the way you did. I still get an error. here is exactly what I did.

ALTER TABLE CLASSMATE.CH09DOGATTENDANCE
DROP CONSTRAINT DOGSHOWID;


BUT I GET THIS ERROR STILL

Error starting at line 1 in command:
ALTER TABLE CLASSMATE.CH09DOGATTENDANCE
DROP CONSTRAINT DOGSHOWID
Error report:
SQL Error: ORA-02443: Cannot drop constraint - nonexistent constraint
02443. 00000 - "Cannot drop constraint - nonexistent constraint"
*Cause: alter table drop constraint <constraint_name>
*Action: make sure you supply correct constraint name.


Re: ALTERING OR MODIFYING FREIGN KEY CONSTRAINT [message #621232 is a reply to message #621231] Mon, 11 August 2014 16:10 Go to previous messageGo to next message
Littlefoot
Messages: 19648
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Read what Oracle reports! It says "nonexistent constraint", i.e. you are trying to drop a constraint that can't be found.

Maybe it would be easier (for us) if you posted CREATE TABLE statements as well, because - providing partial information, it is difficult to guess what you did wrong.
Re: ALTERING OR MODIFYING FREIGN KEY CONSTRAINT [message #621236 is a reply to message #621232] Mon, 11 August 2014 19:58 Go to previous messageGo to next message
dariusd7
Messages: 24
Registered: August 2014
Junior Member
Here is my create statement for the table
CREATE TABLE CLASSMATE.CH09DOGATTENDANCE
(
DOG_ID INT,
DOG_NAME VARCHAR(255),
DOGSHOWID INT,
SHOW_NAME VARCHAR (255),
FOREIGN KEY (DOG_ID) REFERENCES CLASSMATE.CH09DOG(DOG_ID),
FOREIGN KEY (DOGSHOWID) REFERENCES CLASSMATE.CH09DOGSHOW(DOGSHOWID),
CONSTRAINT PK_ATTENDSHOW PRIMARY KEY (DOG_ID, DOGSHOWID),
PLACEMENT VARCHAR(255),
RANK VARCHAR(6),
CHECK (RANK = 'FIRST' OR RANK = 'SECOND' OR RANK = 'THIRD' OR RANK = 'FOURTH')
);

Re: ALTERING OR MODIFYING FREIGN KEY CONSTRAINT [message #621237 is a reply to message #621236] Mon, 11 August 2014 20:17 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
read http://www.orafaq.com/forum/t/174502/

SELECT constraint_name, 
       constraint_type 
FROM   all_constraints 
WHERE  table_name = 'CH09DOGATTENDANCE' 
       AND owner = 'CLASSMATE'; 


post results from SQL above

[Updated on: Mon, 11 August 2014 20:18]

Report message to a moderator

Re: ALTERING OR MODIFYING FREIGN KEY CONSTRAINT [message #621238 is a reply to message #621237] Mon, 11 August 2014 21:26 Go to previous messageGo to next message
dariusd7
Messages: 24
Registered: August 2014
Junior Member
CONSTRAINT_NAME                CONSTRAINT_TYPE 
------------------------------ --------------- 
SYS_C0011147                   R               
SYS_C0011146                   R               
PK_ATTENDSHOW                  P               
SYS_C0011144                   C               

4 rows selected


*BlackSwan added {code} tags. Please do so yourself in the future. see URL below

http://www.orafaq.com/forum/t/174502/

[Updated on: Mon, 11 August 2014 21:39] by Moderator

Report message to a moderator

Re: ALTERING OR MODIFYING FREIGN KEY CONSTRAINT [message #621240 is a reply to message #621238] Mon, 11 August 2014 21:42 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
the FK names are system assigned named which is why it threw error when you used "DROP CONSTRAINT DOGSHOWID"

consider to actually Read The Fine Manual to learn correct SQL syntax.
http://docs.oracle.com/database/121/SQLRF/toc.htm
Re: ALTERING OR MODIFYING FREIGN KEY CONSTRAINT [message #621245 is a reply to message #621240] Tue, 12 August 2014 00:24 Go to previous messageGo to next message
Littlefoot
Messages: 19648
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Now it is obvious; you didn't name foreign key constraints (but you did that with a primary key constraint).

CONSTRAINT PK_ATTENDSHOW PRIMARY KEY (DOG_ID, DOGSHOWID) -> PK_ATTENDSHOW is its name

FOREIGN KEY (DOGSHOWID) REFERENCES CLASSMATE.CH09DOGSHOW(DOGSHOWID) -> as you didn't name it, Oracle created a name for you. It is one of the "R" constraint type (R as referential integrity), SYS_C00...

Therefore, you should have dropped SYS_C00... constraint, not DOGSHOWID. But - which one of SYS_C00...? In order to find that out, broaden previous query so that you'd see table and column names, such as
SQL>   SELECT c.constraint_name,
  2           c.constraint_type,
  3           c.table_name,
  4           o.column_name
  5      FROM user_constraints c, user_cons_columns o
  6     WHERE o.constraint_name = c.constraint_name AND c.table_name LIKE 'T%'
  7  ORDER BY c.table_name, c.constraint_type;

CONSTRAINT_NAME      C TABLE_NAME           COLUMN_NAME
-------------------- - -------------------- --------------------
PK_TDEPT             P T_DEPT               DEPTNO
SYS_C00210946        C T_EMP                EMPNO
FK_TEMP_TDEPT_ODC    R T_EMP                DEPTNO

SQL>
Doing so, you should be able to choose the right constraint. Feel free to see what information those views (user_constraints, user_cons_columns) contain, you might find it helpful some day.
Re: ALTERING OR MODIFYING FREIGN KEY CONSTRAINT [message #621254 is a reply to message #621245] Tue, 12 August 2014 03:26 Go to previous messageGo to next message
cookiemonster
Messages: 10981
Registered: September 2008
Location: Rainy Manchester
Senior Member
And next time you create constraints make sure you supply names for all of them.
Re: ALTERING OR MODIFYING FREIGN KEY CONSTRAINT [message #621341 is a reply to message #621254] Tue, 12 August 2014 12:02 Go to previous messageGo to next message
dariusd7
Messages: 24
Registered: August 2014
Junior Member
Ok Im going to do this now..Thanks.. I'll get back to you..
Re: ALTERING OR MODIFYING FREIGN KEY CONSTRAINT [message #621349 is a reply to message #621341] Tue, 12 August 2014 13:02 Go to previous messageGo to next message
dariusd7
Messages: 24
Registered: August 2014
Junior Member
Am I suppose to sub in my table names for where you have c and O?
Re: ALTERING OR MODIFYING FREIGN KEY CONSTRAINT [message #621350 is a reply to message #621349] Tue, 12 August 2014 13:29 Go to previous message
balu_dba
Messages: 2
Registered: October 2011
Location: India
Junior Member

It is not mandatory to use those table aliases now, run the query and post the result.
By checking the result you can drop the constraint with column_name value as DOGSHOWID then again create a new constraint with ON DELETE CASCADE option.

If the result is
CONSTRAINT_NAME      C TABLE_NAME           COLUMN_NAME
-------------------- - -------------------- --------------------
SYS_C0011146         R CH09DOGATTENDANCE    DOGSHOWID


Now you can drop the constraint SYS_C0011146 and then create a new foreign key constraint with on delete cascade option.
Previous Topic: issue inserting multiple lines into one row
Next Topic: Help me! 06546. 00000 - "DDL statement is executed in an illegal context"
Goto Forum:
  


Current Time: Tue Sep 30 23:17:45 CDT 2014

Total time taken to generate the page: 0.05427 seconds