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.
2
votes
2answers
170 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 ...
2
votes
1answer
76 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
2answers
46 views
Auto-increment key and Foreign Key as composite for relational database
I do not have any experience in databases other than what I have from just playing with workbench and trying to understand how I need to setup this database for a project. I have been searching the ...
0
votes
0answers
56 views
Database associative table
I have a coworker who's insisting on using associative tables to create nullable associations. For example, we have this table :
cm.accounts
----------
id:int (PK, identity)
...
sm.services
...
1
vote
2answers
98 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 ...
0
votes
1answer
86 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 ...
0
votes
1answer
33 views
Foreign key with multiple references
I have the following three tables in mysql database named "THE_COLLEGE"
mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | ...
0
votes
1answer
17 views
HOW to “SUM” and “JOIN” for single query of a poll results?
I have a poll database as
CREATE TABLE poll_answer
answer_id,
answer varchar(255),
poll_id int(11),
FOREIGN KEY(poll_id) REFERENCES polls(poll_id) ON DELETE CASCADE,
PRIMARY KEY(answer_id)
) ...
0
votes
1answer
29 views
MySQL cannot create foreign key, errors 1005, 150
I'm trying to add a foreign key to a MySQL InnoDB table, linking a BIGINT with a table within the same database. I'm using MySQL Workbench to do this. It generates a script:
ALTER TABLE ...
0
votes
1answer
54 views
Is it possible to force drop of mysql tables with FK?
For dropping a table in which a Foreign Key is used in other tables, it is necessary to drop all other tables having FK connection with the corresponding table.
Is there a short way to drop a table, ...
2
votes
2answers
61 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: ...
0
votes
2answers
38 views
Usage of foreign key when the referenced table data will not change
I have to design a table where user has 10 fields and 6 are dropdown. Some are single selections and some multiple. Dropdown data is supposed to change very rarely.
For e.g., the user is presented ...
3
votes
1answer
198 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
46 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 ...
0
votes
1answer
17 views
Table Design for user-specific and user-agnostic criteria
I want to design a table that is general enough to accommodate an increasing number of achievements as I come up with new accomplishments to reward. Initially, I thought to design the table as ...
1
vote
0answers
31 views
A separate table or ENUM? [duplicate]
There is a table person (which has among autoincremented primary key field id such fields as firtsname and lastname).
There is a table employee whose primary (but not autoincremented) personid is a ...
3
votes
1answer
70 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 ...
1
vote
1answer
35 views
Foreign Key to Multicolumn Primary Key?
I have three connected table in my design. Basic logic is when a category deleted, also delete records in category_product table which has deleted category's id in its cat_id column. But when I tried ...
1
vote
1answer
68 views
Table for optional parent/child relationship
Assuming we have the following table: Item, Parent, Child and Parent is the parent of child.
The item can either belong to a parent or child and not both.
I have other tables that are similar to ...
0
votes
1answer
53 views
best relationship for this design?
There is a table users with autoincrement primary key id.
There is also a table employees (every employee is a user but not vice versa). The employees primary key is userid. The field userid is not ...
0
votes
1answer
19 views
Which of tables key to reference?
MySQL.
There is a table users with autoincrement primary key id.
There is also a table employees (every employee is a user but not vice versa). The employees primary key is userid. The field userid ...
2
votes
1answer
50 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 ...
1
vote
3answers
55 views
What is the benefit of two FK, when one of them can be deduced from another table?
The third party Microsoft SQL Server database I'm working on is using the structure which can be illustrated with this example:
Let's take three tables:
Shop which is the topmost level and ...
0
votes
1answer
519 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 ...
1
vote
1answer
36 views
Getting the data from a foreign key table
I have a table that relates a user to client using both their table id's
UsersClients Table
---------------------
| userid | clientid |
---------------------
| 2 | 32 |
| 3 ...
0
votes
1answer
42 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 ...
1
vote
2answers
67 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 ...
4
votes
2answers
108 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 ...
0
votes
1answer
70 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
134 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
208 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 ...
0
votes
0answers
100 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
53 views
Retrieving all PK and FK with pgadmin
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?
0
votes
2answers
73 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 ...
6
votes
3answers
305 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 ...
0
votes
1answer
34 views
Refer to a table with all rows or to the table which restricts allowed rows?
There are some discounts:
CREATE TABLE `discounts` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`value` decimal(4,2) NOT NULL,
`titleru` text COLLATE utf8_bin NOT NULL,
`titlehe` text ...
2
votes
2answers
234 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 ...
5
votes
2answers
745 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 ...
2
votes
1answer
80 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 ...
1
vote
1answer
145 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 ...
1
vote
1answer
102 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:
...
2
votes
1answer
378 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
32 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".
3
votes
1answer
150 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
102 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
315 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 ...
0
votes
1answer
515 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 ...
2
votes
1answer
290 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.
...
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 ...
6
votes
1answer
100 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 ...
