sql server – Best approach for extending a foreign relationship for an existing table

I’m not sure the title accurately reflects my question. I have an existing Rate table that has an identity column key and contains a [Rate] column. col1 + col2 + col3 don’t uniquely identify a row:

+--------+------+------+------+------+
| RateId | col1 | col2 | col3 | Rate |
+--------+------+------+------+------+

I have a new table that needs to match a rate. The new table has 3 of the required columns to make a match but those columns will return a number of rows in the Rate table. To uniquely identify a rate for the new table I need to match on MaterialTypeId and UnitTypeId where UnitTypeId can be null.

+------------+------+------+------+----------------+------------+
| MaterialId | col1 | col2 | col3 | MaterialTypeId | UnitTypeId |
+------------+------+------+------+----------------+------------+

What is the best approach to resolving this? I could add the two additional columns to the Rate table but that would not be relevant for the current uses of the Rate table, i.e. the two additional columns would be null for all existing rows.

I could introduce an intermediate table and join on MaterialTypeId & UnitTypeID which would return multiple rows and then join using the existing columns to uniquely identify the rate:

+--------+----------------+------------+
| RateId | MaterialTypeId | UnitTypeId |
+--------+----------------+------------+

Is that the correct approach?

sql server – Foreign keys to primary tables or nested table

In this hypothetical example should the foreign key constraint setup for the ProductId and UserId columns in the ProductUserCommentAction table be referencing the Product/User tables as shown in the first diagram OR is it OK for those columns to reference the ProductUserComment table as shown in the second diagram?

I like how it’s setup in the second diagram as it reduces the spider web in visualizations.

Are there any downsides to this second approach?

enter image description here

Versus

enter image description here

sql server – SQL Azure Indexes need Rebuilding for Foreign Keys to work

We have a product that runs off SQL databases.

Until recently, each of our clients had their own fileserver running SQL Server, however we have now started hosting on Azure for some existing and some new clients.

Attempting to roll out the latest version of the software to our clients, we’ve run into an issue with some (but not all!) of the Azure-hosted databases.

The update required adding some new tables, which had Foreign Keys to some existing tables. This worked fine on all the SQL Server hosted databases, but on some of the Azure hosted databases we got the following error:-

There are no primary or candidate keys in the referenced table ‘TableName’ that match the referencing column list in the foreign key ‘FK_Name’

But there were definitely valid PKs, all the databases had the same schema, and only some had issues. Those that had issues also reported the issue against different tables.

The solution turned out to be that the indexes needed rebuilding on some of the tables:-

ALTER INDEX ALL ON TableName REBUILD;

In some cases, more than one index needed to be rebuild.

Once that was done, the scripts to create the new tables ran without issue.

All of the Azure databases are quite new, and most of them barely have any data in them. Half a dozen records in the tables affected, in some cases, so not a case of indexes getting too big, or too fragmented.

The tables themselves worked fine, before and after the indexes were rebuilt, and the software didn’t throw any errors.

The only obvious issue related to this is being unable to reference the PKs with FKs.

However, we’ve obviously concerned that there is some underlying issue with Azure and indexes, and that we could face worse problems down the line.

Has anyone else experienced this issue?

Is there something we need to be doing on Azure to stop indexes breaking?

I ran overnight jobs on all the Azure databases to rebuild all indexes, so hopefully we’re now good, but I’d still like to know what happened, and why, and how to stop it happening again.

TL:DR;

Different indexes, on different tables, on different Azure databases, have broken somehow in the last few weeks (newest DB) to months (oldest DB), and we’d kinda like to know why!

sqlite – How to query/join tables where foreign key constraint is set in schema

I’m relatively new to sqlite and databases in general.
I’ve successfully setup a schema of 4 tables, each having a foreign key pointing to its parent table. The schema also has the foreign key constraint set on the child keys.

Performing a query where every table is joined with the SELECT * FROM…JOIN…ON ……… works without issue, but it is laborious manually connecting the keys.

My question is: Is it possible to perform a query/join of the tables without the ON statement which links each parent/child key relationship? Since this was already established in the schema definition, this seems to be redundant. I’m trying to simplify the sql command and make use of the parent/child relationship already defined. When I perform said query but omit the ON statements, the results are not what I expected. Any guidance is appreciated.

Has anyone satisfied the UK fiancee/spouse visa financial requirements by working their foreign job remotely as someone self-employed in the UK?

I’m a UK citizen with a Japanese fiancee. We plan to get married in the UK, so we want to apply for his entry under the fiancee visa. However, we don’t meet the financial requirements yet — I’m searching for, but haven’t yet secured, a UK-based job. That said, I have a Japanese job that meets the income requirement and would be willing for me to work remotely.

I spoke to a Japanese visa firm who suggested it would be possible to count J-income if

  1. I paid tax on the income in the UK.
  2. I became self-employed in the UK and submitted that documentation.

However, it seems like the forms for the self-employed look to a past year of s.e. income, which I don’t have.

Tldr; Has anyone satisfied the UK fiancee/spouse visa financial requirements by working their foreign job remotely as someone self-employed in the UK?

postgresql – Feasible to have thousands of foreign data wrappers in Postgres?

We are creating enterprise-grade SaaS where we will have many 1,000s of customers. We are considering creating a database in the Postgres server for every customer to ensure enterprise-grade security/access.

Each of these customer databases will have some access to a few tables in a root database via foreign data wrappers using the postgres_fdw extension. Also highly probable that we’ll have a few triggers on the local tables that are referencing the foreign tables in the root db (I’m not sure how the root database syncs with the databases which could be a scaling issue).

