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
94 views

Foreign key for rows added later

I'm using a queue to crawl pages and track links between them. I have a table called page with a primary key called page_id. I have a table called links with two columns: link_from, link_to The ...
0
votes
0answers
111 views

Need advice on this EER Diagram

This db will work with a CakePhp app and need advice about design. Is it good idea to put orders on center or should I put clients on center? Clients has orders and an order record can only have one ...
0
votes
1answer
411 views

Can a foreign key have a default value equal to 0?

I have a column whose default value is 0 .Can I make that column as foreign_key
0
votes
2answers
58 views

Guaranteeing a FK relationship through multiple tables

I'm using MySQL / InnoDB, and using foreign keys to preserve relationships across tables. In the following scenaro (depicted below), a 'manager' is associated with a 'recordLabel', and an 'artist' is ...
0
votes
2answers
3k views

Delete rows in parent table based on criteria in child table - Oracle

I have a table B with a foreign key to table A. I want to DELETE some rows in table B, and I also want to DELETE their parent rows in table A. However, the delete criteria is based on table B. The ...
1
vote
1answer
859 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: ...
4
votes
1answer
986 views

How to relate two rows in the same table

I have a table where the rows can be related to each other, and logically, the relationship goes both ways (basically, is directionless) between the two rows. (And if you're wondering, yes, this ...
1
vote
0answers
228 views

How can I look up table relationships in SQL if I don't have physical/logical models? [closed]

What is the best way to look up/learn table relationships in a database that I'm not familiar with and I don’t have any Logical/Physical models available? Is there a tool that I can use? I’ve been ...
2
votes
1answer
90 views

Is it necessary to drop foreign keys before adding a null column to a table and then re-add them?

I have generated some SQL Server(2008) upgrade scripts using SqlCompare. The primary objective is to add a null column. However, the generated script wants to drop foreign keys in a couple of other ...
1
vote
1answer
6k 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
184 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
4k views

Converting ER diagram to tables and relationships in sql

Department : Branch is 1 : M Branch : Course is 1 : M Branch : Student is 1 : M Branch : Applicant is M : N Applicant : Student is 1 : 1 These are the conditions prescribed in the ER diagram. ...
1
vote
1answer
3k views

Removing foreign key when it is not named

I've created a table named 'student' as : create table student( stud_id number primary key, stud_name varchar2(30) not null, branch_id varchar2(4) references branch(branch_id), course_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
2k views

Cannot create FOREIGN KEY for a table [closed]

I'm not a professional DBA, I encountered a problem when I's attempting to create FOREIGN KEY for a specific table by the following SQL script (I'm using MySQL): ALTER TABLE mmy_answer ADD CONSTRAINT ...
1
vote
1answer
441 views

Multiple foreign keys with shared columns for weak entities

this is my first question at stackexchange. I have a parent table (P) with two child (CC and CD): CREATE TABLE P ( A CHAR(3), B CHAR(2), CONSTRAINT PK_P PRIMARY KEY(A, B) ) CREATE TABLE CC ...
0
votes
1answer
111 views

Foreign key in MySQL

I am learning about database, I need to know something about the foreign key in MySQL. Consider the following two tables. UserGroupType. UserGroup. UserGroupType: UserGroup: In this table ...
0
votes
1answer
710 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 ...
-2
votes
1answer
226 views

How to represent it with UNIQUE indexes and foreign keys?

"Group" is a group of pupils. "Topic" is a topic of lessons. To every group corresponds exactly one topic. (So there may be multiple groups per topic.) To a pupil corresponds multiple groups, but ...
2
votes
1answer
2k 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 ...
13
votes
1answer
6k views

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

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 ...
3
votes
1answer
3k 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 ...
4
votes
2answers
3k 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
1answer
1k 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) ) ...
2
votes
1answer
2k 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
312 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: ...
4
votes
1answer
2k 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 ...
3
votes
1answer
3k 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
50 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
36 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 ...
5
votes
1answer
160 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
1k 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
462 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
74 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
26 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
113 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
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
2k 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
931 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
369 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 ...
2
votes
1answer
774 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
1answer
2k 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
0answers
750 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 ...
7
votes
3answers
905 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 ...
4
votes
2answers
391 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 ...
8
votes
2answers
5k 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 ...
3
votes
1answer
371 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
840 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
188 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: ...
3
votes
1answer
1k 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; ...