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

0
votes
2answers
193 views

What's the use of assigning foreign keys on MySQL [duplicate]

Aside from indexing the value of the foreign key, what is/are the advantage of assigning foreign keys to databases? I've seen a lot of databases that does not assign foreign keys, but is relational by ...
0
votes
0answers
105 views

Same column relational Id to multiple tables

Is it appropriate to use a column as a relational id to multiple tables? For example: referenceId - int relational id to various tables tableReference - String defining the table This way I can ...
2
votes
1answer
585 views

size of a primary key and foreign key

Do a primary key and a foreign key referencing it must have the same column size in sql server 2008 ? could someone indicate me a link to a documentation explaining this ? Thank you for help.
2
votes
2answers
202 views

Should I drop a foreign key if I use a soft delete?

"ON UPDATE" scenario never happens in my application. As soft delete is used, "ON DELETE" cascade and restrict scenario never happen as well now. Then, should I just drop foreign key because I have ...
1
vote
1answer
352 views

MySQL InnoDB table accepts Non-Existent Foreign Key

Using EasyPHP 14.1 VC11 and phpMyAdmin (4.1.4) to create a website project in my home computer. The MySQL version is 5.6.15 according to the EasyPHP administration page. Working on Windows 7 64-bit. ...
3
votes
1answer
113 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 ...
4
votes
2answers
319 views

How to write a query which finds all circular references when a table references itself? (II)

