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

1
vote
1answer
134 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
453 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
388 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
121 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
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
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
222 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
225 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
1answer
291 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
2k 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
4k 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
1answer
64 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
2answers
100 views

How to normalize data en-masse?

Goal: Extract address fields from a users table into a separate addresses table linked by foreign key. I have created the structure below, and have millions of rows to deal with. I need to do this ...
1
vote
1answer
980 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
312 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 ...
1
vote
1answer
143 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 ...
1
vote
1answer
229 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
128 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
97 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
241 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
1answer
29 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 ...
1
vote
1answer
59 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 ...
1
vote
2answers
31 views

Altering table Foreign Keys produces an error (errno:150) (MySQL)

This is the first database I set up so I apologize if my problem is something obvious. I'm playing around with my table design and tried to create some link tables with FKs to avoid redundant data. ...
1
vote
0answers
79 views

MATCH FULL vs MATCH SIMPLE

I've noticed a MATCH SIMPLE or MATCH FULL option in phpPgAdmin, but I can't find a description in the docs. How do they function? The default is set to MATCH SIMPLE.
1
vote
0answers
40 views

join performance, index vs. index+foreignkey

I'm using an orm framework that handles data (and its integrity) on top of mysql. So far I ignored foreign keys because the orm handles that just fine. I recently read that foreign keys can enhance ...
1
vote
1answer
61 views

Proper implementation of child (derived) table PK/FK during single and multiple inheritance hard coded generalization physical data model

Below is an image of hard coded generalization UML logical model of entities. I'd like to implement these classes as tables in a physical data model. Two derived classes/entities (Class2 and ...
1
vote
0answers
127 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 ...
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 ...
1
vote
0answers
341 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 ...
1
vote
1answer
153 views

Pitfalls of creating a foreign key constraint on a system table?

I have a number of tables which hold metadata about the tables and packages in the system. This is intended to be a resource for analysts and documentation so it is not used to create data. One of ...
1
vote
0answers
43 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
141 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
113 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
87 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
127 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
4k 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
83 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) ) ...
0
votes
2answers
510 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 ...
0
votes
2answers
264 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
64 views

Primary key for link/junction table with time-dependent data

I want to link two tables in such a way that for any given time period the relationship is one-to-one, but over time the relationship can change. As an minimal example, suppose I want to list the ...
0
votes
1answer
30 views

Multiple source CASCADE DELETE behavior

I'm having trouble understanding Postgres's CASCADE DELETE behavior. If table_A REFERENCES table_B, and the column referenced could have multiple instances of the same value, will the table_A row be ...
0
votes
1answer
65 views

Implementing foreign keys on generalized data model

Suppose the ASCII tree below is a UML generalized concept model. To represent these entities/classes in UML, lets assume [mammal] a super class, and the level 1 classes, [wild] and [domestic] are sub ...
0
votes
1answer
131 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
41 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). ...
0
votes
1answer
88 views

MYSQL - Problem with foreign keys on same table

In Mysql 5.5, I have a table, in which I store some informations about "events".. In order to have a chronological order, I made two columns that reference to the previous and to the next event. So I ...
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
1answer
263 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
476 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, ...
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 ...
0
votes
1answer
516 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 ...