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.

learn more… | top users | synonyms

3
votes
1answer
1k views

foreign key constraints on same table

I have a table structure like this. tblquestion ( nQuestionId1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT_NULL, nQuestionId2 INT UNSIGNED ALLOW NULL ); Here..nQuestionId2 has a foreign key ...
3
votes
2answers
700 views

DROP TABLE … CASCADE does not drop any dependent table

I am still relatively new to psql and I was just finishing an assignment when I decided to try something which was to drop a table which other tables depend on due to a foreign key relationship. My ...
3
votes
1answer
14k views

Column name or number of supplied values does not match table definition error

I have a table, with two foreign keys defined on two columns of that table. The problem is that, when I insert the values from SQL Server, I get an error: Column name or number of supplied values ...
3
votes
2answers
239 views

Pair of 2-tuple composite FKs pointing to same table (closure table pattern)

I don't have much experience with DB design, and having a hard time figuring out what's wrong with the following: Table father has a composite PK (col1, col2) Table closure has ancestor(a-col1, a-...
3
votes
2answers
188 views

Tablestructure for fast inserts/deletes with foreign keys

Current Situation We have a table called c with ca. 300,000 rows. In this table we store the competitors for a specific product. Example: id | competitors | some infos about competitor | ...
3
votes
1answer
137 views

Unique foreign key constraint between three tables

I'd like to track appointment attendances. Visitors can have appointments, or they can just turn up. If they have an appointment, and miss it, in which case I'd like to record a reason for the missed ...
3
votes
1answer
773 views

Question on Design for User + User Groups + User Types + Subscriptions Model

I am redeveloping an existing web application with both weak source code and a weak underlying database design. I'm trying to develop things in a way that future expansion will be much easier, namely:...
3
votes
1answer
92 views

Is it Ok to use GUID as the Foreign Key?

Here's my current situation: We have a table storing data submitted from the website form, say "MyTable". The table uses bigint (auto-increment) as the primary key. Everything is good so far. Now, ...
3
votes
2answers
116 views

Design encouraging FK to non-unique/non-PK

My current design is makes me want to create a FK to a non-unique/non-PK. Is this a code smell and/or does my overall design make sense? CREATE TABLE [User].[ConnectionRequest] ( [...
3
votes
3answers
700 views

Reasons for db2 foreign key error (-667) despite all rows appearing sane?

I have some csv formatted table dumps and loaded those into a new database with the tables defined by a DDL script from the source database (both acquired by somebody else so I cannot have a look at ...
3
votes
1answer
4k views

Insert Row With Foreign Key Equal to ID - Circular Reference

In MySQL database I have an events table which has a circular reference to itself with the following columns. id (PK) | user_id (FK) | event_id (FK) | title Each new row inserted has a circular ...
3
votes
1answer
5k views

Foreign key of multiple tables

I have the following 3 tables: ╔═════════════════╗ ║ Companies ║ ╠═════════════════╣ ║ id ║ ║ name ║ ║ type ║ ║ etc... ║ ╚═════════════════╝ ╔════════...
3
votes
1answer
27 views

enforcing data integrity for sequential relations

These are my tables: create table trips ( trip_id serial primary key, trip_nm text ); create table trip_segments ( segment_id serial primary key, departure_ts timestamp with time zone, -- ...
3
votes
1answer
31 views

Integrity Issues in this structure?

Imagine the situation. There are three tables, Student table[PK Student ID] Enrollment Table [PK EnrollID, FK Student ID] Fees Paid [PK Fee ID, FK EnrollID, FK StudentID] Edit: Fee_Paid Table ...
3
votes
1answer
85 views

How to remove unreferenced rows in a “Many to One” relation?

in my db i have the following entities: Cities, Persons, Houses. Each Person MUST live in a City. Each Person CAN live in a House. Each Person can change or leave their Houses. Persons living in ...
3
votes
1answer
565 views

Foreign keys become untrusted after bulk insert

In a SQL 2014 edition server (12.0.2430.0 - no SP1 yet) with a database in 2012 compatibility mode (working on getting it switched to 2014...) I have a handful of foreign key objects which are ...
3
votes
2answers
612 views

multivalued weak key in ER database modeling

I was wondering since i didnt find out any clarification for this. I want to store movies that exist in different formats (dvd, bluray etc) and the price for each format differs from each other as ...
3
votes
2answers
10k views

How to make a trigger that will compare input with a value of other table?

I had made two tables with the following fields: TABLE 1: "Bookings" FIELD: "fk_flight_number" FIELD: "date_of_reservation" TABLE 2: "Flights" FIELD: "flight_number" FIELD: "...
3
votes
1answer
1k views

Dump PostgreSQL schema with foreign keys and referenced data

The official PostgreSQL documentation states that pg_dump can use the --schema=SCHEMA option to selectively dump a given schema. However according to the same documentation (read Note on --schema=...
3
votes
1answer
4k views

Why does altering a table and adding a foreign key create an extra constaint? [closed]

Edit: It was a default constraint as a result of setting a default value when adding the column. The name gave it away when starting with 'DF__'. Anyway, solved it by adding the column and manually ...
3
votes
1answer
123 views

Soft ON DELETE CASCADE

I am familiar with the ON DELETE CASCADE method, but I don't want to do a real delete, just a soft delete. This is implemented with a column named isDeleted (0/1). Is there a way for me to cascade ...
3
votes
1answer
133 views

Dropping Foreign Key In Production Environment

I have been asked to drop foreign keys on a number of databases in our production environment. I am relatively new to MySQL... what do I need to be aware of when doing this? E.g. full table locks, ...
3
votes
1answer
278 views

Table structure for validating data using category and subcategory fields

I am creating a simple budget application, and the primary table will be a transaction table that contains things like the amount of the transaction, date, account, the category and subcategory (the ...
3
votes
1answer
48 views

Adding NotNull FK field in existing DB

Context: We’re building a web application to manage business-data. Each “client” of the application has a separate DB (this wont change), and each DB is based off a model (so they are all identical in ...
3
votes
1answer
96 views

Store different rows type in single table

I'd like to create a DB to store different rows type. It will be kind of event list with time stamp. So I'd like to have an Event table with relations to other tables - each table will be a row type (...
3
votes
1answer
2k views

How to add FK on a column of composite type in PostgreSQL?

I have a table which contains a composite type and I am trying to connect a column of it to another table's PK. CREATE TABLE aaa (col1 SERIAL PRIMARY KEY); CREATE TYPE bbb AS (col2 INTEGER); CREATE ...
3
votes
1answer
553 views

Is it possible to check all constraints in a MySQL database?

MySQL offers the ability to temporarily disable the foreign key checks, thus allowing inconsistent data to enter the database. Is it possible to check an entire MySQL database, table by table, row by ...
3
votes
1answer
78 views

Convert a lookup value to foreign key when a query is run

Currently we have a legacy system running queries on a Postgres server to update/insert records. One of the obstacles we are having is that we have to convert text values to the correct foreign key ...
3
votes
0answers
64 views

How does mysql(innoDB) handle same foreign key for join

I have a table called X and another called Y. There is a primary key on Y that is a foreign key on X. Now, my data is such that there will be many rows in X that will have the exact same foreign key ...
3
votes
2answers
38 views

Reasons to maintain FKs on an empty, deprecated table?

So I have a few dozen empty tables with foreign keys to other, populated tables. These tables are not being accessed by any CRUD operations, but have about 6-12 months before they can be unlinked from ...
3
votes
0answers
512 views

Restoring from pg_dump with foreign key constraints

In restoring a database from a pg_dump, a number of errors are being generated and the whole table is subsequently being ignored. An example: ERROR: insert or update on table "channelproducts" ...
3
votes
1answer
142 views

Many to many unique combination

So I originally thought I was going to have a one to many relationship in a database with the following tables ENTITY_JSON_MAP ATTRIBUTE_JSON_MAP Originally I was going to have an FK in ...
2
votes
2answers
705 views

Generalisation in databases

I am working on a Hospital Management project in DB2. I have one table for staff with a primary key of staff_id. The hospital has to have different staff... e.g doctors,nurses,e.t.c. Is it better to ...
2
votes
3answers
122 views

Can't add Foreign Key Constraint

As far as I can tell it should work, I went through other similar questions but it seemed to be a different thing causing the same error. CREATE TABLE Job ( JobNumber INT NOT NULL, JobName ...
2
votes
1answer
7k views

Using a foreign key on multiple fields in one table

Simple question probably. For two tables. A one to many relation is created from A -> B, TeamID -> Fk_Team, however there are multiple fields that need to reference this one relationship how does ...
2
votes
3answers
649 views

What is the optimal solution for converting a database schema?

I actually want to get rid of the existing constraints in the database which I know sounds crazy but we are upgrading our application to a new framework which relies on the foreign key constraint ...
2
votes
1answer
1k views

size of a primary key and foreign key

Do a primary key and a foreign key referencing it must have the same column size in sql server 2008 ? could someone indicate me a link to a documentation explaining this ? Thank you for help.
2
votes
1answer
92 views

Is it necessary to drop foreign keys before adding a null column to a table and then re-add them?

I have generated some SQL Server(2008) upgrade scripts using SqlCompare. The primary objective is to add a null column. However, the generated script wants to drop foreign keys in a couple of other ...
2
votes
1answer
2k views

Foreign Keys with Composite Key

Imagine I have a table with the following dependency: (Name, BoughtFrom, TimeBought) --> Cost Where the three attributes in bold form a composite primary key for the table. Now then, I want to ...
2
votes
1answer
2k views

How do you avoid a foreign key constraint creating deadlocks?

So I have two tables that create a deadlock. The application literally do nothing but update two different tables in two different transactions. There is a foreign key constraint (not cascading, just ...
2
votes
1answer
83 views

How to get dependencies order

I have a Database Called Total and I have about 40 tables in it.I am trying to move the tables in the database into a different server So I have prepared all the create table scripts but the problem ...
2
votes
1answer
728 views

Foreign Key NULL vs Foreign Key to an empty string

This is a table for a catalog of medicines. Some have a pharmaceutical brand, others are generic (i.e. they will never have brand information) CREATE TABLE medicine ( id serial PRIMARY KEY, ...
2
votes
2answers
141 views

EAV-like table with both primary column pointing to the same foreign column

Background: I'm a programmer who, while had to work with simple databases several times, never had to work with complex ones. I.e. I'm proficient with basic stuff, but clueless about advanced features ...
2
votes
2answers
377 views

Performance effect of indexing foreign keys

My database is for an HR application with performance reviews and I am interested in whether I should index my foreign key. There are two tables involved. The first is a ratings table with 2 columns: ...
2
votes
1answer
590 views

Table for optional parent/child relationship

Assuming we have the following table: Item, Parent, Child and Parent is the parent of child. The item can either belong to a parent or child and not both. I have other tables that are similar to ...
2
votes
2answers
405 views

Should I drop a foreign key if I use a soft delete?

"ON UPDATE" scenario never happens in my application. As soft delete is used, "ON DELETE" cascade and restrict scenario never happen as well now. Then, should I just drop foreign key because I have ...
2
votes
1answer
106 views

Conditionally join table

I'm new to database design and have designed a database that seems fine for the application but looks like it has a major red flag to me. I have a master table which is then joined to another table ...
2
votes
1answer
4k views

MySQL Foreign Key Prevents Deleting Table — But Table is Empty

Why would dropping an empty table produce a foreign key error? In MySQL, I have a table with the following definition CREATE TABLE `enterprise_rma` ( `entity_id` int(10) unsigned NOT NULL ...
2
votes
1answer
113 views

Better way to manage data integrity, without having too many composite FK's

I have following tables: class_table (class_id = Auto Incremented Primary Key (AIPK) of this table) user_table (class_id, user_id (AIPK of this table)) course_table(class_id, course_id (AIPK of this ...
2
votes
1answer
2k views

Is it possible to force drop of mysql tables with FK?

For dropping a table in which a Foreign Key is used in other tables, it is necessary to drop all other tables having FK connection with the corresponding table. Is there a short way to drop a table, ...