Error null value in column id of relation user violates not null constraint - Исправление ошибок и поиск оптимальных решений проблем

Summary: in this tutorial, you will learn about PostgreSQL not-null constraint to ensure the values of a column are not null.

Introduction to NULL

In database theory, NULL represents unknown or information missing. NULL is not the same as an empty string or the number zero.

Suppose that you need to insert an email address of a contact into a table. You can request his or her email address. However, if you don’t know whether the contact has an email address or not, you can insert NULL into the email address column. In this case, NULL indicates that the email address is not known at the time of recording.

NULL is very special. It does not equal anything, even itself. The expression NULL = NULL returns NULL because it makes sense that two unknown values should not be equal.

To check if a value is NULL or not, you use the IS NULL boolean operator. For example, the following expression returns true if the value in the email address is NULL.

email_address IS NULL
Code language: PHP (php)

The IS NOT NULL operator negates the result of the IS NULL operator.

To control whether a column can accept NULL, you use the NOT NULL constraint:

CREATE TABLE table_name(
   ...
   column_name data_type NOT NULL,
   ...
);
Code language: PHP (php)

If a column has a NOT NULL constraint, any attempt to insert or update NULL in the column will result in an error.

Declaring NOT NULL columns

The following CREATE TABLE statement creates a new table name invoices with the not-null constraints.

CREATE TABLE invoices(
  id SERIAL PRIMARY KEY,
  product_id INT NOT NULL,
  qty numeric NOT NULL CHECK(qty > 0),
  net_price numeric CHECK(net_price > 0) 
);
Code language: SQL (Structured Query Language) (sql)

This example uses the NOT NULL keywords that follow the data type of the product_id and qty columns to declare NOT NULL constraints.

Note that a column can have multiple constraints such as NOT NULL, check, unique, foreign key appeared next to each other. The order of the constraints is not important. PostgreSQL can check the constraint in the list in any order.

If you use NULL instead of NOT NULL, the column will accept both NULL and non-NULL values. If you don’t explicitly specify NULL or NOT NULL, it will accept NULL by default.

Adding NOT NULL Constraint to existing columns

To add the NOT NULL constraint to a column of an existing table, you use the following form of the ALTER TABLE statement:

ALTER TABLE table_name
ALTER COLUMN column_name SET NOT NULL;
Code language: SQL (Structured Query Language) (sql)

To add set multiple NOT NULL constraint to multiple columns, you use the following syntax:

ALTER TABLE table_name
ALTER COLUMN column_name_1 SET NOT NULL,
ALTER COLUMN column_name_2 SET NOT NULL,
...;
Code language: SQL (Structured Query Language) (sql)

Let’s take a look at the following example.

First, create a new table called production orders ( production_orders):

CREATE TABLE production_orders (
	id SERIAL PRIMARY KEY,
	description VARCHAR (40) NOT NULL,
	material_id VARCHAR (16),
	qty NUMERIC,
	start_date DATE,
	finish_date DATE
);
Code language: SQL (Structured Query Language) (sql)

Next, insert a new row into the production_orders table:

INSERT INTO production_orders (description)
VALUES('Make for Infosys inc.');
Code language: SQL (Structured Query Language) (sql)

Then, to make sure that the qty field is not null, you can add the not-null constraint to the qty column. However, the column already contains data. If you try to add the not-null constraint, PostgreSQL will issue an error.

To add the NOT NULL constraint to a column that already contains NULL, you need to update NULL to non-NULL first, like this:

UPDATE production_orders
SET qty = 1;
Code language: SQL (Structured Query Language) (sql)

The values in the qty column are updated to one. Now, you can add the NOT NULL constraint to the qty column:

ALTER TABLE production_orders 
ALTER COLUMN qty
SET NOT NULL;
Code language: SQL (Structured Query Language) (sql)

After that, you can update the not-null constraints for material_id, start_date, and finish_date columns:

UPDATE production_orders
SET material_id = 'ABC',
    start_date = '2015-09-01',
    finish_date = '2015-09-01';
