Foreign key violation 7 error insert or update on table

I am a new one in postgreSQL. I have 3 tables, one table references the other 2 table's primary keys. But I couldn't insert data into the Table3. See the code below : DROP TABLE Table1 CASCADE; CR...

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.

Источник

Я действительно новичок в этом деле. Дело в том … У меня есть статья на сайте. Люди могут оценивать статьи там. Если никто не оценил, я могу удалить статью. Но если кто-то оценил статью, я продолжаю получать следующую ошибку:

PDOException: SQLSTATE[23503]: Foreign key violation: 7 ERROR:  update or delete on table "article" violates foreign key constraint "article_rating_item_id_fkey" on table "article_ratings"DETAIL:  Key (id)=(xxxx) is still referenced from table "article_ratings". in /libs/Nette/loader.php:3515  @  http://www.xxxxxx/admin/articleedit/3578?do=deletearticle  @@  exception-2014-09-29-18-14-37-b625334b3e569cb7661f1704256874c1.htm

Когда я проверяю этот файл, появляется следующий код:

public function handleDeletearticle($id)
{
$article = $this->context->createArticles()->get($id);
$this->context->createArticles()->where("id", $id)->delete();
$this->flashMessage('Done', 'success');
$this->redirect('Admin:articles');
}

Не могли бы вы помочь мне, как это исправить? заранее спасибо

РЕДАКТИРОВАТЬ: это выглядит как Articles.php

    public function selectArticleWithRating($slug)
{
$article = $this->query("Select article.*, COUNT(rating.id) AS plus, COUNT(rating2.id) AS minus, "user".avatar, "user".username
FROM article
LEFT JOIN rating AS rating ON rating.item_id=article.id and rating.type='article' and rating.rate=1
LEFT JOIN rating AS rating2 ON rating2.item_id=article.id and rating2.type='article' and rating2.rate=0
LEFT JOIN "user" ON "user".id=article.user_id
WHERE slug='$slug'
GROUP BY article.id, "user".id");

return $article;
}

Там не должно быть article_ratings?

0

Решение

Это действительно говорит о том, что в полученном вами сообщении об ошибке у вас есть нарушение ссылки на внешний ключ. Это означает, что вы пытаетесь удалить строку, на которую ссылается где-то в вашей базе данных, она даже сообщает вам, где: 

is still referenced from table "article_ratings"

Вы также можете удалить строку ссылки, используя ON DELETE CASCADE
http://www.mysqltutorial.org/mysql-on-delete-cascade/

Есть вопрос, касающийся этого здесь, на SO: MySQL на каскаде удаления. Тестовый Пример

И отличное объяснение здесь: https://dba.stackexchange.com/questions/44956/good-explanation-of-cascade-on-delete-update-behavior

Редактировать: На Postgres:

CREATE TABLE order_items (
product_no integer REFERENCES products ON DELETE RESTRICT,
order_id integer REFERENCES orders ON DELETE CASCADE,
quantity integer,
PRIMARY KEY (product_no, order_id)
);

http://www.postgresql.org/docs/9.3/static/ddl-constraints.html

1

Другие решения

В качестве другого варианта ответа, данного @hebron, который основывается на изменении поведения каскадного удаления внешнего ключа, вы можете найти его более простым и понятным в вашем коде (то есть, не полагаясь на «скрытое» поведение базы данных) для удаления через объединение.

DELETE articles, article_ratings
FROM articles
LEFT JOIN article_ratings
ON articles.id = article_ratings.article_id /* or whatever your foreign key name is */
WHERE articles.id = ?

0

Источник

Hello People,

Found some issue when dealing with postgresql:


/opt/lappstack-7.1.9-0/apache2/htdocs/fusio# php bin/fusio install
Install version 1.0.3

                                                                               
  [DoctrineDBALExceptionDriverException]                                    
  An exception occurred while executing 'INSERT INTO fusio_schema (status, na  
  me, source, cache) VALUES (?, ?, ?, ?)' with params [1, "Passthru", "{n     
   "id": "http:\/\/fusio-project.org",n    "title": "passthru",  
  n    "type": "object",n    "description": "No schema was specified.  
  ",n    "properties": {}n}", "C:17:"PSX\Schema\Schema":826:{O:23:"  
  PSX\Schema\PropertyType":33:{s:7:"u0000*u0000type";s:6:"object";s:  
  13:"u0000*u0000properties";N;s:20:"u0000*u0000patternProperties";N;  
  s:23:"u0000*u0000additionalProperties";N;s:16:"u0000*u0000minPropert  
  ies";N;s:16:"u0000*u0000maxProperties";N;s:11:"u0000*u0000required  
  ";N;s:15:"u0000*u0000dependencies";N;s:8:"u0000*u0000items";N;s:18:  
  "u0000*u0000additionalItems";N;s:14:"u0000*u0000uniqueItems";N;s:10  
  :"u0000*u0000minimum";N;s:10:"u0000*u0000maximum";N;s:19:"u0000*  
  u0000exclusiveMinimum";N;s:19:"u0000*u0000exclusiveMaximum";N;s:11:"  
  u0000*u0000minItems";N;s:11:"u0000*u0000maxItems";N;s:10:"u0000*u0  
  000pattern";N;s:12:"u0000*u0000minLength";N;s:12:"u0000*u0000maxLen  
  gth";N;s:7:"u0000*u0000enum";N;s:8:"u0000*u0000const";N;s:10:"u0  
  000*u0000default";N;s:8:"u0000*u0000title";s:8:"passthru";s:14:"u  
  0000*u0000description";s:24:"No schema was specified.";s:9:"u0000*u0  
  000format";N;s:13:"u0000*u0000multipleOf";N;s:8:"u0000*u0000allOf"  
  ;N;s:8:"u0000*u0000anyOf";N;s:8:"u0000*u0000oneOf";N;s:6:"u0000*  
  u0000not";N;s:6:"u0000*u0000ref";s:24:"http://fusio-project.org";s  
  :8:"u0000*u0000class";N;}}"]:                                            
                                                                               
  SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input synta  
  x for type bytea                                                             
                                                                               

                                                                               
  [DoctrineDBALDriverPDOException]                                          
  SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input synta  
  x for type bytea                                                             
                                                                               

                                                                               
  [PDOException]                                                               
  SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input synta  
  x for type bytea                                                             
                                                                               

