creation of tables to register questions in postgresql

Question

I’m making a program where the user can register and search for questions. Each question has a domain (e.g., law, psychology), various themes, a difficulty, the text of the question and the answer. until now my code registers and searches the questions, but now I will implement it to a database, but I am very lay in the subject and I’m having problems creating the tables I always come across several errors and now I’m not able to create this sequence of tables:

---

I’m trying to create with this code:

CREATE TABLE tab_dominio (id_dominio SERIAL NOT NULL PRIMARY KEY, dominio varchar (50))

CREATE TABLE dificuldade (dificuldade int)

CREATE TABLE tab_questoes (id_questao SERIAL NOT NULL PRIMARY KEY, dominio varchar(50) FOREIGN KEY references tab_dominio(dominio), dificuldade FOREIGN KEY references dificuldade(dificuldade), pergunta varchar(250), resposta varchar(250))

CREATE TABLE tab_temas (id_tema SERIAL PRIMARY KEY NOT NULL, dominio varchar(50) FOREIGN KEY references tab_dominio(dominio), tema varchar(50))

CREATE TABLE questaotema (id_questao int FOREIGN KEY references tab_questoes(id_questao), id_tema FOREIGN KEY references tab_temas(tema))

But I’m getting the bug:

ERROR: syntax error at or near "FOREIGN" LINE 1: ... SERIAL NOT NULL PRIMARY KEY, dominio varchar(50) FOREIGN KE..

If I delete where it says there is error it keeps showing more and more errors and in the end I can’t create my table, I’m caught in it.

Answer 1

You cannot place a field that does not uniquely identify a line as a foreign key in another table.

The drawing may lack the relationship between tables table_questoes and Themes.

Substitute dominio for id_dominio.

CREATE TABLE tab_dominio (
    id_dominio SERIAL NOT NULL PRIMARY KEY, 
    dominio varchar (50)
);

CREATE TABLE dificuldade (
    dificuldade int NOT NULL PRIMARY KEY
);

CREATE TABLE tab_questoes (
    id_questao SERIAL NOT NULL PRIMARY KEY, 
    id_dominio int references tab_dominio(id_dominio), 
    dificuldade int references dificuldade(dificuldade), 
    pergunta varchar(250), 
    resposta varchar(250)
);

CREATE TABLE tab_temas (
    id_tema SERIAL PRIMARY KEY NOT NULL, 
    id_dominio int references tab_dominio(id_dominio), 
    tema varchar(50)
);

CREATE TABLE questaotema (
    id_questao int references tab_questoes(id_questao), 
    id_tema int references tab_temas(id_tema),
    PRIMARY KEY (id_questao, id_tema)
);

I understand the existence of the difficulty table solely to ensure consistency of permitted values, which may be replaced by a CHECK clause.

You use the FOREIGN KEY expression in the case of table_constraint:

[ CONSTRAINT constraint_name ] FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]

In the case of column_constraint use:

[ CONSTRAINT constraint_name ] REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ]

Answer 2

Use INTEGER REFERENCES in place of FOREIGN KEY references

Reference -> http://www.postgresqltutorial.com/postgresql-foreign-key/

Example:

CREATE TABLE tab_dominio (id_dominio SERIAL NOT NULL PRIMARY KEY, dominio varchar (50))


CREATE TABLE dificuldade (id_dificuldade  integer UNIQUE)

CREATE TABLE tab_questoes (
    id_questao SERIAL NOT NULL PRIMARY KEY, 
    id_dominio INTEGER REFERENCES  tab_dominio(id_dominio), 
    dificuldade INTEGER REFERENCES   dificuldade(id_dificuldade), 
    pergunta varchar(250), 
    resposta varchar(250)
);

Or you can use another form, where you first define the field in the table id_dominio INTEGER and then assigns it foreign key status FOREIGN KEY (id_dominio) REFERENCES tab_dominio (id_dominio),

CREATE TABLE tab_questoes (
    id_dominio INTEGER,
    id_questao SERIAL NOT NULL PRIMARY KEY, 
    FOREIGN KEY (id_dominio) REFERENCES tab_dominio (id_dominio),
    dificuldade INTEGER REFERENCES   dificuldade(id_dificuldade), 

    pergunta varchar(250), 
    resposta varchar(250)
);