Code language: SQL (Structured Query Language) (sql)

Add not-null constraints to multiple columns:

ALTER TABLE production_orders 
ALTER COLUMN material_id SET NOT NULL,
ALTER COLUMN start_date SET NOT NULL,
ALTER COLUMN finish_date SET NOT NULL;
Code language: SQL (Structured Query Language) (sql)

Finally, attempt to update values in the qty column to NULL:

UPDATE production_orders
SET qty = NULL;



  


                       
  



Code language: SQL (Structured Query Language) (sql)

PostgreSQL issued an error message:

[Err] ERROR:  null value in column "qty" violates not-null constraint
DETAIL:  Failing row contains (1, make for infosys inc., ABC, null, 2015-09-01, 2015-09-01).
Code language: JavaScript (javascript)

The special case of NOT NULL constraint

Besides the NOT NULL constraint, you can use a CHECK constraint to force a column to accept not NULL values. The NOT NULL constraint is equivalent to the following CHECK constraint:

CHECK(column IS NOT NULL)
Code language: SQL (Structured Query Language) (sql)

This is useful because sometimes you may want either column a or b is not null, but not both.

For example, you may want either username or email column of the user tables is not null or empty. In this case, you can use the CHECK constraint as follows:

CREATE TABLE users (
 id serial PRIMARY KEY,
 username VARCHAR (50),
 password VARCHAR (50),
 email VARCHAR (50),
 CONSTRAINT username_email_notnull CHECK (
   NOT (
     ( username IS NULL  OR  username = '' )
     AND
     ( email IS NULL  OR  email = '' )
   )
 )
);
Code language: SQL (Structured Query Language) (sql)

The following statement works.

INSERT INTO users (username, email)
VALUES
	('user1', NULL),
	(NULL, 'email1@example.com'),
	('user2', 'email2@example.com'),
	('user3', '');
Code language: SQL (Structured Query Language) (sql)

However, the following statement will not work because it violates the CHECK constraint:

INSERT INTO users (username, email)
VALUES
	(NULL, NULL),
	(NULL, ''),
	('', NULL),
	('', '');
Code language: SQL (Structured Query Language) (sql)

[Err] ERROR:  new row for relation "users" violates check constraint "username_email_notnull"
Code language: JavaScript (javascript)

Summary

Use the NOT NULL constraint for a column to enforce a column not accept NULL. By default, a column can hold NULL.
To check if a value is NULL or not, you use the IS NULL operator. The IS NOT NULL negates the result of the IS NULL.
Never use equal operator = to compare a value with NULL because it always returns NULL.

Was this tutorial helpful ?

Источник

Answer by Elianna Christensen

First.
I changed the id naming convention from user_id to simply id in the database (this is true for all others). ,

Stack Overflow
Public questions & answers

,Find centralized, trusted content and collaborate around the technologies you use most.,Thanks for contributing an answer to Stack Overflow!

First.
I changed the id naming convention from user_id to simply id in the database (this is true for all others).

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)

Second.

The issue was found in dumping the original sqlite db file into postgres (from sqlite3 —> postgres) instead of creating the db in postgres.
So, I ran the original code in SQLAlchemy, but this time pointing to the postgres db:

engine = create_engine('postgresql://localhost/some_db')

Answer by Salvatore Hansen

sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) null value in column «username» violates not-null constraint,The table in my adminer has columns id small int autoincrement, username not null VARCHAR, and password not null VARCHAR.,and it’ll probably fail with a KeyError I’m guessing. Whatever it is, username is None (sql null) when it tries to insert,Replace credentialszero = request.form.get(«username»)

<form action = "{{url_for('signup')}}" methods = "post">

	`<label for = "username">Username</label>`

	`<input name = "username">`

	`<label for = "password">Password</label>`

	`<input name = "password">`

	`<button>Submit</button>`

`</form>`

def signup():

`credentialszero = request.form.get("username")`

`credentialsone = request.form.get("password")`

