Foreign Key Creation Issue in Oracle

ORACLE CREATE TABLE with FOREIGN KEY error

Leave "FOREIGN KEY" out, if you want to declare a foreign key constraint inline.

CREATE TABLE warehouse(
warehouse_id INTEGER NOT NULL PRIMARY KEY,
warehouse_address VARCHAR2(20),
warehouse_postcode VARCHAR2(8),
warehouse_phonenm INTEGER,
region_id REFERENCES region(region_id));

Foreign key does not exist error? Oracle SQL

You want to use the composite key:

CREATE TABLE WRITTEN_BY (
  NAME VARCHAR2(20),
  ADDRESS VARCHAR2(30),
  ISBN VARCHAR2(30),
  CONSTRAINT WB_FK FOREIGN KEY(NAME, ADDRESS)
  REFERENCES AUTHOR(NAME, ADDRESS),
  CONSTRAINT WB_FK3 FOREIGN KEY(ISBN)
  REFERENCES BOOK(ISBN)
);

db<>fiddle here

Why oracle doesn't allow creating fk on unique index?

Constraints are part of the SQL standard & relational theory. Indexing is not. It could be part of the standard, but it isn't. But if it were part of the standard then the standard still shouldn't mix up two levels--abstract relation interface vs its implementation that it should be independent of. SQL says a FK (foreign key) references a UNIQUE or PK (primary key). So if the DBMS conforms, you have to do that. But when you declare a UNIQUE constraint the DBMS will give you an implicit unique index. (And when you declare a PK constraint the DBMS will give you an implicit UNIQUE NOT NULL constraint & implicit corresponding index.)

can't set a foreign key on oracle sql

No "FOREIGN KEY" keyword is used for inline constraints. So, like this:

CREATE TABLE manufacturer (manufacturer_id integer primary key, manufacturer_name varchar2(20));

CREATE TABLE models (model_id integer primary key, 
                     model_name varchar2(20),
                     model_year integer, 
                     eng_cylinder integer, 
                     eng_horsepower integer,
                     manufacturer_id int constraint models_fk1 references manufacturer(manufacturer_id));

Can't enable a foreign key in ORACLE SQL

There are values in the column that are invalid from the foreign key's perspective.

Assuming that you have a self-referencing foreign key like:

create table employees (
    employee_id int primary key,
    manager_id  int references employees(employee_id)
);

Then the error message indicates that there are manager_ids that do not exists in column employee_id. You can exhibit the offending rows with a query like:

select manager_id
from employees e
where not exists (select 1 from employees e1 where e1.employee_id = e.manager_id)

You need to fix this before you can enable the foreign key.

Oracle SQL : Cannot create a foreign key based on index of more than one column

A Foreign Key references either a Primary Key constraint or a Unique constraint. And it must be a constraint, a unique index is not enough.

If you already have index then you can create unique constraint based on that index. For your case:

alter table test_ek_uk
  add constraint test_ek_uk_nooper_numseq unique (NOOPER, NUMSEQ)
  using index altest_ek_uk_nooper_numseq;

But if you don't have that index - there is no need to create it explicitly.
So, instead of creating the unique index you could create a unique constraint:

alter table test_ek_uk
  add constraint test_ek_uk_nooper_numseq unique (NOOPER, NUMSEQ);

The unique index is created in the background when you create this unique constraint.

Foreign key constraint is not working in Oracle

The foreign key part for an inline constraint is invalid. You can only use references

create table x
(
  p_id integer references persons(p_id)
);