CODEWITHSUNDEEP
sqldatabaseoracle-database
bhanu
bhanu

Reputation: 383

ORA-02270 error while creating a table

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

Upvotes: 0

Views: 138

Answers (2)

Antriksh Shah
Antriksh Shah

Reputation: 11

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

Upvotes: 1

neshkeev
neshkeev

Reputation: 6486

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

Upvotes: 1

Related Questions