system:install

/opt/lappstack-7.1.9-0/apache2/htdocs/fusio# php bin/fusio deploy 
An exception occurred during deploy. No changes are applied to the database.

Message: An exception occurred while executing 'INSERT INTO fusio_app_token (appId, userId, status, token, scope, ip, expire, date) VALUES (?, ?, ?, ?, ?, ?, ?, ?)' with params [1, 1, 1, "1ce86ceb8fae0df76896-1ea4885d27d22f277a9251c2477365b1b21da16c8f52ca8a-089ca16cd6", "backend", "127.0.0.1", "2017-10-14 03:07:31", "2017-10-14 02:37:31"]:

SQLSTATE[23503]: Foreign key violation: 7 ERROR:  insert or update on table "fusio_app_token" violates foreign key constraint "apptokenappid"
DETAIL:  Key (appid)=(1) is not present in table "fusio_app".
Trace: #0 /opt/lappstack-7.1.9-0/apache2/htdocs/fusio/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php(176): DoctrineDBALDriverAbstractPostgreSQLDriver->convertException('An exception oc...', Object(DoctrineDBALDriverPDOException))
#1 /opt/lappstack-7.1.9-0/apache2/htdocs/fusio/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php(150): DoctrineDBALDBALException::wrapException(Object(DoctrineDBALDriverPDOPgSqlDriver), Object(DoctrineDBALDriverPDOException), 'An exception oc...')
#2 /opt/lappstack-7.1.9-0/apache2/htdocs/fusio/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(1065): DoctrineDBALDBALException::driverExceptionDuringQuery(Object(DoctrineDBALDriverPDOPgSqlDriver), Object(DoctrineDBALDriverPDOException), 'INSERT INTO fus...', Array)
#3 /opt/lappstack-7.1.9-0/apache2/htdocs/fusio/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(775): DoctrineDBALConnection->executeUpdate('INSERT INTO fus...', Array, Array)
#4 /opt/lappstack-7.1.9-0/apache2/htdocs/fusio/vendor/fusio/impl/src/Service/System/ApiExecutor.php(125): DoctrineDBALConnection->insert('fusio_app_token', Array)
#5 /opt/lappstack-7.1.9-0/apache2/htdocs/fusio/vendor/fusio/impl/src/Service/System/ApiExecutor.php(88): FusioImplServiceSystemApiExecutor->getAccessToken()
#6 /opt/lappstack-7.1.9-0/apache2/htdocs/fusio/vendor/fusio/impl/src/Service/System/SystemAbstract.php(92): FusioImplServiceSystemApiExecutor->request('POST', 'connection', Object(stdClass))
#7 /opt/lappstack-7.1.9-0/apache2/htdocs/fusio/vendor/fusio/impl/src/Service/System/Import.php(111): FusioImplServiceSystemSystemAbstract->doRequest('POST', 'connection', Object(stdClass))
#8 /opt/lappstack-7.1.9-0/apache2/htdocs/fusio/vendor/fusio/impl/src/Service/System/Import.php(70): FusioImplServiceSystemImport->importGeneral('connection', Object(stdClass))
#9 /opt/lappstack-7.1.9-0/apache2/htdocs/fusio/vendor/fusio/impl/src/Service/System/Deploy.php(100): FusioImplServiceSystemImport->import(Object(stdClass))
#10 /opt/lappstack-7.1.9-0/apache2/htdocs/fusio/vendor/fusio/impl/src/Console/System/DeployCommand.php(109): FusioImplServiceSystemDeploy->deploy(Array, '/opt/lappstack-...')
#11 /opt/lappstack-7.1.9-0/apache2/htdocs/fusio/vendor/symfony/console/Command/Command.php(262): FusioImplConsoleSystemDeployCommand->execute(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))
#12 /opt/lappstack-7.1.9-0/apache2/htdocs/fusio/vendor/symfony/console/Application.php(888): SymfonyComponentConsoleCommandCommand->run(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))
#13 /opt/lappstack-7.1.9-0/apache2/htdocs/fusio/vendor/symfony/console/Application.php(224): SymfonyComponentConsoleApplication->doRunCommand(Object(FusioImplConsoleSystemDeployCommand), Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))
#14 /opt/lappstack-7.1.9-0/apache2/htdocs/fusio/vendor/symfony/console/Application.php(125): SymfonyComponentConsoleApplication->doRun(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))
#15 /opt/lappstack-7.1.9-0/apache2/htdocs/fusio/bin/fusio(47): SymfonyComponentConsoleApplication->run()
#16 {main}

