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

2
votes
1answer
1k views

Column name or number of supplied values does not match table definition error

I have a table, with two foreign keys defined on two columns of that table. The problem is that, when I insert the values from SQL Server, I get an error: Column name or number of supplied values ...
2
votes
1answer
2k views

Error Creating Foreign Key in Oracle

I have 2 tables: PRODUCTS PREFIX SUFFIX ATTR1 ATTR2 ----------------------------------------------------- 001 AAA 50 100 001 BBB 20 300 010 ...
2
votes
1answer
474 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
277 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
56 views

Efficient manual cleanup of foreign keys in MySQL [closed]

I have an all-InnoDB database in MySQL (Percona) 5.6 that was previously using FK constraints. I had to give up on them as I kept running into locking issues, so I removed the constraints and added ...
2
votes
1answer
70 views

Foreign key, key split in two tables

I have to record a list of events made of action, reason (optional, may be multiple), user. I have a master table with all the valid action, and another with all the valid action-reason combination. ...
2
votes
1answer
65 views

Can triggers be used to implement automated polymorphic association?

(Apologies for any bad formatting or ignorance of common practices within this community; this is my first question here.) I'm attempting to (re)design a MySQL database for a personal social ...
2
votes
1answer
87 views

Relational modeled design to store versioned files

The scenario I have images in a database table and some of these need to be altered then re-saved(not overwriting the original) whilst providing a reference back to the original. I also save the ...
2
votes
2answers
234 views

How to establish a Two-to-One table relationship (PK-FK-CK)?

My question is about DB design and its principles. I am working on a Python application where objects are mapped to database tables. I have Projects and People objects, mapped to their respective ...
2
votes
3answers
414 views

Many to many link tables with history? [closed]

While creating a PostgreSQL database of customer subscribed services with DNS domains, I want to: enable customers to subscribe to multiple services each service can have multiple domain names per ...
2
votes
2answers
1k views

Partitioning a Table Referenced by a Foreign Key