Will we find any unexpected issues if we have 1,000s of databases using postgres_fdw to connect to a root database in the same postgres instance?

database design – Putting foreign keys of several tables into the same column

I’ve never come across this situation, not sure how to go about it.

Let’s say I have a table Portfolio. I also have two tables: Asset 1, and Asset 2. I need to create a bridge between Portfolio and the Asset tables. A portfolio can have either Asset 1, or Asset 2, or both, or none.

enter image description here

Can I do something like the bridge table in the image above, i.e. where the bridge table (example below) would contain in the same column, the foreign keys to two different tables

enter image description here

Sale – I sell accounts on foreign pornographic forums. | NewProxyLists

There are a lot of scammers on the Internet, so I recommend that you only complete all transactions through a warranty service you trust.
Hello.
I sell accounts on foreign porn forums.
All of my accounts have a mega thread by hardcore porn genre.
I can still prove my account and send you a personal message from my account to your registered account.
Reason for the sale: transition to a more profitable type of activity for me, namely sales passports (scans).
The price is 36 dollars each.


I accept Bitcoin, Capitalist, Advcash payment.
Write on all my contacts:
Telegram https://t.me/mickey1907
Skype mickey7091
Icq 628882622
E-mail (protected by e-mail)

magento2 – cataloginventory_stock_item – errno: 150 Foreign key constrain is incorrectly formed

I’m tranfering my magento2 website locally.

Importing website database gives me the following error:

ERROR 1005 (HY000) at line 636351: Can’t create table ‘cataloginventory_stock_item’ (errno: 150 “Foreign key constrain is incorrectly formed”)

This is the sql part that causes this:

DROP TABLE IF EXISTS `cataloginventory_stock_item`;
CREATE TABLE `cataloginventory_stock_item` (
  `item_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Item Id',
  `product_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Product Id',
  `stock_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Stock Id',
  `qty` decimal(12,4) DEFAULT NULL COMMENT 'Qty',
  `min_qty` decimal(12,4) NOT NULL DEFAULT 0.0000 COMMENT 'Min Qty',
  `use_config_min_qty` smallint(5) unsigned NOT NULL DEFAULT 1 COMMENT 'Use Config Min Qty',
  `is_qty_decimal` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Is Qty Decimal',
  `backorders` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Backorders',
  `use_config_backorders` smallint(5) unsigned NOT NULL DEFAULT 1 COMMENT 'Use Config Backorders',
  `min_sale_qty` decimal(12,4) NOT NULL DEFAULT 1.0000 COMMENT 'Min Sale Qty',
  `use_config_min_sale_qty` smallint(5) unsigned NOT NULL DEFAULT 1 COMMENT 'Use Config Min Sale Qty',
  `max_sale_qty` decimal(12,4) NOT NULL DEFAULT 0.0000 COMMENT 'Max Sale Qty',
  `use_config_max_sale_qty` smallint(5) unsigned NOT NULL DEFAULT 1 COMMENT 'Use Config Max Sale Qty',
  `is_in_stock` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Is In Stock',
  `low_stock_date` timestamp NULL DEFAULT NULL COMMENT 'Low Stock Date',
  `notify_stock_qty` decimal(12,4) DEFAULT NULL COMMENT 'Notify Stock Qty',
  `use_config_notify_stock_qty` smallint(5) unsigned NOT NULL DEFAULT 1 COMMENT 'Use Config Notify Stock Qty',
  `manage_stock` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Manage Stock',
  `use_config_manage_stock` smallint(5) unsigned NOT NULL DEFAULT 1 COMMENT 'Use Config Manage Stock',
  `stock_status_changed_auto` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Stock Status Changed Automatically',
  `use_config_qty_increments` smallint(5) unsigned NOT NULL DEFAULT 1 COMMENT 'Use Config Qty Increments',
  `qty_increments` decimal(12,4) NOT NULL DEFAULT 0.0000 COMMENT 'Qty Increments',
  `use_config_enable_qty_inc` smallint(5) unsigned NOT NULL DEFAULT 1 COMMENT 'Use Config Enable Qty Increments',
  `enable_qty_increments` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Enable Qty Increments',
  `is_decimal_divided` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Is Divided into Multiple Boxes for Shipping',
  `website_id` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT 'Website ID',
  PRIMARY KEY (`item_id`),
  UNIQUE KEY `CATALOGINVENTORY_STOCK_ITEM_PRODUCT_ID_STOCK_ID` (`product_id`,`stock_id`),
  KEY `CATALOGINVENTORY_STOCK_ITEM_WEBSITE_ID` (`website_id`),
  KEY `CATALOGINVENTORY_STOCK_ITEM_STOCK_ID` (`stock_id`),
  CONSTRAINT `CATINV_STOCK_ITEM_PRD_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE,
  CONSTRAINT `CATINV_STOCK_ITEM_STOCK_ID_CATINV_STOCK_STOCK_ID` FOREIGN KEY (`stock_id`) REFERENCES `cataloginventory_stock` (`stock_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Cataloginventory Stock Item';

How can I fix this?

datagrip – How to copy multiple tables with foreign keys from one MySQL database to another, in PhpStorm?

How to copy multiple tables with foreign keys from one MySQL database to another, in PhpStorm?

You should copy not all the tables from the original database, but only a part. All these tables have foreign keys. In the second database, some tables also already exist.