`db.execute("INSERT INTO information(username, password) VALUES (:username, :password)",` 

		`{"username":credentialszero, "password":credentialsone});`

`success = "Success. Log in to continue"`

`db.commit()`

`return render_template("loginpage.html", success = success)`

Answer by Joyce Cannon

Install databases[postgresql].,I inspected the database schema using Postico, and here’s what the DDL looks like:,The following would resolve the issue at least for postgres.,By clicking “Sign up for GitHub”, you agree to our terms of service and
privacy statement. We’ll occasionally send you account related emails.

asyncpg.exceptions.NotNullViolationError: null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, John, Doe, [email protected], 1997, M, null, f, null, f, 0, 0, null, 0, 0).

Answer by Adler Kirk

class BkpFulfilmentProductProvider(db.Model):
    """ Databae Model for FUlfilment Product Provider"""
    __tablename__ = 'bkp_fulfilment_product_provider'

    product_code = db.Column(db.String(100), primary_key=True)
    fulfilment_provider_code = db.Column(db.String(100))
    is_primary = db.Column(db.Boolean)
    on_hand_stock = db.Column(db.Integer)
    on_order_stock = db.Column(db.Integer)


@app.route('/')
def fulfilment_view():
    return render_template('Ful_Product_Provider.html')


@app.route('/Fulfilment', methods=['POST'])
# if request.method == "POST":
def fulfilmentproductprovider():
    #  from models import BkpFulfilmentProductProvider
    if request.method == 'POST':
        prod_cd = request.form.get('product_code')
        ful_provider_cd = request.form.get('fulfilment_provider_code')
        primary = request.form.get('is_primary')
        stock_in_hand = request.form.get('on_hand_stock')
        ordered_stock = request.form.get('on_order_stock')
        fulfil = BkpFulfilmentProductProvider(product_code=prod_cd, fulfilment_provider_code=ful_provider_cd,
                                          is_primary=primary, on_hand_stock=stock_in_hand, on_order_stock=ordered_stock)
        db.session.add(fulfil)
        db.session.commit()
    return render_template('Ful_Product_Provider.html')

[python]

Answer by Samson McGuire

Summary: in this tutorial, you will learn about PostgreSQL not-null constraint to ensure the values of a column are not null.,Then, to make sure that the qty field is not null, you can add the not-null constraint to the qty column. However, the column already contains data. If you try to add the not-null constraint, PostgreSQL will issue an error.,Besides the NOT NULL constraint, you can use a CHECK constraint to force a column to accept not NULL values. The NOT NULL constraint is equivalent to the following CHECK constraint:,To control whether a column can accept NULL, you use the NOT NULL constraint:

To check if a value is NULL or not, you use the IS NULL boolean operator. For example, the following expression returns true if the value in the email address is NULL.

.wp-block-code {
	border: 0;
	padding: 0;
}

.wp-block-code > div {
	overflow: auto;
}

.shcb-language {
	border: 0;
	clip: rect(1px, 1px, 1px, 1px);
	-webkit-clip-path: inset(50%);
	clip-path: inset(50%);
	height: 1px;
	margin: -1px;
	overflow: hidden;
	padding: 0;
	position: absolute;
	width: 1px;
	word-wrap: normal;
	word-break: normal;
}

.hljs {
	box-sizing: border-box;
}

.hljs.shcb-code-table {
	display: table;
	width: 100%;
}

.hljs.shcb-code-table > .shcb-loc {
	color: inherit;
	display: table-row;
	width: 100%;
}

.hljs.shcb-code-table .shcb-loc > span {
	display: table-cell;
}

.wp-block-code code.hljs:not(.shcb-wrap-lines) {
	white-space: pre;
}

.wp-block-code code.hljs.shcb-wrap-lines {
	white-space: pre-wrap;
}

.hljs.shcb-line-numbers {
	border-spacing: 0;
	counter-reset: line;
}

.hljs.shcb-line-numbers > .shcb-loc {
	counter-increment: line;
}

