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
52 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
32 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
29 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
548 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
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
113 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
857 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
93 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
142 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
1k 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
141 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
311 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
173 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
750 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
561 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
264 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
108 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
4k 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
257 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
29 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
0answers
29 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
145 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
142 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
77 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
358 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
1answer
3k 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
0answers
82 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
117 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
179 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
583 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
188 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
166 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
154 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 ...
0
votes
2answers
7k 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
62 views

Create relationship between two tables with composite primary key

I've created a table [t1] with two fields and a composite primary key, show in below: create table t1( id int, name nchar(10), primary key(id,name) ); I want to create a second table ...
0
votes
4answers
91 views

SqlServer: can one table's PK be the FK of other two table's? [duplicate]

I have three tables project, users and proj_user. The PK of proj_user table is the FK of project and users table's pk e.g.(project_id,user_id) My question is, is it possible for the project_id and ...
0
votes
1answer
65 views

Batch converting indexes to keys [closed]

I have a legacy MS SQL Server database where all the primary and foreign keys were created as indexes. I am developing the new version of our software with Entity Frameworks, which I have learned ...
0
votes
1answer
158 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
2k 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
3answers
72 views

Can i have one foreign key to both parent and it's childs in postgresql?

I want to have a foreign key to reference table and it's children. Here's how I tried and failed: CREATE TABLE parent ( id serial, CONSTRAINT parent_pk PRIMARY KEY(id) ); CREATE TABLE child ( ...
0
votes
2answers
50 views

One field with multiple values?

I know that what I'm asking can't be done or shouldn't be done, but I really don't know how to do it. This is the problem: 2 tables: - Drivers (id_driver, name, etc...) - Trucks (id_truck, model, ...
0
votes
1answer
113 views

deleting a table referenced by many foreign keys

I have a table that I want to drop and create again, because I want to add the IDENTITY property to a column, wich is not possible directly. The problem is that I can't drop it, because my column is ...
0
votes
2answers
123 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 ...