CODEWITHSUNDEEP
sqloracle-databaseddl
Bryan E
Bryan E

Reputation: 25

SQL "Alter Table" for foreign key

So I'm learning some SQL as a bit of a side project since my SQL sucks. I have the following table I created:

CREATE TABLE deliveries (
  pid INTEGER,
  FOREIGN KEY (pid) REFERENCES person_lives_at,
  );

I want to alter it to be a table like this one:

CREATE TABLE deliveries (
  pid INTEGER,
  FOREIGN KEY (pid) REFERENCES employee,
  );

How can I achieve that? I'm using Oracle SQL Developer

Upvotes: 0

Views: 3356

Answers (1)

OldProgrammer
OldProgrammer

Reputation: 12179

Since you created an FK constraint without a name, oracle assigned a system-generated name to the constraint like SYS_xxxxxx. To find the constraint name:

select constraint_name from all_Constraints
where table_name = 'DELIVERIES'

In my test case, it returned "SYS_C0016779". I then drop the constraint:

alter table deliveries drop constraint SYS_C0016779

Then add the new constraint:

ALTER TABLE deliveries
ADD CONSTRAINT PID_EMP_FK -- or whatever you want to call it.
   FOREIGN KEY (pid)
   REFERENCES employee(pid); -- or whatever the name of the column is

Upvotes: 4

Related Questions