ORA-02270 error while creating a table

Question

This is my create table command for creating table employee but I can't figure out why I get this error even though I have declared ssn as primary key

CREATE TABLE EMPLOYEE(
F_NAME VARCHAR2(15) NOT NULL,
M_NAME CHAR(2),
L_NAME VARCHAR2(15) NOT NULL,
SSN CHAR(9) PRIMARY KEY,
BIRTHDAY DATE,
ADDRESS VARCHAR2(50),
SEX CHAR(1) CHECK(SEX IN('M','F','m','f')),
SALARY NUMBER(7) DEFAULT 800,
SSSN CHAR(9),
DEPARTMENT_NUMBER NUMBER(5),
CONSTRAINT EMP_SSSN_FK FOREIGN KEY(SSSN)
REFERENCES EMPLOYEE(SSSN) ON DELETE SET NULL,
CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY(DEPARTMENT_NUMBER)
REFERENCES DEPARTMENT(DEPT_NO) ON DELETE CASCADE);

but I am getting error:

ORA-02270: no matching unique or primary key for this column-list

possible duplicate of [Oracle (ORA-02270) : no matching unique or primary key for this column-list error](http://stackoverflow.com/questions/10802212/oracle-ora-02270-no-matching-unique-or-primary-key-for-this-column-list-erro) — Mat, Aug 04 '14 at 13:06
Please search for the error number before asking here, there's tons of docs on this on the Internet. — Mat, Aug 04 '14 at 13:07

score 1 · Accepted Answer · answered Aug 04 '14 at 13:12

1

Change CONSTRAINT EMP_SSSN_FK FOREIGN KEY(SSSN) to CONSTRAINT EMP_SSSN_FK FOREIGN KEY(SSN) like this:

CREATE TABLE EMPLOYEE(
F_NAME VARCHAR2(15) NOT NULL,
M_NAME CHAR(2),
L_NAME VARCHAR2(15) NOT NULL,
SSN CHAR(9) PRIMARY KEY,
BIRTHDAY DATE,
ADDRESS VARCHAR2(50),
SEX CHAR(1) CHECK(SEX IN('M','F','m','f')),
SALARY NUMBER(7) DEFAULT 800,
SSSN CHAR(9),
DEPARTMENT_NUMBER NUMBER(5),
CONSTRAINT EMP_SSSN_FK FOREIGN KEY(SSSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET NULL,
CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY(DEPARTMENT_NUMBER) REFERENCES DEPARTMENT(DEPT_NO) ON DELETE CASCADE);

answered Aug 04 '14 at 13:12

neshkeev

6,280
3
26
47

still I am getting error that table doesn't exist, maybe it is referring to department table which I have to create yet. Can't I can define the department table after executing this statement – bhanu Aug 04 '14 at 13:37
1

Of course, you have to have the `department` table before you create the `employee` table. You can't create the `department` table after. Read the [basic info](http://docs.oracle.com/cd/E11882_01/appdev.112/e10766/tdddg_objects.htm#TDDDG30000) about creating tables and other database objects in oracle database. – neshkeev Aug 04 '14 at 13:44

score 1 · Answer 2 · answered Aug 04 '14 at 13:16

A foreign key can be declared in a table if and only if it is a primary key in another table. What you need to do immediately is ensure that SSN and DEPARTMENT_NUMBER are Primary key in their respective tables.

Visit this link and you will easily find your error. http://www.techonthenet.com/oracle/errors/ora02270.php

In case you do not follow, learn from http://www.w3schools.com/sql/sql_foreignkey.asp

Hope it helps

ORA-02270 error while creating a table

2 Answers2