.hljs.shcb-line-numbers .shcb-loc > span {
	padding-left: 0.75em;
}

.hljs.shcb-line-numbers .shcb-loc::before {
	border-right: 1px solid #ddd;
	content: counter(line);
	display: table-cell;
	padding: 0 0.75em;
	text-align: right;
	-webkit-user-select: none;
	-moz-user-select: none;
	-ms-user-select: none;
	user-select: none;
	white-space: nowrap;
	width: 1%;
}email_address IS NULLCode language: PHP (php)

To control whether a column can accept NULL, you use the NOT NULL constraint:

CREATE TABLE table_name(
   ...
   column_name data_type NOT NULL,
   ...
);Code language: PHP (php)

The following CREATE TABLE statement creates a new table name invoices with the not-null constraints.

CREATE TABLE invoices(
  id SERIAL PRIMARY KEY,
  product_id INT NOT NULL,
  qty numeric NOT NULL CHECK(qty > 0),
  net_price numeric CHECK(net_price > 0) 
);Code language: SQL (Structured Query Language) (sql)

To add the NOT NULL constraint to a column of an existing table, you use the following form of the ALTER TABLE statement:

ALTER TABLE table_name
ALTER COLUMN column_name SET NOT NULL;Code language: SQL (Structured Query Language) (sql)

To add set multiple NOT NULL constraint to multiple columns, you use the following syntax:

ALTER TABLE table_name
ALTER COLUMN column_name_1 SET NOT NULL,
ALTER COLUMN column_name_2 SET NOT NULL,
...;Code language: SQL (Structured Query Language) (sql)

First, create a new table called production orders ( production_orders):

CREATE TABLE production_orders (
	id SERIAL PRIMARY KEY,
	description VARCHAR (40) NOT NULL,
	material_id VARCHAR (16),
	qty NUMERIC,
	start_date DATE,
	finish_date DATE
);Code language: SQL (Structured Query Language) (sql)

Next, insert a new row into the production_orders table:

INSERT INTO production_orders (description)
VALUES('Make for Infosys inc.');Code language: SQL (Structured Query Language) (sql)

To add the NOT NULL constraint to a column that already contains NULL, you need to update NULL to non-NULL first, like this:

UPDATE production_orders
SET qty = 1;Code language: SQL (Structured Query Language) (sql)

The values in the qty column are updated to one. Now, you can add the NOT NULL constraint to the qty column:

ALTER TABLE production_orders 
ALTER COLUMN qty
SET NOT NULL;Code language: SQL (Structured Query Language) (sql)

After that, you can update the not-null constraints for material_id, start_date, and finish_date columns:

UPDATE production_orders
SET material_id = 'ABC',
    start_date = '2015-09-01',
    finish_date = '2015-09-01';Code language: SQL (Structured Query Language) (sql)

Add not-null constraints to multiple columns:

ALTER TABLE production_orders 
ALTER COLUMN material_id SET NOT NULL,
ALTER COLUMN start_date SET NOT NULL,
ALTER COLUMN finish_date SET NOT NULL;Code language: SQL (Structured Query Language) (sql)

Finally, attempt to update values in the qty column to NULL:

UPDATE production_orders
SET qty = NULL;Code language: SQL (Structured Query Language) (sql)

PostgreSQL issued an error message:

[Err] ERROR:  null value in column "qty" violates not-null constraint
DETAIL:  Failing row contains (1, make for infosys inc., ABC, null, 2015-09-01, 2015-09-01).Code language: JavaScript (javascript)

Besides the NOT NULL constraint, you can use a CHECK constraint to force a column to accept not NULL values. The NOT NULL constraint is equivalent to the following CHECK constraint:

CHECK(column IS NOT NULL)Code language: SQL (Structured Query Language) (sql)

For example, you may want either username or email column of the user tables is not null or empty. In this case, you can use the CHECK constraint as follows:

