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.

1
vote
2answers
36 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
28 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
34 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
21 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
24 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
47 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
27 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
33 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
18 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
30 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
25 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
26 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
32 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
24 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
16 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
47 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
49 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
326 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
24 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
81 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
44 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
54 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
48 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
62 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
95 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
33 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
136 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
317 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
34 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
776 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
396 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
28 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
145 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
110 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
15 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
40 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
59 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
384 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
301 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
48 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
238 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
272 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
56 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
642 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 ...