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
1answer
85 views

Get unmatched rows for foreign key

Consider two tables: CREATE TABLE Users( id INT PRIMARY KEY, name VARCHAR(128) ) Engine=InnoDB CREATE TABLE Tags( id INT PRIMARY KEY, user REFERENCES Users(id), tag VARCHAR(128) ) ...
2
votes
1answer
441 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] ...
0
votes
2answers
46 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
66 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
76 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
49 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
518 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
259 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
208 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 ...
0
votes
1answer
244 views

Mysql foreign key cascade delete

Does mysql have a way to delete all sub links if a row was deleted from a "parent" table? Example table setup is below, the issue I am trying to avoid is having to maintain a script that deletes the ...
1
vote
0answers
130 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
67 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
2k 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
117 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
991 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
321 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
419 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
294 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
145 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 ...
4
votes
3answers
8k views

How do I insert a row which contains a foreign key?

Using PostgreSQL v9.1. I have the following tables: CREATE TABLE foo ( id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY, type VARCHAR(60) NOT NULL UNIQUE ); CREATE TABLE bar ( id BIGSERIAL ...
0
votes
1answer
46 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 ...
1
vote
4answers
2k 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 ...
0
votes
1answer
175 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
143 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
546 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 ...
12
votes
1answer
1k 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 ...
1
vote
2answers
3k views

Good explanation of cascade (ON DELETE/UPDATE) behavior

I don't design schemas everyday, but when I do, I try to setup cascade updates/deletes correctly to make administration easier. I understand how cascades work, but I can never remember which table is ...
2
votes
1answer
728 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 ...
3
votes
2answers
653 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
113 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 ...
0
votes
1answer
277 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
264 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
482 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
157 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: ...
3
votes
1answer
660 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 ...
2
votes
1answer
1k 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
36 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
34 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
101 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
98 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
168 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
63 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
22 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
90 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
67 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

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
226 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
233 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
129 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 ...
5
votes
2answers
585 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 ...