Please kindly help attend to the mention matter.

Thanks :-)

Источник

Summary: in this tutorial, you will learn how to perform transactions in PostgreSQL using PHP PDO.

A transaction is a series of operations performed as a single logical unit of work. A transaction has four characteristics:

  • Atomicity
  • Consistency
  • Isolation
  • Durability

These characteristics are referred to as (ACID).

By default, PostgreSQL uses the auto-commit mode. This means that for every statement that the application issues, PostgreSQL commits it automatically.

To turn off the auto-commit mode in PHP, you call the beginTransaction() method of the PDO object. By doing this, the change to the database is made only when the commit() method of the PDO object is called.

If there is an exception or error happens, you can cancel the change using the rollback() method of the PDO object.

The typical usage of the transaction in PHP PDO is as follows:

<?php

try {
    $pdo->beginTransaction();

    $pdo->query("SELECT * FROM table");

    $stmt = $pdo->prepare("UPDATE QUERY");
    $stmt->execute();

    $stmt = $pdo->prepare("ANOTHER UPADTE QUERY");
    $stmt->execute();

    $db->commit();
} catch (PDOException $e) {
    $db->rollBack();
    throw $e;
}
Code language: PHP (php)

We’ll create the following tables for the demonstration:

  1.  accounts: stores the account information such as first name, last name
  2.  plans: stores the plan information for the account such as silver, gold, and platinum.
  3.  account_plans : stores the plan for each account with the effective date.

The following CREATE TABLE statements create the three tables:

CREATE TABLE accounts(
   id SERIAL PRIMARY KEY,
   first_name CHARACTER VARYING(100),
   last_name CHARACTER VARYING(100)
);

CREATE TABLE plans(
   id SERIAL PRIMARY KEY,
   plan CHARACTER VARYING(10) NOT NULL
);

