Alter Table , adding a Foreign key constraint at a column ORA-02253

Question

Hello there i am studying for the Oracle Certification of SQL Associate . And trying to do some Examples . I have an issue where i cannot find easily a reference on this .

create table employees 
(employee_id number NOT NULL,
first_name varchar(20),
last_name varchar(30),
constraint employee_pk primary key (employee_id));

create table employee_notes 
(employee_notes_id number, 
 employee_notes varchar(500),
 constraint pk_employee_notes primary key (employee_notes_id));

create sequence employee_notes_seq start with 1 increment by 1

---

Now i want to add a new column at employee_notes table with a foreign key constraint . I can't find out in syntax where is the problem .

****alter table employee_notes 
add employee_id number
constraint fk_employee_notes foreign key (employee_id) references employees (employee_id);****

i get this error

ORA-02253: constraint specification not allowed her

---

I also tried to alter the table and add column and then the constraint but cannot

 alter table employee_notes 
add employee_id number;

--

alter table employee notes add constraint fk_employee_notes foreign key (employee_id) references employees (employee_id);

ORA-02253: constraint specification not allowed here

I would like to know how i can do this and why this syntax is wrong :)

Answer 1

You did something wrong because - it works OK:

SQL> CREATE TABLE employees
  2  (
  3     employee_id  NUMBER NOT NULL,
  4     first_name   VARCHAR (20),
  5     last_name    VARCHAR (30),
  6     CONSTRAINT employee_pk PRIMARY KEY (employee_id)
  7  );

Table created.

SQL>
SQL> CREATE TABLE employee_notes
  2  (
  3     employee_notes_id  NUMBER,
  4     employee_notes     VARCHAR (500),
  5     CONSTRAINT pk_employee_notes PRIMARY KEY (employee_notes_id)
  6  );

Table created.

SQL> ALTER TABLE employee_notes ADD employee_id NUMBER;

Table altered.

SQL> ALTER TABLE employee_notes ADD CONSTRAINT fk_employee_notes
  2  FOREIGN KEY (employee_id)
  3  REFERENCES employees (employee_id);

Table altered.

SQL>

Answer 2

When you use ALTER TABLE ... ADD in order to add a column and a constraint in one statement, do the following:

-- notice the () and the comma!
alter table employee_notes
add ( 
  employee_id number
, constraint fk_employee_notes 
  foreign key (employee_id) references employees (employee_id)
) ;

That should do the trick. See dbfiddle. The syntax is similar to CREATE TABLE, where you'd also write all column names, data types etc in (), separated by commas.