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  |
 |
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 #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
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 #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 asSQL> 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.
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun May 18 05:25:40 CDT 2025
|