Stack Exchange Network

Stack Exchange network consists of 174 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
Make your voice heard. Take the 2019 Developer Survey now

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.

1
vote
0answers
14 views

Strategy to manage foreign key from another system/database

I googled a lot but alas found nothing. This is a re-post from stackoverflow as someone suggested that it is better suited here. There is a system/database in the company which stores all the ...
-1
votes
0answers
9 views

#1005 - Can't create table `canteen_system`.`main` (errno: 150 “Foreign key constraint is incorrectly formed”)

CREATE TABLE Main ( Customer_Id INT(5), Customer_Name varchar(50), StallName INT(5), State INT(5), Worker_ID INT(5), Position INT(5), Feedback INT(5), PRIMARY KEY (Customer_Id), ...
0
votes
1answer
27 views

Problem with creating foreign key in database, errno 1239. Incorrect foreign key definition

I'm getting "errno 1239. Incorrect foreign key definition" when creating this table. Can someone explain why this isn't working? Here is my code: CREATE TABLE IF NOT EXISTS `game_review`.`game` ( ...
0
votes
3answers
15 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
30 views

PostgreSQL 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
44 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
40 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
15 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
22 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
24 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
42 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
45 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
39 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, ...
-1
votes
0answers
19 views

SHOW CREATE TABLE <tablename> not showing foreighn key name

I wanted to drop the foreighn key ,so i followed link & tried to find foreighn key constraint by below query : SHOW CREATE TABLE ecomexpress_awb; but it gave below result, its not showing ...
-1
votes
0answers
31 views

Foreign key referencing

I am making an application where you can select a user from the User’s combobox and a role. When the Insert User and Role button is clicked, the userid and roleid is added to tblUserRoles. The ...
2
votes
0answers
52 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
votes
0answers
54 views

Circular reference when designing DB

I have created the following ER-diagram. On UserHistory, I have added the following constraints: FK UserID => UserID (in User) FK TeamID => TeamID (in Team) onDelete: cascade onUpdate: cascade On ...
1
vote
0answers
37 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
17 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
40 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
25 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
39 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; ...
0
votes
1answer
208 views

Truncating a table with a foreign key constraint - SQL Server

I have two tables in a SQL Server Database, StudentAttendance and Attendance with the following structure: Attendance AttendanceID TimeTableID AttendanceDate UserName StudentAttendance ...
1
vote
1answer
30 views

Delete from table using FK columns, JOIN and LIMIT

This query below will never complete, since it always looks at the first 100,000 (user_type, user_ids). How can I write this to delete 100,000 records from the table with the FK reference (Product) to ...
1
vote
0answers
472 views

errno: 150 “Foreign key constraint is incorrectly formed”, I cant figure it out

I've been trying to get this to work for a while and I've found plenty of examples to compare it too. Yet no matter what I try I get the same error. Can you guys please take a look at this and point ...
2
votes
2answers
206 views

truncate table with foreign keys

I have to truncate a table which gave me the below error. ORA-02266: unique/primary keys in table referenced by enabled foreign keys. I fixed that by checking the below, SELECT p.table_name "...
1
vote
1answer
27 views

“Print” specific columns through foreign keys

I have three tables: Users Users_request Users_applications Table Users has the id column, that column is related on Users_request.id finally Users_request.id is related with Users_applications.id ...
1
vote
2answers
74 views

Does SQL Server automatically update the foreign key relationships between tables when one of those tables is renamed?

What happens to the foreign key relationships on a table if that table is renamed?
0
votes
1answer
85 views

Getting around Primary Key requiring non-nullable columns

Word Problem I need to be able to create a table that has a column which is a foreign key and is nullable, but is also part of the (composite) primary key. Obviously the problem is that (at least ...
0
votes
1answer
13 views

Getting data from 2 tables by JOIN, when @Foreign Keys exist to the same Table/Object

I have 2 Tables: User id | name | email | staff 1 name1 email1 true 2 name2 email2 no 3 name3 email3 true 4 name4 email4 no Note id | text | sender_id | receiver_id 1 ...
1
vote
0answers
33 views

postgresql handle conflict on delete

I have a table that looks roughly like this: create table people ( id bigserial primary key, name varchar, deleted timestamp with time zone, parent bigint references people(id) ...
1
vote
0answers
20 views

Ideal and Efficient Way of Handling User Logins with Multiple Types (i.e. Employee, Client, etc.)?

I'm developing a database where I will have two types of users: Employee and Client. I'm thinking it would be ideal to just have one big USERS TABLE with a user_ID as PK and Type column to ...
0
votes
1answer
50 views

Using NULL values for foreign key with normalization

I am creating a table as follows: Building (PK: buildingID) Area (PK: areaID, FK: buildingID) Room (PK: roomID, FK: buildingID, FK: areaID) All rooms are in a building. Some rooms can be in an area ...
-1
votes
1answer
27 views

Keeping reference in an array format

I have a design with an orders table and products table. Each order can have many products, so 1 order N products. The default design would be to have something like this: orders table order_id, ...
0
votes
1answer
279 views

How to disable foreign key constraints in Postgres dump file?

I used pg_dump -U postgres -p 65432 --table=tableName --data-only --column-inserts DBName>fileName.sql to dump one particular table's rows. Now I need to insert them to another database but I get ...
0
votes
2answers
147 views

mySQL - Cannot add Foreign Key Constraint [closed]

I'm trying to add a foreign key constraint but, mySQL is not taking it. No detailed error, just Cannot add foreign key constraint Here is my code from an export dump... thanks for any assistance -- ...
1
vote
0answers
46 views

Associate multiple tables with a single table

I have a RDBMS database and I need to associate a column in a new table to multiple other existing tables whose schema can't be changed. The new table looks something like this, UUID(pk) PartId(pk)...
1
vote
1answer
149 views

Clarification on Cardinality and Participation

I'm currently redesigning a schema with 20 or so tables and my first task is to create an ERD from the existing tables/constraints/relationships. This has proven more difficult than I expected because ...
1
vote
1answer
109 views

Creating index on foreign key column?

I have below table structure where one department can have many employees. Department id dept_name ... Employee id emp_name dept_id(foreign key to department table) .... ...
0
votes
2answers
53 views

Prevent deleting last child

CREATE TABLE Parent ( ParentID int NOT NULL PRIMARY KEY, ParentName varchar(50) NULL ) CREATE TABLE Child ( ChildID int NOT NULL PRIMARY KEY, ParentID int ...
0
votes
1answer
350 views

Error 1239: Incorrect foreign key definition

While creating primary keys and foreign keys for my tables I got the error 1239 and apparently I'm the only one having this error as I could not find anything with a Google search. I was wondering if ...
1
vote
1answer
266 views

Create foreign key on foreign table in postgresql [duplicate]

I need to link my table with another one which is in different database(say table logs in device db and table accounts in user db, both on same server). So using Foreign Data Wrapper I create a ...
2
votes
1answer
39 views

Identify data in PostgreSQL database in violation of foreign keys

Database PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit - this is on an Amazon AWS instance. I recently identified some orphan data in one of ...
1
vote
1answer
24 views

Finding entries which have no foreign key mapping in any other table

Let's say I have Table A, which has one column id, which is unique within the table. I have multiple other tables: Table B, Table C, ..., Table N. Each of these tables has a column A_id, which is a ...
4
votes
1answer
574 views

Understanding One to One Relationships

As far as I understand, a one-to-one relationship between two tables means that each row in one table matches exactly one row in the other. There a a number of reasons why this is useful, such as ...
2
votes
0answers
43 views

Design for a child to have a single parent `A` OR `B` with FK cascade constraint?

I'm assuming this is not possible with PK and FK... Let's say I have tables A, B, and C. A and B are "parents", and B and C are "children" A is the root B can be the child of either A or B C ...
1
vote
0answers
43 views

How to find which foreign key constraints a table row is associated with?

(There are similar sounding questions out there at first glance, but none any that are actually identical from what I've seen so far) I'm on PostgreSQL 9.4.7 In narrow terms of my use case (good ...
0
votes
1answer
632 views

MySQL WorkBench SQL : Cannot Add Foreign Key Constraint (Error 1215) while creating database

Problem: I am receiving the Error 1215: Cannot add foreign key constraint error while running my MySQL schema code generated from the EER Diagram I used to model my database. The Error occurs ...
0
votes
0answers
223 views

H2: Drop index which belongs to a constraint

We have a schema "Logger" that contains two tables: LogEntry (PK: Id) LogExceptions (Combined PK:LogEntryId,LineIndex) LogProperties (Combined PK:LogEntryId,PropertyKey) The schema has a foreign key ...
4
votes
2answers
240 views

Advantages to column constraints over table constraints

Is there any advantages whatsoever, other than style to writing this, CREATE TABLE foo ( a int PRIMARY KEY, b int ); And then, CREATE TABLE bar ( a int REFERENCES foo, c int ); Over, ...