Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [foreign-key]

A type of integrity constraint used in a RDBMS platform to ensure that a value in a column matches one of a range of key values from another table.

0
votes
0answers
16 views

How can i write an update trigger between two tables

i have two tables one is called defaulters and users .defaulters has mypin and debt_amount and users has mypin and debtor .i want a trigger that update debtor in users table if mypin exist in users ...
0
votes
1answer
13 views

MYSQL - Error in After Insert trigger affects the actual insert on the trigger table

I have two tables call it - table_1 and table_2 I have an After insert trigger on table_1 and in the trigger I insert on table_2 which has some fk constraints on another table_3. When the insert on ...
0
votes
1answer
31 views

Foreign keys to prevent “orphan” file rows in a database for a scenario involving projects, files and downloads

In a system that monitors a file mirror by regularly downloading a file, I want to store a history of the results. So I have three tables: ┌──────────┐ ┌──────────────────┐ ┌────────┐ │projects │ │...
0
votes
1answer
24 views

MySQL: Can not add foreign key into create table statement

The queries below (assigned to php variables) seem to have an issue on the 2nd query, where I set the foreign key to the "couponId" column on the first table. My IDE lints "couponId" (inside the ...
0
votes
1answer
23 views

Copying table subset from one database to another with same schema

I am trying to subset of a table from one table to another users(id, name, referrer_id, country_id) this table have self referential association with refferer_id as foreign key to itself. I ...
0
votes
1answer
17 views

How to change all columns from one type to another, changing all FK columns as well?

In an inherited DB we found that there are around 25 tables with a primary key defined as tinyint. For a few of those, it's an imminent problem, since this optimistic optimization clashes with the ...
0
votes
1answer
40 views

The ALTER TABLE statement conflicted with the FOREIGN KEY constrain

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 ...
0
votes
1answer
35 views

how can i write a trigger that compare between two tables in phpmyadmin mysql [closed]

I have two tables that has rows that have same values. I want to write a trigger that will check for mypin in debtors tables and if mypin exists in debtors it fills the debtor row in the users ...
1
vote
3answers
58 views

Check whether a key value is referenced in any related table

I have one table in the database having ID as the primary key. Now this ID is referenced (as Foreign Key) in many other tables. How can I create a result with each row that whether it is referenced or ...
4
votes
1answer
49 views

Standard Behaviour for mix of 'ON-DELETE-CASCADE' and 'ON-DELETE-RESTRICT' constraints in Mysql

In mysql 5.6, Consider these 2 examples creating relationships between A, B, C and D. Example 1 CREATE TABLE `a` ( id INT UNSIGNED NOT NULL, PRIMARY KEY (id) ) ENGINE = INNODB; CREATE TABLE ...
-1
votes
1answer
28 views

MySQL: Syntax error around foreign key assignment [closed]

I put the following into DB Fiddle in an attempt to debug it. The error is below Results Schema Error: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds ...
0
votes
0answers
43 views

how to dump mysql schema without keys and without constraint ? and keep all of them to alter afterwards

i am using ubuntu 16.04 and want to dump large schema with hounded of tables from AWS rds, remove all keys and foreign key , load data and then add back the keys , is there any automatic tool that ...
0
votes
2answers
29 views

Two composite foreign keys have identical columns which must be equal value

Let's say I have a relation: CourseRegistrations with composite primary key: (course_id, sem_id, student_id) I have another relation: CourseEvaluations with composite primary key: (course_id, sem_id, ...
-2
votes
1answer
16 views

How to retrieve a users uploaded files using foreign key

I have two tables,users, and,files, Users Id,firstname,lastname,email,password files Id,filename,description,userid Userid in files is foreign key witch relates to Id in users I am trying to ...
0
votes
2answers
53 views

What is the difference between a foreign key and a primary foreign key?

I understand that you use foreign keys to relate rows from one table to the other and if this foreign key also becomes a primary key of that table then it is a primary foreign key. But, for example, ...
10
votes
5answers
4k views

The use of multiple foreign keys on same column in SQL Server

SQL Server allows me to create multiple foreign keys on a column, and each time using just different name I can create another key referencing to the same object. Basically all the keys are defining ...
-3
votes
1answer
79 views

Postgres is Throwing a False Foreign Key Constraint Violation? [closed]

I am trying to delete a LocalTax record from Postgres. Currently, I don't think there are any Employee records that reference the LocalTax in question (8). => SELECT COUNT(*) FROM employees WHERE ...
0
votes
1answer
65 views

Proper database structure for storing multiple foreign keys

I am using MySQL 5.7.25 and need to store multiple foreign keys for multiple columns. Scheme of my table: CREATE TABLE `tours` ( `id` int(10) UNSIGNED NOT NULL, `countries` text NOT NULL, `...
1
vote
2answers
34 views

How to get number of PK used as FK?

How to get number of PK used as FK? Table 1 has a column ID (PK). Table 2 & 3 has a column ID (FK - refers Table 1 PK). How to get Value 1 (PK) has used in table 2 & 3 or not without ...
1
vote
2answers
68 views

Double FOREIGN KEY, i.e. how to create a table with two different FOREIGN KEYs that reference a single PRIMARY KEY in another table - DDL

I have an issue with my SQL syntax when creating a table with a set of Foreign Key (Orig_Dept_ID & New_Dept_ID) that references Dept_ID in the Department Table. Below is my sql syntax: CREATE ...
1
vote
2answers
31 views

How does ON DELETE CASCADE relate to JSON?

For relational databases, there is the notion of ON DELETE CASCADE. When working with JSON, how do these two concepts relate? Is ON DELETE CASCADE supported for JSON? Can you give an example of a ...
0
votes
1answer
37 views

make exist table as inherit

i try to create parent table for exists table, by next algorithm: -- CREATE TABLE CREATE TABLE t1 (id serial PRIMARY KEY); CREATE TABLE foreign_t1 (id serial, t1_id integer REFERENCES t1 (id)); -- ...
0
votes
1answer
18 views

SQL enforce value of a column if other column is NOT NULL

I'm having a hard time to explain this so let me illustrate it with an exemple. I have a table for comments and it looks like so: CREATE TABLE comments ( id INT UNSIGNED AUTO_INCREMENT, ...
0
votes
1answer
29 views

Cannot import table due to zombie constraint

I tried to refresh my copy of the production database by emptying and importing a SQL file. I've done it hundreds of times, only this time the import failed. I tried dropping & recreating the ...
0
votes
2answers
25 views

Finding MySQL Tables with no primary key OR no composite key

I can easily find tables that do not contain a PK from information_schema, but I'm having a tough time writing the logic to find tables that might contain neither a PK nor a Composite Keys (via two ...
0
votes
2answers
71 views

Dealing with subtypes and supertypes in Database design

I'm currently doing a database project for a class and I am having some difficulty dealing the subtypes and supertypes. This is the list of entities and their attributes: Piece (PIECE_ID, Description)...
2
votes
2answers
45 views

AdonisJS: is something wrong with belongsTo() relationship design?

In AdonisJs framework, suppose we have a Profile model in which we define belongsTo() relationship to theUser model. The documentation says this will lead us to define our databases this way: The ...
1
vote
1answer
47 views

Foreign key constraints check during replication?

I am a little bit new to replication and can't seem to find an answer to this anywhere. Let's say I have a simple relation in my database: Department table with Code column as PK and Person table ...
0
votes
0answers
19 views

How to insert into a MySQL table column, known its name as string?

I want to insert into a MySQL table (child table) column a certain value(int value). I know that the column name is: 'parent_data_ID' But that column name is got from the following command: SELECT ...
1
vote
1answer
180 views

SQLITE: Multiple foreign key referenced to multiple table cascade delete

In SQLite I would like to delete parent table row and it has to delete all the related child tables. I have gone through all the questions in StackExchange and other websites but my query no where ...
1
vote
0answers
28 views

Weird MySQL column name issue

After trying to use the Propel PHP ORM on my database, I noticed that there is an issue with foreign keys. This is a table with foreign keys: CREATE TABLE `appusers` ( `AppID` INT(11) NOT NULL, `...
0
votes
1answer
43 views

MS Access Foreign key not working in SQL Server

In MS Access I have a 'multi column - One to Many' foreign key between two table. When I try to create the same relationship in SQL Server I get a message " The columns in table 'Laser_Status_info' do ...
1
vote
1answer
36 views

2 tables with foreign keys to 3rd table with primary key

This might be a silly question. But can you have the following: 1 Table with Primary Key, 2 different tables each with a foreign key referencing the first table Primary key? I know its possible to ...
1
vote
0answers
25 views

Strategy to manage foreign key from another system/database

There is a system in the company which stores all the consultants working for the company. Our project needs to store the consultants assigned to clients. We use the id of the consultant in our ...
0
votes
3answers
17 views

MYSQL: Display NULL value when foreing key is NULL

I have the following database structure in MYSQL: CREATE TABLE xs_bugtracking ( `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `product` VARCHAR(64) NOT ...
0
votes
0answers
53 views

Occasional slow insertion speed because of FK trigger

I have two tables in master-child relationship (several children per master). In one use-case a master with all of its children needs to be copied as new master. For this I'm using two select+insert ...
0
votes
1answer
50 views

Database Design - multiple tables

I am trying to build a database that has all of our employees, their skills, and the level of their skills. The idea is that a manager could go in, type in a skill, and relevant employees are returned....
0
votes
1answer
1k views

Postgresql 10 There is no unique or exclusion constraint matching the ON CONFLICT specification

I currently have a table which looks like this: CREATE TABLE "PDPC".collection ( col_no bigint NOT NULL DEFAULT nextval('"PDPC".collection_col_no_seq'::regclass), q1 character varying(10000) ...
0
votes
0answers
16 views

Postgres - Storing Attributes in 1:0 related object, performance improvements?

In my DB's central table, "Profiles," I want to collect activity statistics that will be updated often, although not all profiles will have statistics. The "Profiles" table is already heavily updated ...
1
vote
1answer
29 views

Constraint via Foreign Key

I have the following schema in SQLite: CREATE TABLE calendars ( id INTEGER PRIMARY KEY, title TEXT NOT NULL ); CREATE TABLE entry_types ( id INTEGER PRIMARY ...
0
votes
1answer
171 views

#1215 - Cannot add foreign key constraint ERROR even though the columns are exactly the same

Last night I ran SQL commands in phpMyAdmin very similar to the following to setup a players table and a games table linked by a foreign key. It worked without errors, making google_id in my games ...
3
votes
0answers
56 views

Query for getting reserved seats in a cinema

I have this cinema project for a course. And I have to visually show the seats to user so they can reserve their preferred seats. I need to get the reserved seats from the database of that particular ...
0
votes
1answer
65 views

Auto incrementing a foreign key value the same as the primary key of the parent table in SQL when inserting data?

I have the following two connected tables as shown below in the Microsoft SQL Server. When inserting data from visual studio, the foreign key in the Reservations table (Customer_ID) should be auto ...
1
vote
1answer
75 views

Select FK constraints with ON UPDATE NO ACTION

We use Postgres 9.5. Is it possible to modify this script to show the ON UPDATE action for each foreign key reference at this table? Or get this values somehow? SELECT tc.table_schema, ...
2
votes
0answers
91 views

Is it best practice to use surrogate keys when creating foreign key constraints in SQL Server?

I'm trying my best not to ask a leading question, but after a lot of searching I'm unable to find a similar answer or recommendation. First, take a look at this question I asked on Stack Overflow ...
1
vote
0answers
38 views

Should I Use Nulls/Blanks to Convey Meaning in a Related Column Representing a Category?

I'm designing a personal database that has a table which contains categories for seasonal events of a video game. In my design I have a related column from Events to Items (one to many [1:m] ...
0
votes
2answers
19 views

Postgres - Only allow insert if TWO columns match with a separate table

I have three tables: a users table, a books table, and a chapters table. Each book has an id called identifier_id (for good business reasons that I cannot work around). This id is guaranteed to be ...
1
vote
1answer
196 views

A question about foreign keys and many to many relationships

My question is mostly conceptual in nature. I have a database schema that involves a many-to-many relationship between a School table and Student table. This seems sensible to me, given that a school ...
0
votes
1answer
47 views

Delete all records in tables with reference to another reference

Sorry if the title to this question makes no sense. I really wasn't sure how else to word what I needed to do. So I have the following tables in my database: customers customer_id (PK) ...
0
votes
1answer
340 views

Disable foreign key checks during import

I have started a humongous (~800GB sql dump) import to a mysql 5.5. At some point I realized that while the db was being imported, the progress was starting slowing down as the db size increased; ...