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
59 views

Enforce a single NULL end date per foreign key value using an index

I want to implement a Check Constraint across a date range and foreign key. I have a table created as: Primary Key Foreign Key DateStart DateEnd The ForeignKey can appear many times in the table ...
1
vote
1answer
67 views

Understanding the output of \d <table> in psql

Say I run the following command: \d mtb.big_table I get the output shown below. My questions are: What are column modifiers? (what does default now() mean?) Under Indexes, Why do some indices ...
1
vote
3answers
72 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
211 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
81 views

Index cleanup/usage help

My question is this: When running sp_blitzindex, I get a lot of these 2 warnings/suggestions: Index Hoarder: Unused NC index and Feature-Phobic Indexes: Potential filtered index (based on ...
1
vote
2answers
316 views

Can I use `on delete set null` on a compound foreign key which contains a primary key column?

The docs say: Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the ...
1
vote
2answers
103 views

Insert New Foreign Key Row for new Strings

Lets say I have a table new_data like this: ndx | someval | somemachine serial | integer | integer ---------------------------- And let's say I have a reference table new_data_someval_ref: ...
1
vote
2answers
851 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, ...
1
vote
1answer
259 views

Performance of checking for data in related tables

NB: this question is purely academic / to help improve my understanding of SQL Server performance. Given a master table which relates to one or more other tables, how would you determine the best ...
1
vote
1answer
63 views

What problem prevents well defined behavior of a foreign key on a non-unique column?

In my noob opinion, it strikes me that in a serialized environment, this shouldn't be a problem. Is that correct? If not, please explain to me why a foreign key's behavior on a non-unique column is ...
1
vote
1answer
977 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: ...
1
vote
1answer
243 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
2k 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 ...
1
vote
1answer
979 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
135 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
2answers
27 views

Recreate all foreign keys in all tables as deferrable (batch)

I want to make all foreign keys in my database deferrable. But there is no possibility to alter an existing constraint. So I need to drop and add again each foreign key. How to do it automatically?
1
vote
1answer
56 views

Discover missing foreign keys and/or indexes

We have a pretty big database (around 700GB of data), which has been inherited to us, and has had several DBAs. With time we've realized that there are several missing foreign keys and indexes. I'd ...
1
vote
3answers
76 views

Is it a good idea to use a personal number as a primary key for a customer table?

We are designing a new system to handle customers at our firm and me and another developer are split on wether we should use the customers personal numbers as a primary key. My preference would be to ...
1
vote
1answer
298 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
1answer
36 views

is there some way to give me the order in which inserts should be made so all FK constraint are met? [closed]

I have a mysql 5.6 schema with about 60 tables. I need to write some code (not sure if it will be Sql or java) to populate the data. The schema has lots of FK etc. Is there some good way (I am ...
1
vote
1answer
93 views

“Transitive” keys

I have a central table, A, which will have a key that I'll call A_ID. Now, I have a table B in a "one to many" relationship with A (many B's map to one A). Each link will, of course, have a link ...
1
vote
1answer
65 views

Schema design - Reference slightly different data in different tables

I'm trying to design a schema for a MySQL database and got stuck on the following. My database stores documents which should have relations with blocks of content. create table document ( `id` ...
1
vote
1answer
731 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
1answer
7k 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 ...
1
vote
1answer
7k 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
1k 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
3k 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
274 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
75 views

“Merge” two rows in a Postgres table, with foreign keys

I am keeping a database of books I've read, using the following two tables in PostgreSQL: CREATE TABLE authors ( id SERIAL PRIMARY KEY, name text ); CREATE TABLE books ( id SERIAL ...
1
vote
1answer
31 views

What is the best approach to find all fields that has (Foreign Key and Primary Key) constraints in a Mysql database?

I have a mysql database that has 250 tables. I would like to run a query to retrieve: tableName ; FieldName ; ConstaintName (Primary or foreign key). I could use ...
1
vote
1answer
55 views

MySQL Dump Restoration when FOREIGN_KEY_CHECKS=0

I currently use a SaaS DBMS. Backups of my database are provided to me in the form of a MySQL dump, and I noticed that FOREIGN_KEY_CHECKS=0. I am annoyed by this because it appears that this dump is ...
1
vote
1answer
64 views

How to reference a UNIQUE KEY CONSTRAINT in parent table as a foreign key in child table?

I have created two tables, the parent in this case being "department" and "employee" being child. CREATE TABLE department ( department_id NUMBER(4) CONSTRAINT department_id_not_null NOT NULL, ...
1
vote
1answer
77 views

Should child comment be treated as separate table?

I'm building a commenting system, currently my table indicates which comment is child with parent id in 1 table, I would like to know what is the best way of doing this? Current Table Scheme (1 ...
1
vote
1answer
64 views

Foreign key constraints failure when cross updating between two databases

I have two databases (com and usa) with a table with the following schema: Create Table: CREATE TABLE `eav_attribute` ( `attribute_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT ...
1
vote
1answer
53 views

Adding restaurants in database

I wanna store some vendors (like McDonald ,etc) and it's branches in a table. a vendor may,or may not have branches. I have created two tables: 1- A vendors table which have name,logo, etc. 2-A ...
1
vote
1answer
158 views

Foreign key default value

I have a master table and another table with foreign key. I can easily add data to master table. But whenever I want to add data to the connected table, I get a message saying Foreign key doesn't have ...
1
vote
1answer
91 views

Rename PK column names when they are FK in another tables

I need to change the names on PK columns and that has relations with another tables meaning they are FK also so this is what I am doing: SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET ...
1
vote
1answer
103 views

How to get a surrogate key value in order to put a foreign key in and create relationship?

I am trying to create two relationships between three tables, using foreign keys with values of primary keys, which are surrogate keys. But I don't understand how to enter the right value in the ...
1
vote
1answer
198 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
2answers
204 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
124 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
1k views

#1025 - Error on rename of table errno: 150 : Table was deleted while tried to assign foreign key

When I tried to assign foreign key to a table named mydb.table1 it was deleted from my database, I could not understand the reason and tried to move on. For this tried to create same table with create ...
1
vote
1answer
282 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
29 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
263 views

ON DELETE CASCADE not working

Trying to implement a simple user follower system in MySQL, using a junction table: CREATE TABLE users ( id int NOT NULL AUTO_INCREMENT, email varchar(255) NOT NULL, username varchar(25) ...
1
vote
1answer
69 views

MySQL: Is there a way to update a field so that all foreign key related fields are updated as well?

I'm assuming the answer to this question is yes but it is hard to Google this type of question and not get overly ambiguous results. I am working with a database that was dumped from a Magento ...
1
vote
1answer
38 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
75 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
485 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
1answer
548 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. ...