-
Simple Newbie Questions about SQL and Oracle 10g
First of all, i'm pleased to be a member of your forum. I got some newbie questions, i will be glad to hear back from you. Excuse me if you see any writing mistakes.
create table test1 (
pk NUMBER PRIMARY KEY,
fk NUMBER,
col1 NUMBER,
col2 NUMBER,
CONSTRAINT fk_constraint FOREIGN KEY (fk) REFERENCES test1,
CONSTRAINT ck1 CHECK (pk > 0 and col1 > 0),
CONSTRAINT ck2 CHECK (col2 > 0) );
An error is returned for the following statements:
ALTER TABLE test1 DROP (pk); -- pk is a parent key.
We can not drop it because we did not mention ON DELETE CASCADE. Am i right?
ALTER TABLE test1 DROP (col1) -- col1 is referenced by multicolumn constraint ck1.
I do not get it, can you explain please. col1 is not referenced, i see CHECK constraint is applied but no references made. Secondly, is ck1 considered multicolumn because it check two columns? Or multicolumn here represents something else?
-
-
I just would like to add 2 questions relating to the thread:
ALTER TABLE emp2
DROP COLUMN employee_id CASCADE CONSTRAINTS;
This drop employee_id column and all its child. Correct?
ALTER TABLE test1
DROP (pk, fk, col1) CASCADE CONSTRAINTS;
This drops three columns and all its child if there are any. Correct?
Then what's the difference between ON DELETE CASCADE and CASCADE CONSTRAINTS?
For example, What if employee_id in emp2 table definition does not have ON DELETE CASCADE, will CASCADE CONSTRAINTS work? Please explain...
-
alter mytable drop column mycolumn cascade constraints; would 1) drop fks pointing to mytable.mycolumn then 2) drop mytable.mycolumn column. That's it.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Alright, so CASCADE CONSTRAINTS is defined in the parent side.
ON DELETE CASCADE is defined in the foreign key side.
Thanks
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|