How do I leave the primary and foreign key of a table with the NOT NULL Constraint in Postgresql?

Asked

Viewed 95 times

2

I am creating primary and foreign keys with the NOT NULL Constraint in Postgresql, only the way I am doing this is going in the properties in the table and manually setting the keys as NOT NULL, I would like to do this through the table creation codes.

Below are some tables of my bank so that they can understand and help me in this my doubt

CREATE TABLE endereco_fornecedor (
  cd_endereco_fornecedor SERIAL PRIMARY KEY,
  cidade VARCHAR(30),
  bairro VARCHAR(30),
  endereco VARCHAR(30),
  numero_endereco INT
)

CREATE TABLE telefone_fornecedor (
  cd_telefone_fornecedor SERIAL PRIMARY KEY,
  telefone_fornecedor VARCHAR(15)
)

CREATE TABLE fornecedor (
  cd_fornecedor SERIAL PRIMARY KEY,
  cd_endereco_fornecedor INT,
  cd_telefone_fornecedor INT,
  cnpj_fornecedor VARCHAR(18),
  nome_fornecedor VARCHAR(30),
  email_fornecedor VARCHAR(50),
  foreign key (cd_endereco_fornecedor) references endereco_fornecedor (cd_endereco_fornecedor),
  foreign key (cd_telefone_fornecedor) references telefone_fornecedor (cd_telefone_fornecedor)  
)
sql postgresql

  • You want the cd_address_vendor and cd_phone_vendor fields not to be null?

  • Yes, NOT NULL for all keys.

3 answers

1


To not allow null values, when creating the field, just inform not null, example:

CREATE TABLE fornecedor (
  cd_fornecedor SERIAL PRIMARY KEY,
  cd_endereco_fornecedor INT not null,
  cd_telefone_fornecedor INT not null,
  cnpj_fornecedor VARCHAR(18),
  nome_fornecedor VARCHAR(30),
  email_fornecedor VARCHAR(50),
  foreign key (cd_endereco_fornecedor) references endereco_fornecedor (cd_endereco_fornecedor),
  foreign key (cd_telefone_fornecedor) references telefone_fornecedor (cd_telefone_fornecedor)  
);

See online: http://sqlfiddle.com/#! 17/4b6e9

If you want other fields with such condition, just follow the syntax:

nome_do_campo tipo_do_campo not null

Documentation: https://www.postgresql.org/docs/9.4/ddl-constraints.html

  • NOT NULL is a Constraint that works on Mysql, not Postgresql

  • @Iury Hittalo The Postgres documentation itself has this example and the link from the online example tests in a Postgres bank.

  • @IURY: How does not work in Postgresql???!!!???

  • Are you making a mistake? If so, which?

  • Note that it worked on my local postgres as well: https://i.stack.Imgur.com/uc9c0.png

  • @Danielmendes sorry my mistake, this works well well well.

  • @Danielmendes your answer is good, again every time you need to insert data into a table with foreign keys will always be necessary to enter the foreign key data manually.

Show 2 more comments

0

1 - If you create PK contraints, the field already becomes Notnull by default.

2 - You can create a Check :

CHECK (column_name IS NOT NULL).

0

Another way to do this is by using the SERIAL in foreign keys, so they never get null and implement automatically without having to enter the key values.

CREATE TABLE endereco_fornecedor (
  cd_endereco_fornecedor SERIAL PRIMARY KEY,
  cidade VARCHAR(30),
  bairro VARCHAR(30),
  endereco VARCHAR(30),
  numero_endereco INT
)

CREATE TABLE telefone_fornecedor (
  cd_telefone_fornecedor SERIAL PRIMARY KEY,
  telefone_fornecedor VARCHAR(15)
)

CREATE TABLE fornecedor (
  cd_fornecedor SERIAL PRIMARY KEY,
  cd_endereco_fornecedor SERIAL,
  cd_telefone_fornecedor SERIAL,
  cnpj_fornecedor VARCHAR(18),
  nome_fornecedor VARCHAR(30),
  email_fornecedor VARCHAR(50),
  FOREIGN KEY (cd_endereco_fornecedor) REFERENCES endereco_fornecedor (cd_endereco_fornecedor),
  FOREIGN KEY (cd_telefone_fornecedor) REFERENCES telefone_fornecedor (cd_telefone_fornecedor)  
)

inserir a descrição da imagem aqui

  • The way I did is not a good practice, because the foreign key will always be incremented and errors may occur.

Browser other questions tagged sql postgresql

You are not signed in. Login or sign up in order to post.