CREATE TABLE users (
 id serial PRIMARY KEY,
 username VARCHAR (50),
 password VARCHAR (50),
 email VARCHAR (50),
 CONSTRAINT username_email_notnull CHECK (
   NOT (
     ( username IS NULL  OR  username = '' )
     AND
     ( email IS NULL  OR  email = '' )
   )
 )
);Code language: SQL (Structured Query Language) (sql)

The following statement works.

INSERT INTO users (username, email)
VALUES
	('user1', NULL),
	(NULL, '[email protected]'),
	('user2', '[email protected]'),
	('user3', '');Code language: SQL (Structured Query Language) (sql)

However, the following statement will not work because it violates the CHECK constraint:

INSERT INTO users (username, email)
VALUES
	(NULL, NULL),
	(NULL, ''),
	('', NULL),
	('', '');Code language: SQL (Structured Query Language) (sql)

INSERT INTO users (username, email)
VALUES
	(NULL, NULL),
	(NULL, ''),
	('', NULL),
	('', '');
Code language: SQL (Structured Query Language) (sql)

[Err] ERROR:  new row for relation "users" violates check constraint "username_email_notnull"Code language: JavaScript (javascript)

Answer by Anderson Alvarado

When I try to add one or more tags to a project, I get:

sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (2, puzzle, null).

[SQL: INSERT INTO tags (project_id, value) VALUES (%(project_id)s, %(value)s) RETURNING tags.id]
[parameters: {'project_id': 2, 'value': 'puzzle'}]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

This seems to be raised by the following code, in pygameweb.project.views line 326:

...
tag = Tags(project=project, value=value)
current_session.add(tag)
...
current_session.commit()

Answer by Collins Townsend

Недавно я начал переносить базу данных SQLite на PostGreSQL для сайта Flask, созданного с помощью SQLAlchemy. У меня есть мои схемы в PGSQL и даже вставлены данные в базу данных. Однако я не могу запускать свои обычные команды INSERT для добавления информации в базу данных. Обычно я вставляю новые записи с помощью SQL Alchemy, оставляя столбец ID равным NULL, а затем просто устанавливая другие столбцы. Однако это приводит к следующей ошибке:

sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, 2017-07-24 20:40:37.787393+00, 2017-07-24 20:40:37.787393+00, episode_length_list = [52, 51, 49, 50, 83]

sum_length = 0

for ..., 0, f, 101, 1, 0, 0, , null).
 [SQL: 'INSERT INTO submission (date_created, date_modified, code, status, correct, assignment_id, course_id, user_id, assignment_version, version, url) VALUES (CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, %(code)s, %(status)s, %(correct)s, %(assignment_id)s, %(course_id)s, %(user_id)s, %(assignment_version)s, %(version)s, %(url)s) RETURNING submission.id'] [parameters: {'code': 'episode_length_list = [52, 51, 49, 50, 83]nnsum_length = 0nnfor episode_length in episode_length_list:n    passnnsum_length = sum_length + episode_lengthnnprint(sum_length)n', 'status': 0, 'correct': False, 'assignment_id': 101, 'course_id': None, 'user_id': 1, 'assignment_version': 0, 'version': 0, 'url': ''}]

Вот мои объявления таблицы SQL Alchemy:

class Base(Model):
    __abstract__  = True
    @declared_attr
    def __tablename__(cls):
        return cls.__name__.lower()
    def __repr__(self):
        return str(self)

    id =  Column(Integer(), primary_key=True)
    date_created  = Column(DateTime, default=func.current_timestamp())
    date_modified = Column(DateTime, default=func.current_timestamp(),
                                     onupdate=func.current_timestamp())

class Submission(Base):
    code = Column(Text(), default="")
    status = Column(Integer(), default=0)
    correct = Column(Boolean(), default=False)
    assignment_id = Column(Integer(), ForeignKey('assignment.id'))
    course_id = Column(Integer(), ForeignKey('course.id'))
    user_id = Column(Integer(), ForeignKey('user.id'))
    assignment_version = Column(Integer(), default=0)
    version = Column(Integer(), default=0)
    url = Column(Text(), default="")

Проверяя сторону PostGreSQL, мы можем увидеть построенную таблицу:

                                     Table "public.submission"
       Column       |           Type           | Modifiers | Storage  | Stats target | Description
--------------------+--------------------------+-----------+----------+--------------+-------------
 id                 | bigint                   | not null  | plain    |              |
 date_created       | timestamp with time zone |           | plain    |              |
 date_modified      | timestamp with time zone |           | plain    |              |
 code               | text                     |           | extended |              |
 status             | bigint                   |           | plain    |              |
 correct            | boolean                  |           | plain    |              |
 assignment_id      | bigint                   |           | plain    |              |
 user_id            | bigint                   |           | plain    |              |
 assignment_version | bigint                   |           | plain    |              |
 version            | bigint                   |           | plain    |              |
 url                | text                     |           | extended |              |
 course_id          | bigint                   |           | plain    |              |
Indexes:
    "idx_16881_submission_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "submission_course_id_fkey" FOREIGN KEY (course_id) REFERENCES course(id)
    "submission_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id)
Has OIDs: no

Источник

Из статьи вы узнаете про ограничение целостности SQL. А также, на примере PostgreSQL, я покажу, как эти ограничения создаются.

Теория

В прошлой статье: “Базовые команды SQL” я показывал как создавать таблицы в СУБД PostgreSQL. Первичный ключ уже накладывает некоторые ограничения целостности. Например, все значения в поле, которое является первичным ключом должны быть уникальными.

Ограничение целостности SQL, это определённое требование для полей в таблице SQL. За соблюдение этих требований отвечает сама СУБД. Например:

Тип данных накладывает некоторое ограничение. Нельзя в поле с типом “integer” вставить текстовую строку. Но для некоторых типов можно ещё ужесточить правила:
- для типа “char” и “varchar” можно указать максимальную длину строки. Например, в поле “vaechar(4)” нельзя будет вставить строку из пяти символов.
Первичный ключ сразу накладывает несколько ограничений, хотя их можно наложить и поотдельности:
- записи должны быть уникальны (unique);
- записи не могут быть пустыми (not null).
Есть определённые проверки (check). С их помощью можно наложить дополнительные ограничения. Например, для цифрового значения можно указать что число должно быть больше 50, но меньше 200. Или поле, которое хранит пол человека, может принимать только два значения: “М” или “Ж”.

В документации об ограничениях можете почитать тут.

В этой статье будем говорить о “check” проверках, так как о первичном ключе и типах данных я уже рассказывал. Также покажу как создаются ограничения unique и not null.

Создание таблицы с ограничениями целостности

Создадим новую базу данных и подключимся к ней:

postgres=# CREATE DATABASE check_constraint;
CREATE DATABASE

postgres=# c check_constraint
You are now connected to database "check_constraint" as user "postgres".

Создадим таблицу staff (сотрудники):

Название	Описание	Тип данных	Ограничения ( NOT NULL, UNIQUE )	Ограничения (CHECK)
number	номер	serial	NOT NULL, UNIQUE
fullname	фио	varchar (50)	NOT NULL
sex	пол	char (1)	NOT NULL	Может быть только “М” или “Ж”
age	возраст	integer	NOT NULL	От 18 до 100 (я думаю больше 100 лет сотруднику не может быть)
experience	стаж	integer	NOT NULL	Стаж должен быть меньше возраста

Для создания подойдёт следующий запрос:

# CREATE TABLE staff
(number serial NOT NULL UNIQUE,
fullname varchar(50) NOT NULL,
sex char(1) NOT NULL, CHECK ( sex IN ('М', 'Ж')),
age integer NOT NULL, CHECK ( age > 18 AND age < 100),
experience integer NOT NULL, CHECK ( experience < age)
);
CREATE TABLE

И посмотрим на то, что у нас получилось:

# d staff
                                       Table "public.staff"
   Column   |         Type          | Collation | Nullable |                Default
------------+-----------------------+-----------+----------+---------------------------------------
 number     | integer               |           | not null | nextval('staff_number_seq'::regclass)
 fullname   | character varying(50) |           | not null |
 sex        | character(1)          |           | not null |
 age        | integer               |           | not null |
 experience | integer               |           | not null |
Indexes:
    "staff_number_key" UNIQUE CONSTRAINT, btree (number)
Check constraints:
    "staff_age_check" CHECK (age > 18 AND age < 100)
    "staff_check" CHECK (experience < age)
    "staff_sex_check" CHECK (sex = ANY (ARRAY['М'::bpchar, 'Ж'::bpchar]))

Попробуем создать 15 летнего сотрудника:

# INSERT INTO staff (fullname, sex, age, experience)
VALUES ('Иванов Иван Алексеевич', 'М', 15, 10);
ERROR:  new row for relation "staff" violates check constraint "staff_age_check"
DETAIL:  Failing row contains (1, Иванов Иван Алексеевич, М, 15, 10).

Как видно из ошибки, сработало ограничение staff_age_check.

А 18 летний создастся нормально:

# INSERT INTO staff (fullname, sex, age, experience)
VALUES ('Иванов Иван Алексеевич', 'М', 20, 10);
INSERT 0 1

А что будет, если ошибёмся с полом:

# INSERT INTO staff (fullname, sex, age, experience)
VALUES ('Донченко Иван Андреевич', 'К', 25, 8);
ERROR:  new row for relation "staff" violates check constraint "staff_sex_check"
DETAIL:  Failing row contains (3, Донченко Иван Андреевич, К, 25, 8).

Здесь уже сработало ограничение staff_sex_check.

Если мы что-то забудем указать, то сработает ограничение NOT NULL:

# INSERT INTO staff (fullname, sex, age)
VALUES ('Донченко Иван Андреевич', 'Ж', 25);
ERROR:  null value in column "experience" of relation "staff" violates not-null constraint
DETAIL:  Failing row contains (4, Донченко Иван Андреевич, Ж, 25, null).

И наконец введем второго пользователя в базу:

# INSERT INTO staff (fullname, sex, age, experience)
VALUES ('Донченко Иван Андреевич', 'М', 25, 8);
INSERT 0 1

А теперь посмотрим на табличку:

check_constraint=# SELECT * FROM staff;
 number |        fullname         | sex | age | experience
--------+-------------------------+-----+-----+------------
      2 | Иванов Иван Алексеевич  | М   |  20 |         10
      5 | Донченко Иван Андреевич | М   |  25 |          8
(2 rows)

Как вы могли заметить последовательность постоянно растёт, даже если мы ошибаемся при вставке. Поэтому у нас в поле number вначале 2, а затем 5.

Создание ограничения для существующей таблицы

NOT NULL

Добавить или удалить ограничение целостности NOT NULL можно с помощью ALTER TABLE таким образом:

# ALTER TABLE staff ALTER COLUMN number DROP NOT NULL;
# ALTER TABLE staff ALTER COLUMN number SET NOT NULL;

UNIQUE

Ограничения целостности UNIQUE удаляются с помощью DROP CONSTRAINT, а добавляется с помощью ADD CONSTRAINT:

# ALTER TABLE staff DROP CONSTRAINT staff_number_key;
# ALTER TABLE staff ADD CONSTRAINT staff_number_key UNIQUE (number);

Кстати, при удалении ограничения целостности UNIQUE удаляется и индекс.

CHECK

Ограничения целостности CHECK создаются и удаляются подобным способом:

# ALTER TABLE staff DROP CONSTRAINT staff_age_check;
# ALTER TABLE staff ADD CONSTRAINT staff_age_check CHECK (age > 18 AND age < 100);

При этом названия ограничения (staff_number_key, staff_age_check и другие) вы придумываете сами.

Сводка

Имя статьи

Ограничение целостности в SQL

Описание

Я покажу вам на примере PostgreSQL, что такое ограничения целостности SQL. А также, как указать ограничения целостности для полей таблицы при её создании или для уже существующей таблицы

Источник

Issue

I have a question regarding the @GeneratedValue annotation from Spring JPA.

This is my class:

@Entity
@NoArgsConstructor
@Getter
@Setter
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private Double price;
    private Integer quantity;

    @ManyToOne
    private Category category;

    @ManyToOne
    private Manufacturer manufacturer;



    public Product(String name, Double price, Integer quantity, Category category, Manufacturer manufacturer) {
        this.name = name;
        this.price = price;
        this.quantity = quantity;
        this.category = category;
        this.manufacturer = manufacturer;
    }
}

And when I try to add a new product I get this error:

2021-12-06 18:03:02.013 ERROR 3720 --- [nio-9090-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: null value in column "id" of relation "product" violates not-null constraint
  Detail: Failing row contains (null, Dress, 333, 33, 1, 1).
2021-12-06 18:03:02.028 ERROR 3720 --- [nio-9090-exec-3] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [id" of relation "product]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement] with root cause

org.postgresql.util.PSQLException: ERROR: null value in column "id" of relation "product" violates not-null constraint
  Detail: Failing row contains (null, Dress, 333, 33, 1, 1).

Here is the save method from my service class:

@Override
    public Optional<Product> save(String name, Double price, Integer quantity, Long categoryId, Long manufacturerId) {
        Category category = this.categoryRepository.findById(categoryId).orElseThrow(() -> new CategoryNotFoundException(categoryId));
        Manufacturer manufacturer = this.manufacturerRepository.findById(manufacturerId).orElseThrow(() -> new ManufacturerNotFoundException(manufacturerId));

        this.productRepository.deleteByName(name);

        return Optional.of(this.productRepository.save(new Product(name, price, quantity, category, manufacturer)));
    }

Honestly I don’t realize how this happens. I read somewhere that GenerationType.IDENTITY is not supported by PostgreSQL, but I don’t think that’s it because I used the same generation type for the Category and Manufacturer tables and I can add new tuples to them no problem. I tried using generation type SEQUENCE and it works, but I don’t want to use that generation type and I don’t understand why IDENTITY won’t work here when it worked on the other who tables. Any ideas?

Edit 1: Tested it with GenerationType.AUTO and it works, but still shows the same error for type IDENTITY.

Edit 2: Sharing the SQL for creating the Product table and Category table.

CREATE TABLE IF NOT EXISTS public.product
(
    id bigint NOT NULL,
    name character varying(255) COLLATE pg_catalog."default",
    price double precision,
    quantity integer,
    category_id bigint,
    manufacturer_id bigint,
    CONSTRAINT product_pkey PRIMARY KEY (id),
    CONSTRAINT fk1mtsbur82frn64de7balymq9s FOREIGN KEY (category_id)
        REFERENCES public.category (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT fkq5vxx1bolpwm1sngn6krtwsjn FOREIGN KEY (manufacturer_id)
        REFERENCES public.manufacturers (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

TABLESPACE pg_default;

ALTER TABLE public.product
    OWNER to wp;

CREATE TABLE IF NOT EXISTS public.category
(
    id bigint NOT NULL DEFAULT nextval('category_id_seq'::regclass),
    description character varying(4000) COLLATE pg_catalog."default",
    name character varying(255) COLLATE pg_catalog."default",
    CONSTRAINT category_pkey PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE public.category
    OWNER to wp;

Final edit: My question has been answered, for some reason my primary key was not set to auto increment and that was causing the problems. I believe it was because I created the table with just the @Id annotation and after it was created I added the generation strategy (which did not set the id to auto increment.

Solution

For it to work, you have to change somethings in your Product table.

@GeneratedValue(strategy = GenerationType.IDENTITY) just work if your ID column is SERIAL type.

Check if the ID column is that type. Example:

CREATE TABLE Product(
    id SERIAL NOT NULL,
    name VARCHAR(20),
    etc...
)

Answered By — Nahuel Giani
Answer Checked By — Dawn Plyler (JavaFixing Volunteer)

Источник