CREATE TABLE account_plans(
   account_id INTEGER NOT NULL,
   plan_id INTEGER NOT NULL,
   effective_date DATE NOT NULL,
   PRIMARY KEY (account_id,plan_id),
   FOREIGN KEY(account_id) REFERENCES accounts(id),
   FOREIGN KEY(plan_id) REFERENCES plans(id)
 
);
Code language: SQL (Structured Query Language) (sql)

The following INSERT statement inserts some sample data into the plans table.

INSERT INTO plans(plan) VALUES('SILVER'),('GOLD'),('PLATINUM');
Code language: SQL (Structured Query Language) (sql)

When creating an account, you need to assign a plan that can be silver, gold, or platinum. To ensure that an account always has at least one plan at a time, you use the transaction API in PDO.

The following addAccount() method performs two main steps:

  1. First, insert an account into the accounts table and returns the account id.
  2. Then, assign the account a specific plan by inserting a new row into the account_plans table.

At the beginning of the method, you call the beginTransaction() method of the PDO object to start the transaction.

If all the steps succeed, you call the commit() method to save the changes. If an exception occurs in any step, you roll back the changes by calling the rollback() method in the catch block.

   /**
     * Add a new account
     * @param string $firstName
     * @param string $lastName
     * @param int $planId
     * @param date $effectiveDate
     */
    public function addAccount($firstName, $lastName, $planId, $effectiveDate) {
        try {
            // start the transaction
            $this->pdo->beginTransaction();

            // insert an account and get the ID back
            $accountId = $this->insertAccount($firstName, $lastName);

            // add plan for the account
            $this->insertPlan($accountId, $planId, $effectiveDate);

            // commit the changes
            $this->pdo->commit();
        } catch (PDOException $e) {
            // rollback the changes
            $this->pdo->rollBack();
            throw $e;
        }
    }
Code language: PHP (php)

The addAccount() method uses two other private methods: insertAccount() and insertPlan() as the following:

   /**
     * 
     * @param string $firstName
     * @param string $lastName
     * @return int
     */
    private function insertAccount($firstName, $lastName) {
        $stmt = $this->pdo->prepare(
                'INSERT INTO accounts(first_name,last_name) '
                . 'VALUES(:first_name,:last_name)');

        $stmt->execute([
            ':first_name' => $firstName,
            ':last_name' => $lastName
        ]);

        return $this->pdo->lastInsertId('accounts_id_seq');
    }
Code language: PHP (php)
   /**
     * insert a new plan for an account
     * @param int $accountId
     * @param int $planId
     * @param int $effectiveDate
     * @return bool
     */
    private function insertPlan($accountId, $planId, $effectiveDate) {
        $stmt = $this->pdo->prepare(
                'INSERT INTO account_plans(account_id,plan_id,effective_date) '
                . 'VALUES(:account_id,:plan_id,:effective_date)');

        return $stmt->execute([
                    ':account_id' => $accountId,
                    ':plan_id' => $planId,
                    ':effective_date' => $effectiveDate,
        ]);
    }
Code language: PHP (php)

To test the AccountDB class, you use the following code in the index.php file.

<?php

require 'vendor/autoload.php';

use PostgreSQLTutorialConnection as Connection;
use PostgreSQLTutorialAccountDB as AccountDB;

try {
    // connect to the PostgreSQL database
    $pdo = Connection::get()->connect();

    $accountDB = new AccountDB($pdo);

    // add accounts
    $accountDB->addAccount('John', 'Doe', 1, date('Y-m-d'));
    $accountDB->addAccount('Linda', 'Williams', 2, date('Y-m-d'));
    $accountDB->addAccount('Maria', 'Miller', 3, date('Y-m-d'));


    echo 'The new accounts have been added.' . '<br>';
    // 
    $accountDB->addAccount('Susan', 'Wilson', 99, date('Y-m-d'));
} catch (PDOException $e) {
    echo $e->getMessage();
}
Code language: PHP (php)

How it works.

  • First, connect to the PostgreSQL database.
  • Second, insert three accounts with silver, gold, and platinum levels.
  • Third, try to insert one more account but with a plan id that does not exist in the plans table. Based on the input, the step of assigning the plan to the account fails that cause the whole transaction to be rolled back.

The following shows the output of the index.php file:

The new accounts have been added.

