Re: Why can I not create a foreign key?

tojo wrote:

>In article <63bdbacc.0310160452.75713eda_at_posting.google.com>,
>northfinchley_at_hotmail.com says...
>
>
>>Hi.
>>
>>This should surely work:
>>
>>SQL> alter table st_shirt add CONSTRAINT S_SC_FK FOREIGN KEY
>>(SHIRT_COLOR_NO) REFERENCES ST_COLOR (COLOR_NO);
>>
>>But it does not. Instead I get this:
>>
>>*
>>ERROR at line 1:
>>ORA-02270: no matching unique or primary key for this column-list
>>
>>
>>The reason I think this should work is because COLOR_NO is the single
>>primary key of ST_COLOR. It is defined thusly:
>>
>> COLOR_NO NOT NULL NUMBER(3)
>>
>>The proposed FK column in ST_SHIRT looks like this:
>>
>> SHIRT_COLOR_NO NOT NULL NUMBER(3)
>>
>>Neither table contains data.
>>
>>So what the hell is going on?????
>>
>>
>>TIA,
>>
>>Chuck.
>>
>>PS: No, I'm not really working on a clothing database. These are just
>>an example....
>>
>>
>>
>
>COLOR_NO can't be NULL, but is it really the primary key? Try this to
>find out:
>
> SELECT
> A.COLUMN_NAME PK_FIELD
> FROM
> SYS.USER_CONS_COLUMNS A,
> SYS.USER_CONSTRAINTS B
> WHERE
> A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
> AND A.TABLE_NAME = 'ST_COLOR'
> AND B.CONSTRAINT_TYPE = 'P'
>
>
>-- Tom
>
>

NULL can not be the issue as the OP has indicated the tables contain no records.

My advice though would be the same. Be very sure that whta you think is a primary key really is.

If it is ... I'd seriously consider dropping the tables and recreating everything from the beginning.

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