Ошибка insert или update в таблице

I have two tables: entitytype and project. Here are the create table statements:

Create table project ( 
pname varchar(20) not null, 
primary key(pname)
);

create table entitytype( 
entityname varchar(20) not null, 
toppos char(100), 
leftpos char(100), 
pname varchar(20) not null, 
primary key(entityname), 
foreign key(pname) references project(pname) on delete cascade on update cascade
);

When I try to insert any values into the entitytype table, I get the following error:

ERROR: insert or update on table "entitytype" violates foreign key constraint "entitytype_pname_fkey"
  Detail: Key (pname)=(494) is not present in table "project".

Can anyone shed some light on what I am doing wrong?

There are a few problems with your tables. I’ll try to address the foreign keys first, since you question asked about them

But before that, we should realize that the two sets of tables (the first three you created and the second set, which you created after dropping the first set) are the same. Of course, the definition of Table3 in your second attempt has syntax and logical errors, but the basic idea is:

CREATE TABLE table3 (   
  "ID" bigint NOT NULL DEFAULT '0',   
  "DataID" bigint DEFAULT NULL,   
  "Address" numeric(20) DEFAULT NULL,   
  "Data" bigint DEFAULT NULL,
   PRIMARY KEY ("ID"),   
   FOREIGN KEY ("DataID") REFERENCES Table1("DataID") on delete cascade on update cascade,   
   FOREIGN KEY ("Address") REFERENCES Table2("Address") on delete cascade on update cascade
);

This definition tell PostgreSQL roughly the following: «Create a table with four columns, one will be the primary key (PK), the others can be NULL. If a new row is inserted, check DataID and Address: if they contain a non-NULL value (say 27856), then check Table1 for DataID˙and Table2 for Address. If there is no such value in those tables, then return an error.» This last point which you’ve seen first:

ERROR: insert or update on table "Table3" violates foreign key constraint 
    "Table3_DataID_fkey" DETAIL: Key (DataID)=(27856) is not present in table "Table1".

So simple: if there is no row in Table1 where DataID = 27856, then you can’t insert that row into Table3.

If you need that row, you should first insert a row into Table1 with DataID = 27856, and only then try to insert into Table3. If this seems to you not what you want, please describe in a few sentences what you want to achieve, and we can help with a good design.

And now about the other problems.

You define your PKs as

