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
1answer
238 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
...
2
votes
1answer
69 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 ...
2
votes
1answer
153 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 ...
2
votes
1answer
319 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
359 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; ...
2
votes
2answers
232 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 ...
2
votes
1answer
555 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
131 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 ...
2
votes
2answers
152 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 ...
1
vote
3answers
78 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 ...
1
vote
3answers
155 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 ...
1
vote
2answers
433 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
vote
1answer
326 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 ...
1
vote
1answer
56 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 ...
1
vote
3answers
579 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 ...
1
vote
3answers
48 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 ...
1
vote
1answer
98 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
916 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 ...
1
vote
1answer
327 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
1answer
215 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 ...
1
vote
3answers
116 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)
...
1
vote
1answer
17 views
MySQL 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 ...
1
vote
1answer
29 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
33 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 ...
1
vote
1answer
129 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
2answers
1k 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 ...
1
vote
2answers
2k 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
2answers
65 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
1answer
65 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 ...
1
vote
1answer
214 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 ...
1
vote
0answers
30 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 ...
1
vote
2answers
84 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 ...
1
vote
0answers
39 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
1answer
104 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
2answers
79 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 ...
1
vote
0answers
84 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 ...
0
votes
1answer
65 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 ...
0
votes
2answers
145 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 ...
0
votes
2answers
1k 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
1answer
439 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 ...
0
votes
1answer
122 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
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 ...
0
votes
1answer
173 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
2answers
72 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
51 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
30 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 ...
0
votes
1answer
49 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
1answer
25 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".
0
votes
1answer
324 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 ...
0
votes
1answer
83 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 ...