SQLSTATE[23503]: Foreign key violation: 7 ERROR: insert or update on table "account_plans" violates foreign key constraint "account_plans_plan_id_fkey" DETAIL: Key (plan_id)=(99) is not present in table "plans".
Code language: JavaScript (javascript)

If you query the data in the accounts and account_plans tables, you will see only three rows inserted in each table:

stocks=# SELECT * FROM accounts;
 id | first_name | last_name
----+------------+-----------
  1 | John       | Doe
  2 | Linda      | Williams
  3 | Maria      | Miller
(3 rows)

stocks=# SELECT * FROM account_plans;
 account_id | plan_id | effective_date
------------+---------+----------------
          1 |       1 | 2016-06-13
          2 |       2 | 2016-06-13
          3 |       3 | 2016-06-13
(3 rows)

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

Summary

  • Use the beginTransaction() method of the PDO object to start a transaction.
  • Use the commit() method to apply the changes to the database and rollback() method to undo the changes.

Was this tutorial helpful ?

Источник

You can’t disable a foreign key constraint in Postgres, like you can do in Oracle. However, you can remove the foreign key constraint from a column and then re-add it to the column.

Here’s a quick test case in five steps:

  1. Drop the big and little table if they exists. The first drop statement requires a cascade because there is a dependent little table that holds a foreign key constraint against the primary key column of the big table. The second drop statement does not require the cascade keyword because there is not a dependent foreign key constraint. 
    DROP TABLE IF EXISTS big CASCADE;
DROP TABLE IF EXISTS little;
  2. Create the big and little tables: 
    -- Create the big table.
CREATE TABLE big
( big_id     SERIAL
, big_text   VARCHAR(20) NOT NULL
, CONSTRAINT pk_little_1 PRIMARY KEY (big_id));
 
-- Display the big table.
d big
 
-- Create little table.
CREATE TABLE little
( little_id     SERIAL
, big_id        INTEGER     NOT NULL
, little_text   VARCHAR(20) NOT NULL
, CONSTRAINT fk_little_1 FOREIGN KEY (big_id) REFERENCES big (big_id));
 
-- Display the little table.
d little

    If you failed to designate the big_id column as a primary key constrained, Postgres will raise the following exception:

    ERROR:  there IS no UNIQUE CONSTRAINT matching given KEYS FOR referenced TABLE "big"
  3. Insert a non-compliant row in the little table. An insert statement into the little table with a value for the foreign key column that does not exist in the big_id column of the big table would fail with the following error: 
    ERROR:  INSERT OR UPDATE ON TABLE "little" violates FOREIGN KEY CONSTRAINT "fk_little_1"
DETAIL:  KEY (big_id)=(2) IS NOT present IN TABLE "big".

    Re-enabling the foreign key constraint, the insert statement succeeds after you first insert a new row into the big table with the foreign key value for the little table as its primary key. The following two insert statements add a row to both the big and little table:

    -- Insert into a big table.
INSERT INTO big
(big_text)
VALUES
('Cat in the Hat 2');
 
-- Insert into a little table.
INSERT INTO little
(big_id
,little_text)
VALUES
( 2
,'Thing 3');

    Then, you can query it like this:

    SELECT *
FROM   big b JOIN little l ON b.big_id = l.big_id;
     big_id |     big_text     | little_id | big_id | little_text 
--------+------------------+-----------+--------+-------------
      1 | Cat IN the Hat 1 |         1 |      1 | Thing 1
      1 | Cat IN the Hat 1 |         2 |      1 | Thing 2
      2 | Cat IN the Hat 2 |         3 |      2 | Thing 3
(3 ROWS)
  4. You can drop a foreign key constraint with the following syntax: 
    ALTER TABLE little DROP CONSTRAINT fk_little_1;
  5. You can add a foreign key constraint with the following syntax: 
    ALTER TABLE little ADD CONSTRAINT fk_little_1 FOREIGN KEY (big_id) REFERENCES big (big_id);

As always, I hope this helps you solve problems.

Источник

Понравилась статья? Поделить с друзьями:

Читайте также:

  • Forecast error variance decomposition
  • Ford ошибка p2787
  • Ford ошибка p0984
  • Ford ошибка p0123
  • Ford ошибка c1155

    • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии