Ошибка 1824 sql

When I try to create the ‘Project table I get this error Code: 1824. Failed to open the referenced table ‘Employee’.

My syntax:

CREATE DATABASE IF NOT EXISTS Test;
USE Test;

CREATE TABLE IF NOT EXISTS Customer (
CustomerID VARCHAR(7) NOT NULL,
CustomerName VARCHAR(50),
CustAdress VARCHAR(70),
CustEmail VARCHAR(50),
PRIMARY KEY (CustomerID)
);

CREATE TABLE IF NOT EXISTS Employee (
EmpID VARCHAR(7) NOT NULL,
EmpName VARCHAR(50),
Position VARCHAR(30),
EmpTimePrice INT(4),
PRIMARY KEY (EmpID)
);

CREATE TABLE IF NOT EXISTS Project (
ProjectNo VARCHAR(7),
ProjectName VARCHAR(50),
StartDate DATE,
ProjTimePrice INT(6),
CustomerID VARCHAR(7),
EmpID VARCHAR(7),
PRIMARY KEY (ProjectNo),
FOREIGN KEY (EmpID) REFERENCES Employee (EmpID),
FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID) 
);

CREATE TABLE IF NOT EXISTS ProjectWork (
ProjectNo VARCHAR(7),
EmpID VARCHAR(7),
PWDATE DATE,
HoursWorked INT(5),
FOREIGN KEY (ProjectNo) REFERENCES Project (ProjectNo),
FOREIGN KEY (EmpID) REFERENCES Employee (EmpID)
);

The names look correct to me and I have referenced the foreign key so I don’t understand why I get this error. Any help would be appreciated, thanks.

Источник

When I try to create the ‘Project table I get this error Code: 1824. Failed to open the referenced table ‘Employee’.

My syntax:

CREATE DATABASE IF NOT EXISTS Test;
USE Test;

CREATE TABLE IF NOT EXISTS Customer (
CustomerID VARCHAR(7) NOT NULL,
CustomerName VARCHAR(50),
CustAdress VARCHAR(70),
CustEmail VARCHAR(50),
PRIMARY KEY (CustomerID)
);

CREATE TABLE IF NOT EXISTS Employee (
EmpID VARCHAR(7) NOT NULL,
EmpName VARCHAR(50),
Position VARCHAR(30),
EmpTimePrice INT(4),
PRIMARY KEY (EmpID)
);

CREATE TABLE IF NOT EXISTS Project (
ProjectNo VARCHAR(7),
ProjectName VARCHAR(50),
StartDate DATE,
ProjTimePrice INT(6),
CustomerID VARCHAR(7),
EmpID VARCHAR(7),
PRIMARY KEY (ProjectNo),
FOREIGN KEY (EmpID) REFERENCES Employee (EmpID),
FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID) 
);

CREATE TABLE IF NOT EXISTS ProjectWork (
ProjectNo VARCHAR(7),
EmpID VARCHAR(7),
PWDATE DATE,
HoursWorked INT(5),
FOREIGN KEY (ProjectNo) REFERENCES Project (ProjectNo),
FOREIGN KEY (EmpID) REFERENCES Employee (EmpID)
);

The names look correct to me and I have referenced the foreign key so I don’t understand why I get this error. Any help would be appreciated, thanks.

Источник

Issue

I am building a database for a school project, but for some reason I cannon make a foreign key reference between 2 tables (only those 2). My project has 14 tables and it works fine for all the others.

The tables are made like:

create table degree(
    title varchar(50),
    idryma varchar(40),
    bathmida enum('High School', 'Univercity', 'Master', 'PHD'),
    constraint degree_id primary key (title, idryma)
);

create table has_degree(
    degree_title varchar(50),
    degree_idryma varchar(40),
    employee_username varchar(12),
    acquisition_year year(4),
    grade float(3,1),

    constraint has_degree_id primary key (degree_title, degree_idryma, employee_username)
);

And then I try to alter the table so that I make the foreign key connections:

alter table has_degree add foreign key (degree_title) references degree(title);
alter table has_degree add foreign key (degree_idryma) references degree(idryma);

But I keep on getting

Error Code: 1824. Failed to open the referenced table ‘degree’

I have tried to make them like that:

create table degree(
    title varchar(50),
    idryma varchar(40),
    bathmida enum('High School', 'Univercity', 'Master', 'PHD'),
    constraint degree_id primary key (title, idryma)
);

create table has_degree(
    degree_title varchar(50),
    degree_idryma varchar(40),
    employee_username varchar(12),
    acquisition_year year(4),
    grade float(3,1),

    foreign key (degree_title) references degree(title),
    foreign key (degree_idryma) references degree(idryma),
    
    /*employee is an other table that I use and that works just fine*/

    foreign key (employee_username) references employee(employee_username),
    constraint has_degree_id primary key (degree_title, degree_idryma, employee_username)
);

But the only thing that changes is that I get

Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint ‘has_degree_ibfk_2’ in the referenced table ‘degree’

Solution

The columns in your foreign key in table has_degree must be the same as the columns in the primary key of the referenced table degree.

In this case, the primary key of degree consists of two varchar columns.

So the foreign key in has_degree that references it must also be only two varchar columns, and values in those columns in has_degree must match exactly the values in a row of degree.

You defined the foreign key this way:

foreign key (degree_title) references degree(title),
foreign key (degree_idryma) references degree(idryma),

But that’s two foreign keys, each having a single column. You need one foreign key with two columns:

foreign key (degree_title, degree_idryma) references degree(title, idryma),

Answered By – Bill Karwin

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Источник

Table of Contents

SQL Server Error: 1824
Severity: 16
Event Logged or not: No
Description:
Cannot attach a database that was being restored.
Severity 16 Description:
Indicates general errors that can be corrected by the user.

Reading sql server error log location from SQL Query

Identifying SQL Server Error Log File used by SQL Server Database Engine can be done by reading SQL Server Error Logs. DBA can execute the XP_READERRORLOG extended stored procedure to read the SQL Server Error Log and search for its location used by the instance of SQL Server.

USE master Go xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc' Go

The parameters for XP_READERRRORLOG are:
1. Value of error log file we would like to read. values are 0 = current, 1 = last one before current, 2 = second last before current etc…
2. Log file type:- 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1:- String one you want to search for
4. Search string 2:- String two you want to search for to further refine the results
5. start time for Search
6. end time for search
7. Sort order for search results:- N’asc’ = ascending, N’desc’ = descending

By default, we have 6 Server Error Logs kept but we can increase the number of SQL Server Error Logs from the default value of six.

For other ways to read and find error log location please our artcile https://sqlserver-dba.co.uk/error-log/sql-server-identify-location-of-the-sql-server-error-log-file.html

Solution for Resolving the Error

Alternate Solutions

Restarting SQL Server Service(non production instances only)

To Restart, Start or Stop the SQL Server instance by right click on sql server instance in SSMS or in SQL. You may need to open SSMS as administrator to start, stop the instance.

Other ways for restarting SQL server Service

From SQL Configuration manager from Start menu
From Services in Windows server
From Cmd using net start and net stop

2.Checking SQL Performance metrics like CPU, Memory

Check SQL Server CPU, Memory usage, longest running queries, deadlocks etc.. using activity monitor or sp_who2.

To view Activity Monitor in SQL Server 2005 and in SQL Server 2008, a user must have VIEW SERVER STATE permission.

2 Different Ways to Open up Activity Monitor in SQL Server 2008 are mentioned below:

Open up Activity Monitor Using Object Explorer

In Object Explorer, right click the SQL Server 2008 Instance and click on Activity Monitor.

Also can be opened from SQL Server 2008 Management Studio’s toolbar, by clicking Activity Monitor

SSMS Activity Monitor by Method2

It shows the graphical display of Processor Time (%), Number of Waiting Tasks, Database I/O (MB/Sec) and the Number of Batch Requests/second.

For information on SQL Server Activity monitor go to https://sqlserver-dba.co.uk/sql-server-administration-basics/activity-monitor

Or using SQL Query analyzer window to run sp_who2 command which is less resource intensive and gives same information as activity monitor.

2.Checking Windows Performance metrics like CPU, Memory, Disk Space etc.

Open task manager to check CPU, Memory usage etc.
Open file explorer to check Disk space on each drive.

SQL Server Error Code and solution summary

SQL Server Error: 1824
Severity: 16
Event Logged or not: No
Description:
Cannot attach a database that was being restored.

Источник

#mysql #laravel #migration #laravel-8

Вопрос:

Я создал три таблицы миграции таблица user_container предназначена для хранения сведений о пользователе, таблица admin_table предназначена для хранения сведений об администраторе, таблица blog_table предназначена для хранения блогов .администратор может создавать блоги, поэтому я создаю отношение ключа foriegn для администратора к таблице блогов .когда я пытаюсь перенести таблицы, я получаю следующую ошибку

   IlluminateDatabaseQueryException 

  SQLSTATE[HY000]: General error: 1824 Failed to open the referenced table 'admin_table' (SQL: alter table `blogs_table` add constraint `blogs_table_admin_id_foreign` foreign key (`admin_id`) references `admin_table` (`id`))

пожалуйста, помогите мне решить эту проблему, я не понимаю, где я ошибся..

структура таблицы миграции

2021_08_11_170129_create_Blogs_table.php

 <?php

use IlluminateDatabaseMigrationsMigration;
use IlluminateDatabaseSchemaBlueprint;

class CreateBlogsTable extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('blogs_table', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('price');
            $table->string('country');
            $table->longText('description');
            $table->integer('rating');
            $table->longText('image');
            $table->unsignedInteger('admin_id');
            $table->foreign('admin_id')->references('id')->on('admin_table');
            $table->timestamps();
            //$table->softDeletes();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('blogs_table');
    }
}

2021_08_12_121933_create_admin_table.php

 <?php

use IlluminateDatabaseMigrationsMigration;
use IlluminateDatabaseSchemaBlueprint;

class CreateAdminTable extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('admin_table', function (Blueprint $table) {
            $table->id();
            $table->string('firstName');
            $table->string('lastName');
            $table->string('email')->unique();
            $table->string('mobile');
            $table->string('password');
            $table->timestamps();
            //$table->softDeletes();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('admin_table');
    }
}

1. Ошибка довольно очевидна… Как вы можете ссылаться на другую таблицу, которая не была создана ? Сначала вы создаете blogs_table , а затем создаете admin_table , поэтому при создании blogs_table и запуске миграции она не будет существовать admin_table для ссылки…

Ответ №1:

Сначала вы пытаетесь запустить миграцию в таблице блогов, а затем запустите миграцию в таблице администратора.

Миграция Laravel использует метку времени в начале файла, чтобы решить, какая миграция должна быть перенесена первой в последовательности.

Убедитесь, что вы сначала создали таблицу администратора перед таблицей блога (это также относится ко всем таблицам, на которые есть ссылки). Или просто переименуйте файл (измените метку времени), например:

 2021_08_12_121933_create_admin_table.php
2021_08_11_170129_create_Blogs_table.php

К этому:

 2021_08_11_121933_create_admin_table.php
2021_08_12_170129_create_Blogs_table.php

Затем запустите php artisan migrate:fresh , чтобы обновить миграцию.

Ответ №2:

Когда вы настраиваете внешний ключ $table->foreign('admin_id')->references('id')->on('admin_table'); , таблица admin_table еще не существует.

Измените имя миграции таблицы admin_table, которая будет запущена перед именем страницы блога.

2021_08_11_121933_create_admin_table.php

вместо

2021_08_12_121933_create_admin_table.php

Источник

Issue

Solution

Reading sql server error log location from SQL Query

Solution for Resolving the Error

Alternate Solutions

Restarting SQL Server Service(non production instances only)

Other ways for restarting SQL server Service

2.Checking SQL Performance metrics like CPU, Memory

2.Checking Windows Performance metrics like CPU, Memory, Disk Space etc.

SQL Server Error Code and solution summary

Вопрос:

Комментарии:

Ответ №1:

Комментарии:

Ответ №2:

Issue

Solution

Reading sql server error log location from SQL Query

Solution for Resolving the Error

Alternate Solutions

Restarting SQL Server Service(non production instances only)

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" loading="lazy" src="https://sqlserver-dba.co.uk/wp-content/uploads/2016/09/DB-mail4.png" alt="db-mail4" width="243" height="205" />

Other ways for restarting SQL server Service

2.Checking SQL Performance metrics like CPU, Memory

2.Checking Windows Performance metrics like CPU, Memory, Disk Space etc.

SQL Server Error Code and solution summary

Вопрос:

Комментарии:

Ответ №1:

Комментарии:

Ответ №2: