By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,117 Members | 2,142 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,117 IT Pros & Developers. It's quick & easy.

unable to add foriegn key constaint

P: 1
hello friends,
i am a beginer.I was unable to add a foriegn key constraint to my NAMES table which refers my EMPLOYEE TABLE

Expand|Select|Wrap|Line Numbers
  1. SQL> desc employee
  2.  Name                                      Null?    Type
  3.  ----------------------------------------- -------- ----------------------------
  4.  FNAME                                              VARCHAR2(10)
  5.  LNAME                                              VARCHAR2(10)
  6.  SSN                                       NOT NULL NUMBER(4)
  7.  ADDRESS                                            VARCHAR2(50)
  8.  SEX                                                VARCHAR2(1)
  9.  SALARY                                             NUMBER(5)
  10.  SUPERSSN                                           NUMBER(5)
  11.  DNO                                                NUMBER(1)
  12.  
  13. SQL> desc names
  14.  Name                                      Null?    Type
  15.  ----------------------------------------- -------- ----------------------------
  16.  NAME                                               VARCHAR2(5)
  17.  NSSN                                               NUMBER(4)
  18.  
  19. SQL> alter table names add constraint nn8 foriegn key(nssn) references employee(ssn);
  20. alter table names add constraint nn8 foriegn key(nssn) references employee(ssn)
  21.                                                  *
  22. ERROR at line 1:
  23. ORA-01735: invalid ALTER TABLE option
Mar 3 '07 #1
Share this Question
Share on Google+
2 Replies


100+
P: 153
hello friends,
i am a beginer.I was unable to add a foriegn key constraint to my NAMES table which refers my EMPLOYEE TABLE

Expand|Select|Wrap|Line Numbers
  1. SQL> desc employee
  2.  Name                                      Null?    Type
  3.  ----------------------------------------- -------- ----------------------------
  4.  FNAME                                              VARCHAR2(10)
  5.  LNAME                                              VARCHAR2(10)
  6.  SSN                                       NOT NULL NUMBER(4)
  7.  ADDRESS                                            VARCHAR2(50)
  8.  SEX                                                VARCHAR2(1)
  9.  SALARY                                             NUMBER(5)
  10.  SUPERSSN                                           NUMBER(5)
  11.  DNO                                                NUMBER(1)
  12.  
  13. SQL> desc names
  14.  Name                                      Null?    Type
  15.  ----------------------------------------- -------- ----------------------------
  16.  NAME                                               VARCHAR2(5)
  17.  NSSN                                               NUMBER(4)
  18.  
  19. SQL> alter table names add constraint nn8 foriegn key(nssn) references employee(ssn);
  20. alter table names add constraint nn8 foriegn key(nssn) references employee(ssn)
  21.                                                  *
  22. ERROR at line 1:
  23. ORA-01735: invalid ALTER TABLE option

Just remember that a foreign key must reference either a unique key or primary key on another table. (i cant see what your indexes and primary keys are here)

**but in this case the immediate problem appears to be your spelling of the word foreign (not foriegn) ** :)
Mar 3 '07 #2

debasisdas
Expert 5K+
P: 8,127
your problem is poor spelling
use FOREIGN KEY
YOUR PROBLEM WILL BE SOLVED

note:-foreign key refers to a Primary key or at least an Unique field.
Take care in future.
Mar 10 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.