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

5
votes
1answer
4k views

disable constraints before using pg_restore.exe

When I try to execute pg_restore.exe of a dump file from a database, it throws dozens of errors, all the same: ERROR: insert or update on table "someTable" violates foreign key constraint ...
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 ...
1
vote
2answers
152 views

How do automaticly delete rows from other tables

I have a MySql DB. I have a table - recipes. Also have 2 tables -ingredients and instructions. both have for each entry - a foreign key to the related recipe. I want that when I delete an entry from ...
2
votes
1answer
772 views

Foreign key with multiple table options

I'm not sure if the title is very good, feel free to suggest a better one. People can "comment" on different objects of my site,similarly to StackExchange (for example they can comment on a Question, ...
2
votes
1answer
112 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
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 ...
4
votes
2answers
4k views

Primary key vs. unique index: performance difference with foreign keys?

Will there be any difference in performance if you compare a query fetching data via a join between two tables where there's a relationship against either of the following: A primary key A unique ...
3
votes
2answers
2k views

Will a delete then insert within a transaction cascade?

I had a question about what the expected and actual behaviour for the following scenario is. The scenario is one where a database table ([table1]) is cleared and reloaded every day. The table has an ...
1
vote
1answer
627 views

How can I check the integrity of disabled foreign key relationships for each individual table? (SQL Server 2008)

I'm porting a huge legacy database that many different legacy frontends depend on. When I'm finished, a new frontend will be running alongside all the old legacy frontends minus the frontend I ...
1
vote
2answers
3k views

MySQL - automatically update all references to specific table to use on delete cascade?

I have a specific table for one of my software packages on a MySQL 5.1 box which I need to drop a few hundred entries. These are user names with names in uppercase, the application is case specific ...
3
votes
1answer
874 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; ...
0
votes
1answer
1k views

Is there a way to skip errors in procedures and resuming procedure

When there is a unique key in a table (not PRIMARY KEY) and procedure is running, on duplicate key error the whole process will be halted. I want to resume on error and call the procedure again. ...
1
vote
0answers
89 views

Dealing with Invisible Referencers

Using Oracle database, suppose I have a parent table parent defined in schema A, and a child table child defined in schema B. To achieve this, schema A granted references privilege on parent to schema ...
3
votes
2answers
228 views

Both Primary and Foreign Key Needed on Table?

Someone recently asked this question: There are two tables: Deal and DealCategories. One deal can have many deal categories. So the proper way should be to make a table called ...
24
votes
3answers
1k views

Is using multiple foreign keys separated by commas wrong, and if so, why?

There are two tables: Deal and DealCategories. One deal can have many deal categories. So the proper way should be to make a table called DealCategories with the following structure: DealCategoryId ...
5
votes
2answers
726 views

Teradata: How to design table to be normalized with many foreign key columns?

I am designing a table in Teradata with about 30 columns. These columns are going to need to store several time-interval-style values such as Daily, Monthly, Weekly, etc. It is bad design to store ...
2
votes
1answer
2k 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 ...
3
votes
2answers
324 views

Foreign key constraints with MySQL/InnoDB

I am try to do some performance testing with varchar foreign keys vs int foreign keys and was wondering how foreign key constraints effect performance of MySQL. I assume that they effect ...
1
vote
3answers
90 views

Need suggestion for placing foreign keys in the databases

I have the database for customers. The customers table is something like this +------------------+ | Customers | +------------------+ | id (PK) | | business_email | | business_name ...
14
votes
4answers
3k views

Where should you define foreign keys?

Is it better to define foreign keys in the database or in the code part of an application?
0
votes
2answers
328 views

Merging Results while avoiding Key Constraint Error?

I have a table with a primary key which is two integer fields, each a foreign key to a different table. (Multi to Multi table) In one of the foreign tables two rows are merged and this table is ...
2
votes
1answer
94 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 ...
1
vote
2answers
6k views

How to use RESTRICT for Foreign Key in mysql?

