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
1answer
450 views

Is there a workaround for lack of support of cascade triggers with MySQL?

As things stand, cascaded foreign key actions do not activate triggers with MySQL. I don't want to get into the debate as to whether or not this is good, I just would like to know whether there is a ...
6
votes
3answers
1k 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 ...
2
votes
1answer
188 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
2answers
258 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` ...
0
votes
1answer
1k views

Foreign Key - One Child Table to Multiple Parent Tables

Maybe I'm thinking of this problem in the wrong way, but we maintain an AccessControl table that is a child to several parent tables (e.g. CompanyItems, SystemFiles, etc.). I'm trying to make sure we ...
1
vote
2answers
236 views

Foreign Key constraint on fixed value field - Ever appropriate?

I have a short (15 rows) look-up table that lists the valid values for several columns in the database. I don't think it can be considered a One-True-Lookup-Table, it is about a single definite ...
1
vote
0answers
540 views

How to represent class table inheritance (current DBMS-specific way please)?

I want to implement class either-or table inheritance (Account, CatAccountDetails, DogAccountDetails) in SQL Server. I found a recommendation for MySQL here (How do I map an IS-A relationship into a ...
0
votes
1answer
259 views

Retrieving all PK and FK

I have a big database that I need to extract all primary keys and foreign keys from each table. I have pgAdmin III. Is there a way to do this automatically and not go over each table manually?
6
votes
3answers
704 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 ...
1
vote
2answers
251 views

Use single table with foreign key or two twin tables for text entries?

I have a table answers that has several columns including the content iself such as ID | q_id | list_id | user_module_id | content | updated_timestamp The content column is a varchar since we want ...
3
votes
1answer
1k 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 ...
1
vote
1answer
148 views

Multiple primary keys

I am new to database design and have been looking at some example data models here. One thing I don't understand is if you have a table like client_addresses from the link above: ...
1
vote
1answer
456 views

Missing PK/FK after database migration

We recently had a server migration. A new server is now the PROD server, and what used to be the PROD server is now the staging environment. Apparently, the data was transferred correctly, but the ...
0
votes
2answers
333 views

Naming foreign key columns

Now I hear that the best practice for naming foreign key columns is the name of the table and field (and maybe the function if there are multiple foreign keys to the same table), for example, a ...
0
votes
1answer
58 views

Check for records that break a foreign constraint (created with foreign_key_checks = 0)?

Is there an easy way to check for any records in a table that breaks any constraint? The constraint was created on a populated table using "foreign_key_checks=0".
4
votes
1answer
169 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 ...
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 ...
5
votes
1answer
2k views

Many to Many and Weak Entities

I have an entity that cannot exist without being defined by another, and I want this entity to participate in a many-to-many relationship. Example: An artist has an album (the album cannot exist ...
0
votes
1answer
5k views

To drop a table, do I have to drop all foreign key constraints or can I just NOCHECK them?

I know you cannot drop a table that has active foreign key constraints. So I was wondering is it safe to: ALTER TABLE myTable NOCHECK CONSTRAINT all DROP TABLE myTable Rather than dropping the ...
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
1answer
246 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
2answers
395 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 ...
0
votes
1answer
307 views

Modify MySQL foreign key related column without dropping foreign key relation

I need to modify a foreign key related column. When I tried to alter it I got "Error on rename of 'x' to 'y' (errno: 150)" error. So I googled and found foreign key relation is the 'villain'. I ...
6
votes
1answer
137 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 ...
3
votes
1answer
132 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
2answers
3k views

How much do foreign keys affect performance?

I'm reviewing an SQL Server 2008R2 installation having large performance problems. Currently I'm looking into its indexes and foreign keys and have found out that none of the tables have any foreign ...
5
votes
1answer
1k 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 ...
3
votes
1answer
2k views

Foreign key of multiple tables

I have the following 3 tables: ╔═════════════════╗ ║ Companies ║ ╠═════════════════╣ ║ id ║ ║ name ║ ║ type ║ ║ etc... ║ ╚═════════════════╝ ...
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
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 ...
1
vote
1answer
107 views

Can I place the attribute in the Entity, when the foreign key is also already present?

I am using DBdesigner Fork for generating ER diagram. I have the 'Company' as an entity for which can I place the attribute of 'idShares', despite of having the foreign key of Shares Entity in the ...
0
votes
2answers
6k views

PostgreSQL - insert/update violates foreign key constraints

I am a new one in postgreSQL. I have 3 tables, one table references the other 2 table's primary keys. But I couldn't able to insert data into the Table3. See the code below : DROP TABLE Table1 ...
0
votes
2answers
65 views

View for a MANY_MANY_MANY relationship

Let's say I have 3 main tables: category, tag, url category and tag have a MANY_MANY relationship (category_tag). tag and url have a MANY_MANY relationship (url_tag). I would like for a given ...
1
vote
3answers
382 views

How to model a 1..1 Composition Relationship

We have a couple of entities/relationships of the following kind: Entity Event has a TimePoint The TimePoint contains time information about the event. It belongs to that event and cannot be used by ...
3
votes
2answers
3k 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
521 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 ...
1
vote
1answer
177 views

Pitfalls of creating a foreign key constraint on a system table?

I have a number of tables which hold metadata about the tables and packages in the system. This is intended to be a resource for analysts and documentation so it is not used to create data. One of ...
0
votes
1answer
107 views

Is it acceptable to have a surrogate/primary key of one table be the primary/foreign key of another table?

For my purposes I'm trying to apply this to Microsoft SQL, but would like to know if there are restrictions for other DBMS regarding this. I don't know how to represent UML as ascii characters so if ...
3
votes
1answer
100 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
1answer
413 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 ...
0
votes
1answer
132 views

Handling error of foreign key

How to set a FK value NULL upon receiving error for foreign key constraint? Consider a simple table a CREATE TABLE test ( id int(11) NOT NULL AUTO_INCREMENT ex_key int(11), FOREIGN ...
1
vote
0answers
43 views

Classes and objects (but not ORD)

I am looking for help laying out a database that stores information similar to a class definition and then also instances of those 'classes' (but not referring to an ORD). The specific example is ...
1
vote
2answers
3k 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
146 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 ...
3
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
159 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
341 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
123 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
855 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
1k 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 ...