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

1
vote
2answers
2k views

Foreign Key Constraint fails

I have the following tables: // Base Scans CREATE TABLE `basescans` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(100) NULL DEFAULT NULL, `status_id` INT(10) UNSIGNED ...
4
votes
1answer
139 views

SQL Server 2005, Foreign key check against part of a table

I have an articles table that references a categories table. I'm defining the foreign key like this: constraint fk_categoryid foreign key (categoryid) references categories (categoryid) on update no ...
2
votes
1answer
2k views

Will FK ON UPDATE CASCADE work?

Intuitively, adding the option ON UPDATE CASCADE to a foreign key constraint will have the effect of updating all referencing columns with the updated value of the key. Would that really work? ...
1
vote
1answer
149 views

Are these custom module Drupal tables lacking foreign keys? mysqldump with table structures without data is included

I am troubleshooting a some tables in MySQL used by a custom Drupal module. Documentation for these tables is poor so I used mysqldump to get the structures in the Drupal database without the data. I ...
1
vote
3answers
301 views

Mysql error while creating FK

here is structure of both tables category table CREATE TABLE category ( `catID` bigint(20) NOT NULL AUTO_INCREMENT, `title` varchar(60) NOT NULL, `created_at` timestamp NOT NULL DEFAULT ...
2
votes
1answer
121 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
734 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 ...
4
votes
2answers
844 views

Using Multi-table Insert for Parent and Child Table

Is it safe to use Oracle's multi-table insert statement to insert into a (foreign key constrained) parent and child table? With minimal examples, I've found that it works as long as the parent table ...
4
votes
1answer
3k 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
136 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
716 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
107 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
538 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
2k 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
755 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
876 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
87 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
223 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
628 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 ...
1
vote
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 ...
2
votes
2answers
310 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
88 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
242 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
93 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
5k 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
487 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
651 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
613 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
271 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 ...
5
votes
2answers
2k 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
123 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
567 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
987 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
441 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
639 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
1k 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
3k 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 ...
10
votes
2answers
945 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 ...
3
votes
1answer
961 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
247 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 ...