In the database structure of CREATE TABLE Country ( name varchar(40) NOT NULL, PRIMARY KEY (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE City ( name varchar(40) NOT NULL, ...
1
vote
1answer
585 views

Usefulness of Foreign Key WITHOUT reference option

Sorry, if this is naive question, but I searched a lot and did not get a vivid answers. Consider a simple InnoDB FOREIGN KEY without ON DELETE or ON UPDATE I think this will avoid INSERT of a value ...
3
votes
2answers
1k 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 ...
1
vote
2answers
2k views

Usage of RESTRICT or No ACTION in FOREIGN KEY Constraints

Sorry if this question is naive, but I was unable to understand possible usage of RESTRICT or No ACTION. Consider a simple database of CREATE TABLE column1 ( first_id int(11) NOT NULL AUTO_INCREMENT, ...
-1
votes
2answers
737 views

Multiple child tables

I would like to know what is the best solution for making a parent table with multiple children? I have to make multiple children table. One for each profile. Is there a better idea than having 3 ...
4
votes
4answers
677 views

How are foreign key indexes helpful?

How are foreign key indexes helpful? What is the criteria for keeping a foreign key index?
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 ...
6
votes
2answers
3k views

How to retrieve foreign key constraints data

I'm looking for a query allowing to retrieve foreign key infos (each line: referrencing table & field, referrenced table & field) of an entire schema. I've found this, but does not gives all ...
4
votes
1answer
2k views

How do you create a relationship to a non-primary key in SQL Server?

I have a Users table which has two columns a primary key called UserID and another column called UserName. UserID (int) PK UserName (varchar(256) They are both unique but I decided for reasons to ...
1
vote
3answers
124 views

If A is next to B, how to automatically assert that B is next to A

I am working in Access 2010. Let's say I have a database that tracks where packages sit in a warehouse. Table: Packages PackageID (PK) PackageHeight PackageWidth PackageWeight PackageOwner (etc) ...
2
votes
2answers
600 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 ...
6
votes
3answers
1k views

How to normalize Time Sheet data?

I am having a terrible time finding an appropriate way to store this data in a database. The current excel (simple) version looks something like this: http://i.stack.imgur.com/BEZOw.png Staff and ...
2
votes
3answers
480 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
662 views

Will a row update check all child tables which have foreign key relationships in InnoDB?

I have a master table and around 60 to 70 child tables, all are InnoDB tables and have foreign key relationships with the master table. Foreign key relations are On-Delete Cascade and On-Update ...
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 ...
4
votes
2answers
2k views

Does a view need its own foreign key constraints?

Disclaimer: I'm a programmer, not a DBA, so bear with me... I have a view that I use to just map 2 entities together. I have to do a join between a few different tables to get that: CREATE OR ...
5
votes
2answers
4k views

MySQL, foreign key, can't create table error 150

UPDATE: Sorry, it was because i forgot to add UNSIGNED attribute to the lang_id column. Original: I'm trying to create a table with foreign key in MySQL. I get this error: #1005 - Can't create table ...
9
votes
2answers
1k views

What is the purpose of SET NULL in Delete/Update Foreign Keys constraints?

I am probably being narrow minded, but if I create a foreign key constraint and a row gets updated or deleted, I lose that connection if the child table's column gets set to NULL. What is the purpose ...
4
votes
1answer
1k views

Foreign Key constraint on fixed value field

Note: I am a developer... I have an Asset table that has many codes that are foreign keys into a second table OutlineFiles. The OutlineFiles table has Type and Code as the primary key. The foreign ...
1
vote
1answer
255 views

InnoDB Foreign key updating vs. manual in-application updating

I've been working on a "legacy" schema in MySQL Workbench that uses a lot of relations. All of the tables are MyISAM, however, and there are no foreign keys. However, I'm working on a new area of the ...
4
votes
2answers
808 views

Using IDs from multiple tables in a single column

One of my co-workers created a schema similar to the following. This is a simplified schema including only the parts necessary to address this question. The system rules are as follows: ...
2
votes
1answer
137 views

InnoDB foreign key and index duplication?

I'm have a part table like this: part_id INT manufacturer_id INT supplier_id INT part_number VARCHAR(50) part_description VARCHAR(120) with foreign ...
0
votes
1answer
136 views

Derived value not calculating as expected using combination of FK and Triggers (MySQL)

I'm having trouble with calculating a derived value using a combination of triggers and foreign key constraints. There are 3 tables involved in this arrangement responses ------------------------ ...
0
votes
2answers
1k views

Query PostgreSQL 9.0 table on foreign key value?

Noob-type question: Say I have two tables, candies and colors. The colors table holds just code/value pairs like 01 yellow, 02 blue, 03 green, etc. and is referenced in the candies table by code. ...
6
votes
2answers
3k views

Non-unique multicolumn foreign key

I have a "comments" table that models a conversation on a topic, like this: id serial topic_id integer parent_comment_id integer body text So, every comment has a reference to its topic AND ...
2
votes
1answer
578 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, ...
3
votes
1answer
870 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 | ...
10
votes
3answers
199 views

Are there any database engines which will intuit the join condition based on the existing foreign keys?

It seems strange to me that, when I've defined a foreign key, the engine cannot use this information to automatically figure out the correct JOIN expressions, but instead requires me to re-type the ...