ORA-00907: missing right parenthesis again

Why? I have used

CONSTRAINT FK_r_p FOREIGN KEY (id_p) REFERENCES Perguntas (id_p)

before, I don’t know if it makes a difference but… what’s is wrong with this?

CREATE TABLE Pergunta
(
    id_p INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    nome_aluno VARCHAR2(60) NOT NULL,
    titulo VARCHAR2(50) NOT NULL,
    nome_disciplina VARCHAR2(50),
    descricao VARCHAR2(4000) NOT NULL
);

CREATE TABLE Resposta
(
    id_r INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    titulo VARCHAR2(50) NOT NULL,
    descricao VARCHAR2(4000) NOT NULL,
   
    FOREIGN KEY (id_p) REFERENCES Pergunta (id_p)
);

Answer

multiple issues :

1. Identity column in Oracle are created using generated always as identity
2. Int datatype doesn’t need length.
3. referencing to another table is based on columns , you need to link a column to a column in another table , so when you make fk to id_p in Pergunta , you need to define a column for that in your table

here is what I mean:

CREATE TABLE Pergunta(
    id_p INT GENERATED ALWAYS AS IDENTITY NOT NULL,
    nome_aluno VARCHAR2(60) NOT NULL,
    titulo VARCHAR2(50) NOT NULL,
    nome_disciplina VARCHAR2(50),
    descricao VARCHAR2(4000) NOT NULL,
     primary key(id_p)
);

CREATE TABLE Resposta(
    id_r INT GENERATED ALWAYS AS IDENTITY NOT NULL,
    titulo VARCHAR2(50) NOT NULL,
    descricao VARCHAR2(4000) NOT NULL,
    id_p int ,
    FOREIGN KEY (id_p) REFERENCES Pergunta (id_p),
    PRIMARY KEY(id_r)
);