So I have a Customer table that is linked to different tables. For instance, a Configuration table is linked to Customer table. Instead of using Customer Id as foreign key to the Configuration table, I would like to introduce a secondary id called Reference. This Reference has a value similar to GUID and would like to use this as Foreign Key to the Configuration table. The reason behind this is because I don't want to use the Id of the Customer table. We use integer as a data type for this and I don't want to expose this. What could be the possible complications on implementing this approach?

I have two tables which are linked by a foreign key.

CREATE TABLE clients (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name character varying,
    phone character varying,
    contact text
);

CREATE TABLE address (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    line1 character varying,
    city character varying,
    state text,
    zip character varying,
    uuid integer REFERENCES clients(id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
);

My lambda server receives data which should be put into both tables. However the queries I am trying to run are inserting a NULL value into the uuid foreign key column of my address table.

BEGIN; 
INSERT INTO clients (name, phone, contact) 
VALUES ('Trevor J','6077730',''); 

INSERT INTO address (line1, city, state, zip) 
VALUES ('82 Haon Street,'Gasto','OH', 51113); 
COMMIT;

What am I missing to get the proper foreign key in my table?

Say I am trying to understand some unkown part of a database. I usually rely a *lot* on foreign keys to understand the relations between the data, but the tables in question don't have any for some reason. And what's even worse, the data are not consistent! There are child records lacking their parent. Can I create the foreign key on such table, but without actually enforcing it (at least temporarily)? As a comment of sorts. --- We can use following AI generated demo as an example.

CREATE TABLE Invoice (
    InvoiceID INT PRIMARY KEY,
    CustomerName NVARCHAR(100) NOT NULL,
    InvoiceDate DATE NOT NULL,
    TotalAmount DECIMAL(18, 2) NOT NULL
);

CREATE TABLE InvoiceRow (
    RowID INT PRIMARY KEY IDENTITY(1,1),
    InvoiceID INT NOT NULL,
    ProductName NVARCHAR(100) NOT NULL,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(18, 2) NOT NULL
);

INSERT INTO Invoice (InvoiceID, CustomerName, InvoiceDate, TotalAmount) VALUES
(1, 'John Doe', '2025-07-20', 150.00);

INSERT INTO InvoiceRow (InvoiceID, ProductName, Quantity, UnitPrice) VALUES
(1, 'Product A', 2, 50.00),
(1, 'Product B', 1, 50.00),
(2, 'Product C', 3, 40.00);

This command fails, because the data are not consistent.

ALTER TABLE InvoiceRow
ADD CONSTRAINT FK_InvoiceRow_Invoice
FOREIGN KEY (InvoiceID) 
REFERENCES Invoice(InvoiceID);

[Fiddle](https://dbfiddle.uk/m6Nb0ohj) .

I have a table called " Product " it has: **ID_P**, **Name**, **expiration date** Now I want to create another table called "Stock" this table has **ID**, **Quantity_of_the_product**, **product_location_in_the_stock**, **ID_P** (foreign key), and **expiration date** I want to bring the expiration date from the table product I am new to SQL oracle so please be patient with me I am using: SQL ORACLE **UPDATE:** and is it right if I do this CREATE TABLE stock (ID, Quantity_of_the_product,product_location_in_the_stock, ID_P foreign key) AS SELECT expiration_date FROM Product WHERE stock.id_p = product.id_P

Consider table A with columns id (primary key), name and table B with columns id, a_id (foreign key linked with table A id column), address. What will be the sequence of columns if the query is: SELECT * FROM B INNER JOIN A ON B.a_id = A.id;

We have a partitioned table from which we attach and detach partitions for archiving purposes. When attempting to add a foreign key on the partitioned table, the process is taking a long time (up to 20 minutes) which is causing many problems. We have discussed using a NOT VALID option, but unfortunately this is not an option for partition tables, so just wanted to see if there were any options to add a foreign key to a partition

Is it possible to auto insert a record into the primary table if the record does not exist when adding a foreign key? For example, assume these tables: - user(id, name, age) - topic(id, name) - post(userId, topicId, text, createdAt, updatedAt) Now i am pulling posts from some source and saving the records in the post table. But sometimes the data that is being returned contains a userId or a topicId that is not yet in my database. So everytime i would have to check if the user and topic records exist then save if not. Only then my post record would be valid and saved. I want to be able to save the post even if its related user or topic does not exist, and add an empty row with the in these tables having the ids that have been stored in the post table. Example: Current User Table +----+------+-----+ | id | name | age | +----+------+-----+ | 15 | Paul | 26 | +----+------+-----+ | 56 | John | 31 | +----+------+-----+ current Topic Table +----+----------+ | id | name | +----+----------+ | 5 | Business | +----+----------+ | 12 | General | +----+----------+ current Post Table: +--------+---------+----------------+-------------+-------------+ | userId | topicId | text | createdAt | updatedAt | +--------+---------+----------------+-------------+-------------+ | 15 | 12 | blah blah blah | *timestamp* | *timestamp* | +--------+---------+----------------+-------------+-------------+ | 56 | 5 | lorem ipsum... | *timestamp* | *timestamp* | +--------+---------+----------------+-------------+-------------+ So then i fetch post from some sources an get a new 1 This is a new topic posted by a user with id 72 in a topic with id 2. The source only returns the id, and to obtain the rest of the details of the user, i should make another request to their api. Post Table after: +--------+---------+---------------------+-------------+-------------+ | userId | topicId | text | createdAt | updatedAt | +--------+---------+---------------------+-------------+-------------+ | 15 | 12 | blah blah blah | *timestamp* | *timestamp* | +--------+---------+---------------------+-------------+-------------+ | 56 | 5 | lorem ipsum... | *timestamp* | *timestamp* | +--------+---------+---------------------+-------------+-------------+ | 72 | 2 | This is a new topic | *timestamp* | *timestamp* | +--------+---------+---------------------+-------------+-------------+ User Table After: +----+------+-----+ | id | name | age | +----+------+-----+ | 15 | Paul | 26 | +----+------+-----+ | 56 | John | 31 | +----+------+-----+ | 72 | | | +----+------+-----+ Topic Table after +----+------------+ | id | name | +----+------------+ | 2 | | +----+------------+ | 5 | Business | +----+------------+ | 12 | General | +----+------------+ So now that i have this, i can make my request to their api and look for data for user with id 72 and data for topic with id 2.

I am using PostgreSQL with two tables, called records and flags, that are partitioned exactly the same -- by range. I want to truncate old partitions in both. We use pg_partman, and they are created as so (toy example):

CREATE TABLE logging.records (
    id BIGSERIAL,
    record VARCHAR,
    PRIMARY KEY (id)
) PARTITION BY RANGE (id);
CREATE TABLE logging.records_template (LIKE logging.records);
ALTER TABLE logging.records_template ADD PRIMARY KEY (id);
SELECT logging.create_parent(
    p_parent_table := 'logging.records'
    , p_control := 'id'
    , p_interval := '10'
    , p_premake := '20'
    , p_default_table := false
    , p_template_table := 'logging.records_template');


CREATE TABLE logging.flags(
    id BIGSERIAL PRIMARY KEY,
    record_id BIGSERIAL,
    archived BOOLEAN,
    FOREIGN KEY (record_id) REFERENCES logging.records(id) ON DELETE CASCADE
) PARTITION BY RANGE (id);
SELECT logging.create_parent(
    p_parent_table := 'logging.flags'
    , p_control := 'id'
    , p_interval := '10'
    , p_premake := '20'
    , p_default_table := false);

If I:

TRUNCATE logging.records_p0;

I get the error: > ERROR: cannot truncate a table referenced by a foreign key constraint If I use CASCADE, then it truncates all partitioned flags, which I do not want. If I:

TRUNCATE logging.flags_p0;

it works. But of course that leaves logging.records full of stuff. If I:

ALTER TABLE logging.records DETACH PARTITION logging.records_p0 CONCURRENTLY;

I get the error: > ERROR: removing partition "records_p0" violates foreign key constraint "flags_record_id_fkey2" If I:

ALTER TABLE logging.flags DETACH PARTITION logging.flags_p0 CONCURRENTLY;
ALTER TABLE logging.records DETACH PARTITION logging.records_p0 CONCURRENTLY;
TRUNCATE logging.flags_p0;
TRUNCATE logging.records_p0;

it works. However, at this point I may as well just drop the tables instead of truncating them since they won't be used again. But I have the deadlock issue with detaching, because while this is all going on, there is heavy activity with other partitions getting inserts and updates. In my ideal world I could just truncate with cascade and it would just wipe out the partition in records and the corresponding one in flags and not have to detach. Is there another way? Thank you

I am refactoring one of my database tables and I've come up with an issue. Specifically, I have a table with four columns. We'll call the first three my.a, my.b and my.c. I have a uniqueness constraint on the combination of these three columns. Column my.d is a foreign key to an auto-incremented ID column in another table foreign. I realized that this other table *also* has a column foreign.a and both column as are complete duplicates, i.e., if you join the tables together on d, my.a will always equal foreign.a. Apparently this is part of the requirements. I would like to remove column my.a from my table, but that will remove the uniqueness constraint. Is there a way to somehow set up a uniqueness constraint on the combination of foreign.a, my.b, my.c? Or am I better off just leaving the duplicate column in place?

I have the following table definition: CREATE TABLE async_task ( idasync_task bigint(20) unsigned NOT NULL AUTO_INCREMENT, idasync_type int(10) unsigned NOT NULL, priority tinyint(3) NOT NULL, status enum('todo','doing','failed') NOT NULL DEFAULT 'todo', iduser int(11) NOT NULL, date_added datetime NOT NULL, PRIMARY KEY (idasync_task), KEY priority_id (priority,idasync_task), KEY status_type (status,idasync_type) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; I've added a column to the my notification table which I want to point to async_task: ALTER TABLE notification ADD COLUMN async_task_id BIGINT(20) And when I add the following foreign key: ALTER TABLE notification ADD CONSTRAINT fk_notification_async_task FOREIGN KEY (async_task_id) REFERENCES async_task(idasync_task); I get: ERROR 1005 (HY000): Can't create table my_database.#sql-182_2d (errno: 150 "Foreign key constraint is incorrectly formed") I've looked elsewhere but only find the errors as being: 1. The table you're referencing is not created (not the case) 2. The table you're referencing is not InnoDB (not the case, both notification and async_task are InnoDB) 3. You're not referencing the entire primary key (not the case, the only primary key is the ID column). What else could it be?

As a scheduled cleanup action, I'm looking to delete orphaned rows in a certain table that can be referenced to by various other tables. Now I could go with a difficult-to-implement reference counting system which makes tons of other cascaded deletes much harder, or I could check ALL the possible tables for references pointing at the data. But I can also just simply occasionally try to delete the rows and skip the ones where this fails because of a foreign key constraint violation. Is there anything wrong with this practice? I'm not too worried about performance, if that becomes an issue I can just add a date column for when it was last attempted. Ideally I'd avoid defining functions for it, but some pseudo-code (I know nothing of postgreSQL functions so bear with me for any syntax errors) I'd imagine could quite easily do it: ``` CREATE OR REPLACE FUNCTION try_delete_or_update(p_id INT) RETURNS VOID AS $$ BEGIN DELETE FROM public.file WHERE last_delete_attempt < 1 month ago EXCEPTION WHEN foreign_key_violation THEN UPDATE public.file SET last_delete_attempt = now() WHERE last_delete_attempt < 1 month ago; END; $$ LANGUAGE plpgsql;

lets say I have a users,posts and categories tables .. - users table would have statuses like ( active, expired .. etc ) - posts table would have statuses like ( published, draft, archived .. etc ) - categories would also have different statuses Is it bad if I did my tables like this: users table: - id - name - email - password - status_id posts table: - id - title - desc - status_id categories table: - id - title - status_id status table: - id - name ( active, expired , published, draft, archived .. etc )

I have two csv files, countries and birth_rate_stat with the following data: | ISO | ISO3 | ISO_CODE | FIPS | Display Name | Currency Name | Phone_Code | Region Code | |---------------|--------------|---------------|-------------|------------------|--------------------|------------------|------------------| | AF | AFG | 4 | AF | Afghanistan | Afghani | 93 | Asia | | AX | ALA | 248 | | Aland Islands | Euro | 340 | Europe | | country_code | country_name | year | birth_rate | |-------------------|-----------------|------------|-----------------| | SI | Slovenia | 2036 | 7.59 | | SI | Slovenia | 2022 | 7.52 | and i created two tables CREATE TABLE countries ( iso varchar(2) NOT NULL, iso3 varchar(3) DEFAULT NULL, iso_code int(11) NOT NULL DEFAULT 0, fips varchar(2) DEFAULT UNIQUE, display_name varchar(255) DEFAULT NULL, currency_name varchar(255) DEFAULT NULL, phone_code int(11) DEFAULT NULL, region_code int(11) DEFAULT NULL, PRIMARY KEY (iso) )ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE birth_rate_stat ( iso varchar(2) NOT NULL, year int(11) NOT NULL, crude_birth_rate float NOT NULL, PRIMARY KEY (iso,year), CONSTRAINT crude_birth_rate_ibfk_1 FOREIGN KEY (iso) REFERENCES countries (iso) )ENGINE=InnoDB DEFAULT CHARSET=latin1; The problem that i face while trying to connect the birth_rate_stat table with the countries one is that, in the birth_rate_stat table, the column country_code contains entries from both the ISO and the FIPS columns from the table countries. That means I can't set iso as a primary key nor fips as fips contains null values. I tried to create a country_id column which would distinguish the countries but then i get an error Cannot add or update a child row: a foreign key constraint fails which makes sense since it doesn't exist in birth_rate_stat. Do you have some suggestions as to how i could connect the two tables?

So the situation is as follow: I have **production DB** called **DB1** which has huge data, around 250 GB. I have **Test DB** called **DB2** on my local machine which has the same schema as the production DB but only the first two tables are partially filled(**UUT_RESULT, STEP_RESULT**). What I want: Copy the only related data from the Production DB tables (**PROP_RESULT**) to my local DB table. Very important note: I can copy the entire table, However, it's huge. That's why I only want to copy the data from **PROP_RESULT** table in the production DB which has relation to the already existed data in my local DB. Note: Only UUT_RESULT and STEP_RESULT are partially filled in the Local DB, other tables are empty and I need to populate them and that's why I am posting this. The following image will make is easier to understand

I do understand why replicated articles have not trusted FK constraints, but wanted to know if I force FK for trusted anyway, will query plan actually change?

I had a specific relational database with different tables. what I wanted to do is actually to copy two tables from different DB located on different server to my database using export feature in Microsoft SQL Server Management Studio 2017. The new tables have been imported to my current database. However, the problem is that the tables that have been exported from the other database into mine don't have primary and foreign keys. So what I did, I deleted the old tables in my current database and now working on adding the primary and foreign keys to the freshly copied one. The issue is that I was able to create the primary keys without any problems however, when I tried to create the foreign key to the second table to link it to the first table I Got this error: > The ALTER TABLE statement conflicted with the FOREIGN KEY constrain I know the reason of the error that I have unmatched number of items in my first and second tables. I ran this query: select UUT_RESULT from STEP_RESULT WHERE UUT_RESULT NOT IN (SELECT ID from UUT_RESULT) I was able to see that there is 883 entries in my second table doesn't have match in the first table. How can I solve this problem? I can't clean the tables because they have 15 million entries so is there a way that I can delete these 883 from my second table and then I can match the two tables?

I have spent a couple hours digging into documentation, online blogs, and the particular permission settings for this DB. My best lead is https://stackoverflow.com/a/28503453/5419599 (more on that later). First, the error message from the Postgres error log, lightly redacted: > my_app_user@my_db::ERROR: permission denied for schema organization at character 417 \dn organization shows, among other things, that the owner is postgres and the permission string my_app_user=U/postgres. The query being run (which does appear in the log) is extremely complicated because it's generated under the hood by some typescript library and has a lot of __local_0__ and __local_3__ aliases, etc. And a lot of calls to to_json and json_build_object and json_build_object. But fundamentally it's just a SELECT query. Here is a simplified version to ignore some of the complexity but keep in the references that may be relevant:

select
  ... ,
  to_json(
    select
      json_build_object(
        ...
      ) as object
    from "organization"."organization" as __local_1__
    where ...
  ) as "@redactedAlias1",
  to_json(
    with __local_2__ as (
      select ...
      from "redacted_schema_name"."custom_function_2"(__local_0__) as __local_3__
      where (TRUE) and (TRUE)
    ), ...
    select
      ...    
  ) as "@redactedAlias2",
  to_json(
    with __local_5__ as (
      select ...
      from "redacted_schema_name"."custom_function_3"(__local_0__) as __local_6__
      where ...
    ), ...
    select ...
  ) as "@redactedAlias3"
from "redacted_schema_name"."custom_function_1"() as __local_0__
where (not (__local_0__ is null)) and (TRUE) and (TRUE)

(Character 417 seems to align with where "organization"."organization" appears.) The custom functions are all SQL functions with "invoker" security (the default). The only possibility I currently know of for where this error might be coming from, is the Stack Overflow answer I linked, which states: > There is a foreign key in a table referring to a table in the schema in question, to which the table owner role does not have access granted. Foreign key checks are done with the permissions of the role that owns the table, not the role performing the query. > > The query is actually doing the internal foreign key check. The organization.organization table in my case does have an awful lot of foreign keys which reference it. However, the table and the schema are both owned by the postgres user, and the postgres user also owns all the other schemas and tables which have references to organization.organization. (Note, the postgres user is NOT the DB superuser but for most purposes might as well be.) I was trying to dig up documentation to confirm the above linked answer, i.e. to confirm what permissions are used when checking foreign key constraints and when this check is done, and I was unable to find any. What could be causing this schema permission error? And, if foreign key constraints could indeed be relevant here, where is the documentation about how they are checked or how table owner permissions relate to foreign key constraint checking?

In a MySQL database, I have a table full of business information. Lots of businesses have been inserted twice due to human error, so the same information exists with two different primary keys. Let's say the same delivery service is represented by primary key 3 and 7. Businesses: | id | name | | 3 | Planet Express | | 7 | Planet Express | Lots of other tables foreign key to this business table. (Employees, etc) Employees: | id | name | business | | 999 | Fry | 3 | | 666 | Bender | 7 | Fry and Bender really do work for the same business. I'd like to delete the business row with primary key 7, and tell **all** the foreign key tables that instead of cascade delete, or cascade null, they should update to point to primary key 3. Obviously I could do this by hand, but I'd rather make the database do it on my behalf since it has a better memory for this kind of thing. Is there a way to do this? Like DELETE FROM business WHERE id = 7 ON CASCADE set 3; or something?

I have a table **Category** with a **parent_id** column referencing the self **id** column, for this, I used "ON DELETE CASCADE" meaning if the parent is deleted, other children would be deleted too. At the same table I have a boolean column **active**, is it possible to use **ON UPDATE CASCADE** in order to make the update of this column impact recursively all the children of the updated row? let's take this example **parent > sub1 > sub2**, what is the way (at the database level) to make the update of the column **active** impact all the children? - update the **parent** will impact himself and **sub1** and **sub2** - update the **sub1** will impact himself and the **sub2**. and so on. Thanks in advance.

I have 2 tables: users - user_id (int ai pk) - premium premium_subscription - subscription_id (int ai pk) - user_id (int) -> fk to user.user_id - expiration (datetime) I would like to implement a boolean value based on the fact if there's a subscription and its expiration date is ahead of the time I made the query. I made this if statement, that works in a SELECT query. However, I'm not able to implement it as the value of a table. IF(timestampdiff(SECOND, CURRENT_TIMESTAMP, (SELECT expiration FROM premium_subscription WHERE user_id = *)) > 0, 1, 0); Is there any way to apply this expression to this column? Thanks in regards! ^^