CREATE all_your_tables (
    first_column NOT NULL DEFAULT '0',   
    [...]
    PRIMARY KEY ("ID"),  

A primary key means that all the items in it are different from each other, that is, the values are UNIQUE. If you give a static DEFAULT (like '0') to a UNIQUE column, you will experience bad surprises all the time. This is what you got in your third error message.

Furthermore, '0' means a text string, but not a number (bigint or numeric in your case). Use simply 0 instead (or don’t use it at all, as I written above).

And a last point (I may be wrong here): in Table2, your Address field is set to numeric(20). At the same time, it is the PK of the table. The column name and the data type suggests that this address can change in the future. If this is true, than it is a very bad choice for a PK. Think about the following scenario: you have an address ‘1234567890454’, which has a child in Table3 like

ID        DataID           Address             Data
123       3216547          1234567890454       654897564134569

Now that address happens to change to something other. How do you make your child row in Table3 follow its parent to the new address? (There are solutions for this, but can cause much confusion.) If this is your case, add an ID column to your table, which will not contain any information from the real world, it will simply serve as an identification value (that is, ID) for an address.

class ProductPrice(models.Model):
    price = models.DecimalField(max_digits=8, decimal_places=2, default=0.00)
    date_with = models.DateTimeField(auto_now_add=True)
    date_by = models.DateTimeField()

    class Meta:
        verbose_name = 'цена'
        verbose_name_plural = 'цены'

    def __str__(self) -> str:
        return f'Цена товара:'

class Products(models.Model):
    product = models.CharField(max_length=100, db_index=True)
    slug = models.SlugField(max_length=200, db_index=True)
    description = models.TextField(max_length=500)
    image = models.ImageField(blank=True, null=True, upload_to='products_images/', verbose_name='Изображение')  
    category = models.ForeignKey(to=ProductsCategory, on_delete=models.PROTECT)
    available = models.BooleanField(default=True)
    created = models.DateTimeField(auto_now_add=True, verbose_name='Дата создания')
    size = models.CharField(max_length=30, blank=True, verbose_name='Размер')
    volume = models.PositiveIntegerField(blank=True, verbose_name='Объем', default=0)
    color_product = models.CharField(max_length=30, blank=True)
    price = models.ForeignKey(to=ProductPrice, on_delete=models.DO_NOTHING, to_field=id)
    stock = models.ManyToManyField(
        Stock, 
        through='ProductStock',
        through_fields=('product', 'stock')
        )

    class Meta:
        ordering = ('product',)
        index_together = (('id', 'slug'),)
        verbose_name = 'Продукт'
        verbose_name_plural = 'Продукты'

    def __str__(self) -> str:
        return f'Продукт: {self.product} | {self.category}'
    
    def get_absolute_url(self):
        return reverse('product:product_detail',
                        args=[self.id, self.slug])

Не могу понять в чем проблема. Выдаёт следующую ошибку при миграции:

Running migrations:
  Applying product.0002_auto_20230403_1141...Traceback (most recent call last):
  File "E:PYTHON_HTML_CSScourse-work_djangocourse-work_djangonenvlibsite-packagesdjangodbbackendsutils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.ForeignKeyViolation: ОШИБКА:  INSERT или UPDATE в таблице "product_products" нарушает ограничение внешнего ключа "product_products_price_id_0e1fa44e_fk_product_productprice_id"
DETAIL:  Ключ (price_id)=(0) отсутствует в таблице "product_productprice".


The above exception was the direct cause of the following exception:
<code>
Applying product.0002_auto_20230403_1141...Traceback (most recent call last):
  File "E:PYTHON_HTML_CSScourse-work_djangocourse-work_djangonenvlibsite-packagesdjangodbbackendsutils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.ForeignKeyViolation: ОШИБКА:  INSERT или UPDATE в таблице "product_products" нарушает ограничение внешнего ключа "product_products_price_id_0e1fa44e_fk_product_productprice_id"
DETAIL:  Ключ (price_id)=(0) отсутствует в таблице "product_productprice".

django.db.utils.IntegrityError: ОШИБКА:  INSERT или UPDATE в таблице "product_products" нарушает ограничение внешнего ключа "product_products_price_id_0e1fa44e_fk_product_productprice_id"
DETAIL:  Ключ (price_id)=(0) отсутствует в таблице "product_productprice".
</code>

Программа и сервер(на Spring boot) запускается, но в консоли вижу ошибку:

Caused by: org.postgresql.util.PSQLException: ОШИБКА: INSERT или UPDATE в таблице "city" нарушает ограничение внешнего ключа "fktjrg7h2j3ehgycr3usqjgnc2u"
  Подробности: Ключ (id)=(1) отсутствует в таблице "house"

Мне кажется, что связи некорректные, либо ещё что-то, но вариантов особо нет, даже догадаться не могу, потому что когда добавляю квартиру (подумал, что города как-то зависят от домов), то ошибка меняется в цифре, была (id)=(1), а становится (id)=(2).

HOUSE:

@Data
@Entity
@Table (name = "house", schema = "task")
public class House {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    // OnrToMany к городу
    @OneToMany(cascade = {CascadeType.DETACH, CascadeType.MERGE, CascadeType.PERSIST,
            CascadeType.REFRESH
    })
    @JoinColumn(name = "id")
    private Set<City> city;

    @Column(name = "id_landlord", nullable = false)
    private Long id_landlord;
    @Column(name = "outside", nullable = false)
    private String outside;
    @Column(name = "rooms", nullable = false)
    private Integer rooms;
    @Column(name = "price", nullable = false)
    private Double price;
    @Column(name = "description", nullable = false)
    private String description;
}

CITY:

@Data
@Entity
@Table(name = "city", schema = "task")
public class City {

    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @Column(name = "id_region", nullable = false)
    private Integer id_region;
    @Column(name = "name", nullable = false)
    private String name;
}

0

2

CREATE TABLE item (
  item_id SERIAL PRIMARY KEY,
  name VARCHAR(127) NOT NULL,
  manufacturer_id INT
);
CREATE TABLE matroska (
  count INT NOT NULL -- вложенность матрёшек, не применимо к другим товарам
) INHERITS (item);
CREATE TABLE "order" (
  order_id SERIAL PRIMARY KEY,
  customer_id INT
);
 CREATE TABLE order_items (
  order_id INT NOT NULL REFERENCES "order",
  item_id INT NOT NULL REFERENCES item
);
insert into matroska (name, manufacturer_id, count) values ('Маша', 9001, 9);
insert into "order" (customer_id) values (100500);

=> select * from matroska ;
 item_id | name | manufacturer_id | count
---------+------+-----------------+-------
       1 | Маша |            9001 |     9
(1 строка)

=> select * from item;
 item_id | name | manufacturer_id
---------+------+-----------------
       1 | Маша |            9001
(1 строка)

=> select * from "order";
 order_id | customer_id
----------+-------------
        1 |      100500
(1 строка)

=> insert into order_items values (1,1);
ОШИБКА:  INSERT или UPDATE в таблице "order_items" нарушает ограничение внешнего ключа "order_items_item_id_fkey"
ПОДРОБНОСТИ:  Ключ (item_id)=(1) отсутствует в таблице "item".