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
2answers
165 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, ...
3
votes
1answer
2k views

Setting up a Postgresql Partition with foreign key

I have a Ubuntu 12.04 Server with PostgreSQL 9.1 installed. I am trying to setup Partitioning on a existing Postgres DB(say 'xyz') which has very huge tables and some have foreign keys set on them. ...
3
votes
2answers
160 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
79 views

Normalized Data Store - Confused with prefixes to use

I'm designing a Staging+NDS+DDS Data Warehouse system, where an ETL is going to normalize data from [Staging] and load it into [NDS], which will hold all history. I've pretty much finished the T-SQL ...
3
votes
1answer
2k views

Foreign key of multiple tables

I have the following 3 tables: ╔═════════════════╗ ║ Companies ║ ╠═════════════════╣ ║ id ║ ║ name ║ ║ type ║ ║ etc... ║ ╚═════════════════╝ ...
3
votes
2answers
2k views

Using Foreign Key and References for ON DELETE CASCADE in mysql

I was thinking that these two ways for creating foreign keys are the same CREATE TABLE child1 ( id int(11) not null auto_increment, parent_id int(11) REFERENCES parent_table(parent_id) ON DELETE ...
3
votes
2answers
438 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
4k 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
604 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 ...
3
votes
1answer
2k views

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

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
889 views

Foreign Key violation - not sure why

I have the following two tables with a foreign key on status_id: mysql> describe usr_cookbook; +-----------+-----------+------+-----+-------------------+-------+ | Field | Type | Null | ...
3
votes
1answer
265 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
1k views

Generate insert statements for table AND related data

I'm looking to move data from tables and related tables between 2 databases. The trouble is the primary keys are out of sync. Is anyone aware of a tool which can generate insert statements for a ...
3
votes
1answer
939 views

MySQL 5.5.8 InnoDB Foreign Key, JOIN performance

We are using aws/rds, MySQL 5.5.8. all InnoDB. For performance purposes, we have merged some data-elements into varchar fields; mini-sized int and date type fields; added covering indexes; ...
3
votes
1answer
106 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
3answers
215 views

Cascade primary key update to all referencing foreign keys

Is it possible to update a primary key column value with cascading the update among all the foreign keys referencing it ? # EDIT 1: When I run followinq query select * from sys.foreign_keys where ...
2
votes
1answer
709 views

Two-column foreign key constraint only when third column is NOT NULL

Given the following tables: CREATE TABLE verified_name ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, UNIQUE (name, email) ); CREATE ...
2
votes
2answers
272 views

MySQL - Normalization over meaningless data - Compound Primay Keys or Surrogate ID field

Please forgive my title; if you have a suggestion for it, feel free to comment. I have a database: DROP TABLE IF EXISTS `books`; CREATE TABLE `books` ( `isbn` VARCHAR(255) NOT NULL, `title` ...
2
votes
2answers
612 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
1answer
427 views

Why dropping all foreign keys from a script is not working? [closed]

I have sysadmin rights on a SQL Server 2008 R2 box. I found a script that can drop all foreign keys and re-add them for us when we need to after a testing import. So I execute the below code with no ...
2
votes
1answer
2k views

If foreign keys/cascade deletes are bad, why use a database-server with that feature?

I noticed wordpress/rails etc do not use foreign keys constraint or cascade deletes features from database. Instead they handle this in PHP/Ruby/scripting level! I have read this and this. Most ...
2
votes
1answer
2k views

How can I create a Foreign Key to a Compound Primary Key with a single column?

I am working with a 'lookup' style table that is already in place, which has a compound primary key (not my design) in Microsoft SQL Server 2012. I want to add a foreign key to it in another table, ...
2
votes
2answers
1k views

Postgres - Create table with foreign key constraint too slow

I have a table called "Account" that is a heavily used table, has 17 columns and has upwards of 300,000 rows. I am trying to create a new table, "NewTable", that has a foreign key constraint to ...
2
votes
1answer
3k 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
500 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
179 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
84 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
222 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
1k 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
82 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
2answers
59 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
236 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
2answers
99 views

Postgresql - Do Foreign Key Constraints Automatically Create Indexes?

Per the PGSQL 9.3 docs: Because this is not always needed, and there are many choices available on how to index, declaration of a foreign key constraint does not automatically create an index on ...
2
votes
2answers
108 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
72 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
1k 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
106 views

How to enforce a nullable foreign key?

I have a relation between two tables. The foreign key table can have a row related in the primary table. There's a way to enforce the value of the FK column to be NULL or one of the values of the PK ...
2
votes
1answer
124 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 ...
2
votes
1answer
907 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 ...
2
votes
1answer
117 views

Constructing Circularly Referenced Tables

I'm attempting to construct two tables in Oracle, A and B; each of these tables reference each other. In my script, I have the foreign key constraints 'in-line' of the create table statements. Of ...
2
votes
3answers
365 views

Altering table Foreign Keys produces an error (errno:150) (MySQL)

This is the first database I set up so I apologize if my problem is something obvious. I'm playing around with my table design and tried to create some link tables with FKs to avoid redundant data. ...
2
votes
1answer
1k views

Need for indexes on foreign keys

I'm struggling with indexes, primary keys and foreign keys... And the need of having them all. If I have two tables, both of them have an integer as a primary key. The first table references ...
2
votes
1answer
3k 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 ...
2
votes
1answer
95 views

Linking identifiers across tables in MySQL (Foreign Keys)

Given a simplified example of two tables table Contact(ID int primary key, Name text not null) table ContactPhone(ContactID int not null, PhoneNumber text not null) Is there a way to tell MySQL ...
2
votes
1answer
593 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, ...
2
votes
1answer
1k views

Error Creating Foreign Key in Oracle

I have 2 tables: PRODUCTS PREFIX SUFFIX ATTR1 ATTR2 ----------------------------------------------------- 001 AAA 50 100 001 BBB 20 300 010 ...
2
votes
1answer
436 views

MySQL Unique Index Keylength

I am hashing keys from one table that provides a 256 bit (64 char length) alphanumeric string that I intend to use in a second table as a foreign key. The engine of choice here is InnoDB. There are ...
2
votes
1answer
275 views

Do you need to include all foreing keys of a parent table in the child table?

Suppose you have a table my_table ----------- id name foreign_id And you are creating another table that will reference my_table. I think that this would be enough: other_table ----------- id name ...
2
votes
1answer
141 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 ...
2
votes
1answer
62 views

Foreign key, key split in two tables

I have to record a list of events made of action, reason (optional, may be multiple), user. I have a master table with all the valid action, and another with all the valid action-reason combination. ...