OraFAQ Forum: SQL & PL/SQL » ALTERING OR MODIFYING FREIGN KEY CONSTRAINT

Home » SQL & PL/SQL » SQL & PL/SQL » ALTERING OR MODIFYING FREIGN KEY CONSTRAINT (SQL pLUS oRACLE 11G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ALTERING OR MODIFYING FREIGN KEY CONSTRAINT [message #621223]

Mon, 11 August 2014 15:14

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.

Code: [Select all] [Show/ hide]

ALTER TABLE CLASSMATE.CH09DOGATTENDANCE
(MODIFY CONSTRAINT DS_ID
FOREIGN KEY(DOGSHOWID)
REFERENCES CLASSMATE.CH09DOGSHOW (DOGSHOWID)
ON DELETE CASCADE);

I also tried

Code: [Select all] [Show/ hide]

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.

Code: [Select all] [Show/ hide]

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.

Code: [Select all] [Show/ hide]

ALTER TABLE CLASSMATE.CH09DOGATTENDANCE
DROP CONSTRAINT DOGSHOWID;

AND THEN LIKE THIS

Code: [Select all] [Show/ hide]

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?

Report message to a moderator

Re: ALTERING OR MODIFYING FREIGN KEY CONSTRAINT [message #621228 is a reply to message #621223]

Mon, 11 August 2014 15:34

Littlefoot
Messages: 21824
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.

Code: [Select all] [Show/ hide]

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>

Report message to a moderator

Re: ALTERING OR MODIFYING FREIGN KEY CONSTRAINT [message #621231 is a reply to message #621228]

Mon, 11 August 2014 16:08

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.

Code: [Select all] [Show/ hide]

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.

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Re: ALTERING OR MODIFYING FREIGN KEY CONSTRAINT [message #621232 is a reply to message #621231]

Mon, 11 August 2014 16:10

Littlefoot
Messages: 21824
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.

Report message to a moderator

Re: ALTERING OR MODIFYING FREIGN KEY CONSTRAINT [message #621236 is a reply to message #621232]

Mon, 11 August 2014 19:58

dariusd7
Messages: 24
Registered: August 2014

Junior Member

Here is my create statement for the table

Code: [Select all] [Show/ hide]

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')
);

Report message to a moderator

Re: ALTERING OR MODIFYING FREIGN KEY CONSTRAINT [message #621237 is a reply to message #621236]

Mon, 11 August 2014 20:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

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

Code: [Select all] [Show/ hide]

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

dariusd7
Messages: 24
Registered: August 2014

Junior Member

Code: [Select all] [Show/ hide]

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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

Report message to a moderator

Re: ALTERING OR MODIFYING FREIGN KEY CONSTRAINT [message #621245 is a reply to message #621240]

Tue, 12 August 2014 00:24

Littlefoot
Messages: 21824
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

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Re: ALTERING OR MODIFYING FREIGN KEY CONSTRAINT [message #621254 is a reply to message #621245]

Tue, 12 August 2014 03:26

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

And next time you create constraints make sure you supply names for all of them.

Report message to a moderator

Re: ALTERING OR MODIFYING FREIGN KEY CONSTRAINT [message #621341 is a reply to message #621254]

Tue, 12 August 2014 12:02

dariusd7
Messages: 24
Registered: August 2014

Junior Member

Ok Im going to do this now..Thanks.. I'll get back to you..

Report message to a moderator

Re: ALTERING OR MODIFYING FREIGN KEY CONSTRAINT [message #621349 is a reply to message #621341]

Tue, 12 August 2014 13:02

dariusd7
Messages: 24
Registered: August 2014

Junior Member

Am I suppose to sub in my table names for where you have c and O?

Report message to a moderator

Re: ALTERING OR MODIFYING FREIGN KEY CONSTRAINT [message #621350 is a reply to message #621349]

Tue, 12 August 2014 13:29

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

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 07 02:59:40 CDT 2025