I have the following schema (names changed), which I cannot change: CREATE TABLE MyTable ( Id INT NOT NULL PRIMARY KEY, ParentId INT NOT NULL ); ALTER TABLE MyTable ADD FOREIGN KEY ...
1
vote
1answer
60 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` ...
0
votes
0answers
46 views

Problems with foreign keys in a mysql database

I am trying to link both these tables with a foreign key. But I keep getting error message "ERROR 1452: Cannot add or update a child row: a foreign key constraint fails " the foreign key is pwalk_id ...
1
vote
1answer
27 views

Determining Homogeneity by Common Foreign Keys

I manage a MySQL database of ten or so tables. In higher code, I have a list of primary keys belonging to entries of some table A. These A entries have foreign keys to entries in another table B. The ...
2
votes
1answer
82 views

Conditionally join table

I'm new to database design and have designed a database that seems fine for the application but looks like it has a major red flag to me. I have a master table which is then joined to another table ...
2
votes
1answer
98 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 ...
1
vote
1answer
181 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 ...
0
votes
1answer
42 views

Move Commonly-Repeated Fields into a new Table

I'm still at the design stage for a new system, and I've noticed there are several places where different types of object share similar fields. In this case, we have customers, offices, warehouses ...
0
votes
1answer
87 views

Batch converting indexes to keys [closed]

I have a legacy MS SQL Server database where all the primary and foreign keys were created as indexes. I am developing the new version of our software with Entity Frameworks, which I have learned ...
0
votes
1answer
38 views

Database relationship refresher

All, After several years of programming applications, I'm looking to refresh my understanding of databases. What I knew to begin with was fairly basic, and honestly, it's disappeared over time. I ...
0
votes
1answer
56 views

Query executes fast, but keeps the table locked for some time

Executing the following query is very fast (75ms), but removing the comment from the last row it becomes slow (2.5s), and the time increases exponentially with the number of rows in the table. It ...
0
votes
0answers
40 views

Redesign tables, multiple foreign key and related table to same table

Hello I have this database: Table Teams is used in each one table. Match table contains teamA and teamB and round contains team that win the SET and team that start the set. But I dont know how I ...
0
votes
1answer
45 views

How to relate to information_schema

Is it possible to create a foreign key that relates to information_schema? For an application implementation, I would like to store extra column information in another table, but I want to ensure ...
2
votes
0answers
129 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 | ... | ...
2
votes
1answer
3k 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
2k views

MySQL Foreign Key Prevents Deleting Table — But Table is Empty

Why would dropping an empty table produce a foreign key error? In MySQL, I have a table with the following definition CREATE TABLE `enterprise_rma` ( `entity_id` int(10) unsigned NOT NULL ...
1
vote
1answer
132 views

More Details for MySQL's errorno:150

When MySQL can't create a table due to a foreign key constraint problem, it returns the following error Can't create table 'dbname.table_name' (errno: 150) Is there anyway to have MySQL tell you ...
0
votes
1answer
3k views

Cannot add foreign key constraint while creating table

My Error Code is 1215 which finds problem with the foreign key constraint. I know all fields are the same data types. I can't see where the problem is. Any help would be much appreciated! Thanks! ...
1
vote
2answers
7k 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
3k views

How to insert data into tables which reference each other?(MySQL)

I'm a student and right now I have a problem that I couldn't solve by myself(after searching on the Internet). I'm not going to give my «homework» because I want to learn what I'm doing but I will ...
1
vote
1answer
854 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 ...
0
votes
0answers
28 views

Approaching multiple values in same column

I'm developing an Android-application that scrapes the schedule for my university and presents it nicely since the current system is a nightmare. I'm processing a lot of data and it gets down to ...
0
votes
0answers
3k views

getting SQL Error (1452): Cannot add or update a child row: a foreign key constraint fails (Unknown error code)

I am getting error with MySQL NDB Cluster 7.3.5 as described below: I have two tables: -User Profiles -User Services User services is a child table which contains foreign key constraint ...
0
votes
1answer
147 views

Foreign key double inheritance SQLITE

I'm designing a domotic system database and I'm trying to figure out how to represent two foreign keys. This is the cardinality I want. Consider 1...N Areas (rooms, halls, kitchens, etc.): Area 1 - ...
0
votes
1answer
61 views

Save dataset within another table best practice

I want to save one dataset within another table, this dataset should be persistent. For example Table1: FIELDS (A,B,C) Table2: FIELDS (D, DuplicateTable1) I can think of 2 possiblities: Have ...
0
votes
0answers
114 views

Spotting foreign key relations in a database that doesn't define all relationships as constraints?

At work I use a rather old MS Access database which defines some relations between tables, but does not outright define all of them. If the database application used an ORM, I would think that this ...
1
vote
0answers
597 views

Crow Feet notation and identifying -vs- non-identifying relationships

Is it possible to distinguish between identifying and non-identifying relationships using the Crow Feet notation? Case in point: In table Book, part of the book's Primary Key is a FK to the Author ...
0
votes
1answer
65 views

Why use foreign keys if cascade action is not need?

I'm testing some queries with MySQL and tried to create two tables were there should be a reference between IDs. Something like: CREATE TABLE user ( id int(11) UNSIGNED AUTO_INCREMENT, name ...
0
votes
1answer
133 views

Implementing a simple ERD in MySQL: is the implementation below 100% compliant with the ERD?

ERD Consider the following ERD: Tables From it, I derived the following tables: Company ------------------ c_id c_name ------------------ CompanyEmail ------------------ c_id ...
1
vote
1answer
123 views

How to design to avoid two-way reference between two tables [duplicate]

I am having a problem with foreign keys between two tables. I am trying to model a relationship between members of a club and their relatives. This is a straightforward many-to-one relationship, with ...
1
vote
3answers
526 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
279 views

Reasons for db2 foreign key error (-667) despite all rows appearing sane?

I have some csv formatted table dumps and loaded those into a new database with the tables defined by a DDL script from the source database (both acquired by somebody else so I cannot have a look at ...
2
votes
1answer
94 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 ...
1
vote
1answer
433 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 ...
0
votes
1answer
37 views

Insert / Edit Rows by Selecting FK row

When inserting or editing rows ("Edit Top 200 Rows") for a table with a FK constraint, can the editor be configured to select the foreign row, rather than me having to look up the FK?
3
votes
1answer
3k views

How can I create a Foreign Key to a Compound Primary Key with a single column?

I am working with a 'lookup' style table that is already in place, which has a compound primary key (not my design) in Microsoft SQL Server 2012. I want to add a foreign key to it in another table, ...
2
votes
1answer
905 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
2answers
90 views

EAV-like table with both primary column pointing to the same foreign column

Background: I'm a programmer who, while had to work with simple databases several times, never had to work with complex ones. I.e. I'm proficient with basic stuff, but clueless about advanced features ...
2
votes
1answer
8k views

How to solve the error Msg 547, Level 16, State 0, Line 1 [closed]

Please suggest me how to solve the error How to solve the error Msg 547, Level 16, State 0, Line 1 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "MyForeignKeyName". The ...
0
votes
1answer
2k views

Create table shows ERROR 1005(HY000) errno 150

i tried to create tables as per given in my textbook in MySQL server as follows: create table department (dept_name varchar(20), building varchar(15), budget numeric(12,2), ...
2
votes
2answers
311 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 ...
0
votes
0answers
135 views

Why is adding a FK constraint slow in MySQL?

I'm doing some experimentation in MySQL 5.1.53 and I've run across a case that I just don't understand. CREATE TABLE A ( `pk` INT(11) NOT NULL, `fk` INT(11) NOT NULL, PRIMARY KEY `pk_A` ...
0
votes
1answer
162 views

CentOS and Ubuntu differences with information_schema

I have a Vagrant VM running CentOS 6.5. My localhost is Ubuntu 12.04, both running MySQL. I have the exact same database on both (structured exactly the same, different testing seed data in both). ...
3
votes
2answers
2k 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 ...