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
604 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
3k 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
1answer
45 views

PostgreSQL: Foreign key on string column

I'm currently in a process of redesigning a legacy database. One of the tables primary key is a varchar(254). I need to create a table relating to it. So for example: create table "Item" ( ...
1
vote
2answers
62 views

Mysql might have too many indexes

I am using the php framework Laravel for my application, together with mysql 5.6, in order to have full-text search with innoBD. But I am not sure if I have too many indexes. My application allows ...
1
vote
1answer
55 views

Transactional Replication - Replication Order of Related Tables

** This was previously asked on Stackoverflow - I removed it from there and recreated here ** I have an issue with SQL Server Transactional Replication. My topology is as follows (and this cannot be ...
1
vote
1answer
61 views

How to insert values into a junction table?

I add values into my two tables using the code below, but I also have a junction table called book_main_cover and I want to add data into this table to make a connection between book_id and ...
1
vote
1answer
19 views

Relation between tables [closed]

This question may be too easy to ask, but I can't understand how to do it. I am new in SQL. I have two tables. Table I book_id (AI & Primary Key) book_name book_year Table ...
1
vote
1answer
70 views

Foreign Keys to tables where primary key is not clustered index

I'm focusing on SQL Server here, but I think this is a database agnostic question, really. I have a table whereby a Guid is logically the primary key. However, uniqueidentifiers apparently make ...
1
vote
1answer
34 views

Help on MySQL joins

I'm building a system to monitor currency prices. Part of my database structure looks like this: My system is trying to pull the api ticker for each exchange with this query: SELECT distinct ...
1
vote
2answers
42 views

Suggestion for Tooth Chart Data Structure

Hello I'm not sure if this is an OK question here but here goes, I'm quite confused on how am I going to make the data structure of my tooth chart database. I had first this thought of making a data ...
1
vote
1answer
1k views

Composite primary key from multiple tables / multiple foreign keys

I want to build a composite primary key made up of foreign keys from two different tables. I'm still pretty green to this... A great answer was given to building a composite key from multiple foreign ...
1
vote
1answer
236 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. ...
1
vote
1answer
25 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 ...
1
vote
1answer
122 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 ...
1
vote
1answer
1k 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
100 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
2answers
183 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
5k 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
1answer
146 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
1answer
2k views

Allow self-referential foreign keys to be null from an alter-table

I am trying to establish a hierarchical relationship between rows in an existing table. There are two fields of interest: a not-nullable primary key field id, and a second nullable field parentId. ...
1
vote
2answers
146 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
3k 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
2k 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
332 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
2answers
174 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 ...
1
vote
2answers
971 views

Foreign key with multiple references

I have the following three tables in mysql database named "THE_COLLEGE" mysql> desc students; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | ...
1
vote
1answer
646 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
305 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
116 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
2answers
5k 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
261 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
0answers
28 views

Implementing a consistent mailbox

I am trying to implement something like a mailbox for short messages between users. I am using Postgresql 9.4.1 with SQLAlchemy (Python3). There is a table of users: users = Table('users',metadata, ...
1
vote
0answers
26 views

MySQL - Cannot add new Foreign Key

I'm using magento and it does some re-index for a product flat table structure. In this process a new table is created. The SQL for that table is something like: CREATE TABLE ...
1
vote
1answer
42 views

How to write a query which recursively returns all foreign key references to a column?

I need to obtain a list of foreign keys which refer to a given column, as well as all foreign keys which refer to those keys, and so on. Order does not matter. I have SELECT s.name, t.name, ...
1
vote
0answers
55 views

Select all foreign key relationship chains from any set of potentially gapped table names

I am using this query to find all foreign key relationships: SELECT con.relname AS child_table, att2.attname AS child_column, cl.relname AS parent_table, att.attname AS parent_column ...
1
vote
1answer
182 views

Adding foreign keys constraint on databases used

We have a server with about 20 databases, all duplicates of the same original db, and we just noticed the original is missing all the foreign keys constraint for some unknown reason. So of course all ...
1
vote
0answers
20 views

Can I include extra columns in a m->m mapping table? [duplicate]

I guess this is sort of an odd question, but consider this scenario. table classes (id_pk) table students (id_pk) table students_classes_map (students_id_fk_pk, classes_id_fk_pk, finalExamScore ...
1
vote
1answer
96 views

Sybase ASA: Saving and dropping all foreign keys and most Primary Keys for (Bulk) Insert

Background Information: I am trying to migrate from an ASA 12 Database, to a ASA 12 Database by using a Remote Server. I have to migrate a lot of Rows (not all), spread over a lot of tables (not ...
1
vote
0answers
444 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 ...
1
vote
0answers
94 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
2answers
176 views

Multiple relationships between the same entities

Is there a name for when 2 entities can have multiple relationships between them?
1
vote
0answers
188 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
616 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
194 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
173 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
160 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
89 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
3answers
45 views

Reset Key Names to Defaults?

I'm working with some tables/columns that were renamed at some point, but the names of the primary/foreign keys are still based on the previous names. In SSMS is there any (preferably automatic) way ...