Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: foreign Key creation SQL help needed!

Re: foreign Key creation SQL help needed!

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Thu, 23 Oct 2003 22:14:49 -0700
Message-ID: <1066972507.282268@yasure>


Tony! wrote:

>This is for an SQL class homework assignment.
>..Now that that's out of the way :)
>
>
>problem 1 was
>to create a table with a
>colA of type Number
>and
>colB which was varchar2(20)
>
>problem2 was -
>To make colA and colB a composite primary key
>
>Now I have to (problem3)...
>
>--Change the homework table so that colB can only contain values that
>--are stored in the order# column of the orders table. Make certain
>--that after the change, colB is still part of the composite key
>--primary key created in step two.
>
>
>I did the first two problems, but when I try to create the foreign key
>(which I believe is what I need to do to acheive the above) I got an
>error that the column type is incompatable with the referenced column
>type. ((The order# column in orders is Number))
>
>I ended up doing...
>
>_______________
>
>ALTER TABLE SCOTT.HOMEWORK
> MODIFY COLB NUMBER;
>
> ALTER TABLE SCOTT.HOMEWORK
> ADD CONSTRAINT COLB_ORDERS_ORDER#_FK
> FOREIGN KEY (COLB)
> REFERENCES SCOTT.ORDERS (ORDER#)
> ON DELETE CASCADE;
>
>__________________
>
>But I wonder if I was somehow supposed to make it work with the colB
>still being varchar2 type..
>
>Anyone have any thoughts on that? or advice?
>
>Thanks afforhand :)
>
>Tony!
>
>

First: Thank you for acknowledging that you are a student.

A foreign key must refer to a primary key or unique constraint in the referenced table and the
data type must be the same (VARCHAR2 or NUMBER or DATE).

Describe the tables are the data types the same?

Perform a SELECT on USER_CONS_COLUMNS
is there an appropriate constraint to support the foreign key?

If the data types are correct and the primary key or unique constraint exists ... post your
best attempt along with the error message you are getting.

And while it is not relevant to this question ... be sure to always post Oracle version and
edition with your questions as often it affects the answer.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Fri Oct 24 2003 - 00:14:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US