CODEWITHSUNDEEP
oracle-databaseforeign-keys
Bhavana
Bhavana

Reputation: 349

oracle 10g foreign key constraints

ALTER TABLE  employees
ADD  CONSTRAINT emp_dno_fk FORIEGN KEY(Dno) REFERENCES Departments(Dno);

When I use this command it shows an error like

CONSTRAINT Specification not allowed here.

But when I use this command it works:

ALTER TABLE  employees
ADD  CONSTRAINT emp_dno_fk Dno REFERENCES Departments(Dno);

Can anyone tell me why Oracle doesn't allow FOREIGN KEY KEYWORD in the first command?

Upvotes: 1

Views: 3850

Answers (1)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

The error message, admittedly, is not very helpful. The following are examples of how a referential integrity constraint may be created in Oracle:

The following examples assume that the column Dno already exists in employees:

ALTER TABLE employees ADD
  CONSTRAINT emp_dno_fk FOREIGN KEY (Dno) REFERENCES Departments (Dno);

ALTER TABLE employees ADD
  FOREIGN KEY (Dno) REFERENCES Departments (Dno);

The following examples assume that the column Dno does not already exist in employees:

ALTER TABLE employees ADD
  CONSTRAINT emp_dno_fk Dno REFERENCES Departments (Dno);

ALTER TABLE employees ADD
  Dno REFERENCES Departments (Dno);

Personally, I avoid the syntax versions which add the column and prefer to add it myself.

Upvotes: 2

Related Questions