I have one table: CREATE TABLE [dbo].[entry] ( [ID] [bigint] IDENTITY(1,1) NOT NULL, [EntryDate] [datetime] NOT NULL, [createddate] [datetime] NOT NULL, CONSTRAINT [PK_Entry_ID] ...
2
votes
2answers
1k views

Two primary keys to one foreign key

racing_couple ID (PRIMARY) breeding_couple ID (PRIMARY) egg IDpar(FOREIGN KEY) I have 2 IDs from 2 different tables (racing_couple and breeding_couple) and they are primary keys. In 3. table (egg) ...
2
votes
1answer
1k views

prevent MySQL Workbench to add index for foreignkey constraint when it is first part of primary

I know for each foreign key we should make an index on its column, But it is not necessary when that column in first part of PRIMARY index. MySQL accepts this query: CREATE TABLE IF NOT EXISTS ...
2
votes
1answer
843 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
676 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
139 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
0answers
68 views

Storing URLs in a database

I am thinking about storing URL's in my database, but I am not sure whether the way I use is an efficient way to do things or not. Table name : url url_id (Primary Key & Auto Increment) url_value ...
2
votes
0answers
178 views

AFTER DELETE trigger will not fire during a cascade delete

I've got two tables, corresponding to Nodes in a graph and Aisles to which they may belong. In addition, each Aisle has a Node at which it starts and ends. Aisle Table: Aisle char(4) Start_Node ...
2
votes
0answers
29 views

See indices implicitly created for foreign key constaints in MySQL

When I add a foreign key constraint to my table, SHOW INDEX doesn't show any new indices, but the overall space used by the indices in that table increases. Apparently, the index for the field I put a ...
2
votes
0answers
98 views

Is it recommended to have redundant foreign key columns?

Imagine I have the models A, B, C e D, where A have many Bs, B have many Cs and C have manyDs: Without redundance A | id | ... | ------------ | ... | ... | B | id | a_id | ... | ...
1
vote
3answers
91 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
533 views

Normalization: 'zip code' as a primary key

I've seen frequently that you should not have all address fields (zip, street, city, state, country, etc) in the same table as your user info table (pk_id, name, email... ). This would be in ...
1
vote
4answers
8k views

Recursive self joins

I have a comments table, which can be simplified down to this: comments ======= id user_id text parent_id where parent_id is nullable, but might be a key for its parent comment. Now, how can I ...
1
vote
3answers
398 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
3k 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
3answers
1k views

Replication fails because of a non-existent foreign key

I have been trying to get a previously working replication to start working. I have tried many settings, but I can't seem to make it work. Here is the scenario. I dropped all the FK constraints ...
1
vote
1answer
541 views

Slow delete caused by many foreign keys

My DB has about 90 tables. Most of the tables have an UpdatedBy and AddedBy column that have foreign keys pointing back to the user table. With even moderate amounts of data spread throughout the ...
1
vote
1answer
379 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
467 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 ...
1
vote
2answers
1k views

Difference between On Delete Cascade & On Update Cascade in mysql

I have two tables in MySQL database- parent, child. I'm trying to add foreign key references to my child table based on the parent table. Is there any significant difference between ON UPDATE CASCADE ...
1
vote
1answer
49 views

Understanding the output of \d <table> in psql

Say I run the following command: \d mtb.big_table I get the output shown below. My questions are: What are column modifiers? (what does default now() mean?) Under Indexes, Why do some indices ...
1
vote
3answers
72 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
166 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
2answers
64 views

Insert New Foreign Key Row for new Strings

Lets say I have a table new_data like this: ndx | someval | somemachine serial | integer | integer ---------------------------- And let's say I have a reference table new_data_someval_ref: ...
1
vote
1answer
56 views

What problem prevents well defined behavior of a foreign key on a non-unique column?

In my noob opinion, it strikes me that in a serialized environment, this shouldn't be a problem. Is that correct? If not, please explain to me why a foreign key's behavior on a non-unique column is ...
1
vote
1answer
67 views

Better way to manage data integrity, without having too many composite FK's

I have following tables: class_table (class_id = Auto Incremented Primary Key (AIPK) of this table) user_table (class_id, user_id (AIPK of this table)) course_table(class_id, course_id (AIPK of this ...
1
vote
1answer
667 views

MySQL: update instead of delete if foreign key constraint?

I have a bit of a strange question. I know about insert on duplicate key update. My question is, is there something similar for deletes that fail because of foreign key constraints? For example: ...
1
vote
1answer
143 views

How to link a customer to a country when the city/state isn't known

We have a situation where medical systems are sold to hospital groups, but at the time of the order, we don't always know into which state and city the system will be installed. We always know the ...
1
vote
1answer
761 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
714 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
125 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
30 views

is there some way to give me the order in which inserts should be made so all FK constraint are met? [closed]

I have a mysql 5.6 schema with about 60 tables. I need to write some code (not sure if it will be Sql or java) to populate the data. The schema has lots of FK etc. Is there some good way (I am ...
1
vote
1answer
71 views

“Transitive” keys

I have a central table, A, which will have a key that I'll call A_ID. Now, I have a table B in a "one to many" relationship with A (many B's map to one A). Each link will, of course, have a link ...
1
vote
1answer
54 views

Schema design - Reference slightly different data in different tables

I'm trying to design a schema for a MySQL database and got stuck on the following. My database stores documents which should have relations with blocks of content. create table document ( `id` ...
1
vote
1answer
143 views

Performance of checking for data in related tables

NB: this question is purely academic / to help improve my understanding of SQL Server performance. Given a master table which relates to one or more other tables, how would you determine the best ...
1
vote
1answer
254 views

How do I validate all my constraints in postgresql?

It seems like I have been bitten by the first bug described in the release notes of postgresql 9.3.4: http://www.postgresql.org/docs/devel/static/release-9-3-4.html I now have e.g. duplicate primary ...
1
vote
1answer
5k views

“Cannot add or update a child row” when deleting a record?

I have the two following tables: survey_main ---------- id name answers_main -------------- id survey_id I have the following foreign key constraint on the answers_main table: CONSTRAINT ...
1
vote
1answer
611 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
1k 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
2answers
258 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 ...