Not able to update the constraints of my table column-CodePudding

I am writing below command to make my column pname of table programs a foreign key, but the it gives me an error saying

ORA-00905: missing keyword

  SQL> alter table programs alter column pname varchar2(20) foreign key references programmer(pname);

CodePudding user response：

Test case:

SQL> create table programmer (pname varchar2(20) primary key);

Table created.

SQL> create table programs (pname varchar2(10));

Table created.

Altering the table:

SQL> alter table programs modify pname varchar2(20);

Table altered.

SQL> alter table programs add constraint fk_prog_ammer
  2    foreign key (pname) references programmer (pname);

Table altered.

SQL>

CodePudding user response：

You want to use ADD CONSTRAINT:

ALTER TABLE programs                            -- table name
  ADD CONSTRAINT programs__pname__fk            -- constraint name
  FOREIGN KEY (pname)                           -- column to apply the constraint to
  REFERENCES programmer(pname)                  -- table and column to reference

db<>fiddle here