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.
12
votes
4answers
2k 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?
12
votes
1answer
755 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 ...
9
votes
3answers
178 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 ...
8
votes
2answers
578 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 ...
6
votes
3answers
700 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 ...
6
votes
1answer
99 views
Ok to drop indexes on FK's if they have no stats in the DMVS
I have used the well known query below from Kevin Kline to check for unused indexes. Several indexes created on Foreign keys returns no read stats, only writes.
Are you 100% safe to drop these ...
6
votes
3answers
273 views
What alternatives exist when a table requires too many foreign keys?
We have a base table that defines parts and holds information like part number, description, price, weight, etc. We also have approximately 400 tables that reference the base table and provide ...
5
votes
3answers
227 views
What does the output of a JOIN statement look like?
I've been wanting to use joins for a while, but I'm having trouble visualizing the output so I know how to put it to use.
Let's say I have 2 tables:
CREATE TABLE Cities (
id INT UNSIGNED PRIMARY ...
5
votes
2answers
705 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 ...
5
votes
2answers
2k 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 ...
5
votes
2answers
378 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 ...
5
votes
1answer
2k 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 ...
4
votes
4answers
420 views
How are foreign key indexes helpful?
How are foreign key indexes helpful? What is the criteria for keeping a foreign key index?
4
votes
1answer
179 views
Performance tuning a cascading delete with many foreign keys
I have a delete query that is taking a long time. Looking at the execution plan, I see that most of the estimated cost in the delete query is in a section of the data model that had a lot of data ...
4
votes
1answer
1k 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 ...
4
votes
2answers
95 views
Foreign keys - link using surrogate or natural key?
Is there a best practice for whether a foreign key between tables should link to a natural key or a surrogate key? The only discussion I've really found (unless my google-fu is lacking) is Jack ...
4
votes
2answers
689 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 ...
4
votes
1answer
85 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 ...
4
votes
2answers
2k 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 ...
4
votes
2answers
537 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:
...
3
votes
2answers
1k 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 ...
3
votes
2answers
335 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 ...
3
votes
1answer
76 views
Optimised Schema for City-to-City Distance Table
This is my first question, forgive me if this question is simple. I'm stuck here, trying to implement the relations for the data represented below. I want to scale it vertically (new rows), instead of ...
3
votes
1answer
602 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 ...
3
votes
1answer
66 views
Many:Many with Shared Relation
I'm modelling data with multiplicity like this:
Each Composition/Anthology related pair must share a Composer. Also, each Anthology must contain at least one Composition. How would you recommend I ...
3
votes
1answer
93 views
Symmetric self-referential many-to-many
I'm trying to model a hierarchy of categories where a category can have multiple parents ( an overlapping tree model as described in this book )
I have the following tables
video_categories
int ...
3
votes
2answers
137 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
2answers
178 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 ...
3
votes
2answers
528 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
474 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
136 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
447 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
714 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
181 views
Schema design: Use of association (aka: bridge/junction) table vs foreign key constraint with composite index containing a non-key field
This is an inventory database for IT assets. The models used are trimmed in order to focus on the problem at hand. Using SQL Server 2008. Thanks for taking the time to read and for any input you can ...
3
votes
1answer
504 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 ...
2
votes
2answers
126 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
453 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
298 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
373 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
3answers
297 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
78 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
56 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
443 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?
...
2
votes
1answer
105 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
88 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
1answer
47 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
396 views
Foreign key of multiple tables
I have the following 3 tables:
╔═════════════════╗
║ Companies ║
╠═════════════════╣
║ id ║
║ name ║
║ type ║
║ etc... ║
╚═════════════════╝
...
2
votes
1answer
206 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
86 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
383 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, ...
