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
2answers
303 views

multivalued weak key in ER database modeling

I was wondering since i didnt find out any clarification for this. I want to store movies that exist in different formats (dvd, bluray etc) and the price for each format differs from each other as ...
1
vote
2answers
27 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. ...
0
votes
2answers
143 views

Usage of foreign key when the referenced table data will not change

I have to design a table where user has 10 fields and 6 are dropdown. Some are single selections and some multiple. Dropdown data is supposed to change very rarely. For e.g., the user is presented ...
2
votes
1answer
36 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
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
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 ...
0
votes
1answer
81 views

Circular foreign key deferrable cascade behavior

I am trying to determine what happens for a particular circular foreign key that will cascade delete. Please feel free to address any other possibilities that my inexperience has not listed. On my ...
0
votes
1answer
147 views

How to add FK on a column of composite type in PostgreSQL?

I have a table which contains a composite type and I am trying to connect a column of it to another table's PK. CREATE TABLE aaa (col1 SERIAL PRIMARY KEY); CREATE TYPE bbb AS (col2 INTEGER); CREATE ...
1
vote
0answers
78 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
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
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
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
0answers
12 views

Analyze a Database to Find Failed Foreign Key Constraints

I aggressively deleted a ton of data after using this query: SET FOREIGN_KEY_CHECKS = 0; Is there any easy way I can analyze the entire database to check to see if I have any hanging records that ...
0
votes
0answers
22 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
0answers
33 views

Table Field to Reference Field in Other Tables

Suppose I want to map a set of input values to database table fields E.g. employeeFirstName --> emp.fName employeeLastName --> emp.lName ... --> ... In theory I can create a ...
0
votes
0answers
45 views

Error adding constraint foreign key to bridge table

Hi there I am fairly new to sql development. I am current following the head first into mysql book and is on chapter 6. On chapter 6 there is a table in third normal form but it does not contains the ...
0
votes
0answers
65 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
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 ...