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

0
votes
1answer
78 views

Save dataset within another table best practice

I want to save one dataset within another table, this dataset should be persistent. For example Table1: FIELDS (A,B,C) Table2: FIELDS (D, DuplicateTable1) I can think of 2 possiblities: Have ...
1
vote
1answer
287 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 ...
3
votes
2answers
491 views

DROP TABLE … CASCADE does not drop any dependent table

I am still relatively new to psql and I was just finishing an assignment when I decided to try something which was to drop a table which other tables depend on due to a foreign key relationship. My ...
3
votes
2answers
115 views

Design encouraging FK to non-unique/non-PK

My current design is makes me want to create a FK to a non-unique/non-PK. Is this a code smell and/or does my overall design make sense? CREATE TABLE [User].[ConnectionRequest] ( ...
0
votes
2answers
52 views

Trying to create a table with foreign keys and its missing a few rows [closed]

This is what I have so far: CREATE TABLE GRADE ( COURSE_NO VARCHAR2(8), STUDENT_ID VARCHAR2(15), GRADE VARCHAR2(3), PRIMARY KEY(COURSE_NO,STUDENT_ID) ENABLE ); ALTER TABLE ...
3
votes
1answer
85 views

How to remove unreferenced rows in a “Many to One” relation?

in my db i have the following entities: Cities, Persons, Houses. Each Person MUST live in a City. Each Person CAN live in a House. Each Person can change or leave their Houses. Persons living in ...
1
vote
1answer
46 views

Indirect constraints (foreign keys amongst three/four tables) [duplicate]

I have the next layout of tables: table COMPANY: - primary key pk_company table USERS: - primary key pk_users, - foreign key (fk_company) references COMPANY (pk_company) table GROUPS: <br> - ...
39
votes
2answers
74k views

Good explanation of cascade (ON DELETE/UPDATE) behavior

I don't design schemas everyday, but when I do, I try to setup cascade updates/deletes correctly to make administration easier. I understand how cascades work, but I can never remember which table is ...
1
vote
1answer
88 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 ...
5
votes
2answers
557 views

Conditional Foreign Key Relationship

I currently have a foreign key between two entities, and I would like to make that relation conditional to the entityType of one of the tables. Here's the hierachy of tables, this is done via FK ...
2
votes
2answers
91 views

Nested table hierarchy: foreign key duplication vs. join

I have a nested table hierarchy, similar to the following one (this is an example but demonstrates the schema well enough): +---------+ +---------+ +---------+ +-------------+ | users | | ...
2
votes
0answers
89 views

Why are foreign keys ignored on subscribers?

We are using merge replication on SQL Server 2008 R2. There are many tables with foreign keys providing a logical dependence on each other. For this question, let's say there are User and File tables, ...
2
votes
1answer
456 views

Mysql foreign key cascade delete

Does mysql have a way to delete all sub links if a row was deleted from a "parent" table? Example table setup is below, the issue I am trying to avoid is having to maintain a script that deletes the ...
1
vote
1answer
231 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
1answer
421 views

How to select multiple id from same table

Here is the simplified version of my problem. I have these tables: Master table id - name - surname - fathers name - mothers name Names id - name Surnames id - surname I want to query master ...
2
votes
0answers
18 views

Check a constraint after adding a Foreign Key [duplicate]

If I add a foreign key constraint with the following command: ALTER TABLE [abc] WITH CHECK ADD CONSTRAINT [fk_some_col] FOREIGN KEY([some_col_id]) REFERENCES [xyz] ([some_col_id]) Is there any ...
8
votes
3answers
249 views

What is lost when I create a Foreign Key using `WITH NOCHECK`?

I know that if I do an EXISTS() call on a FK lookup value, then, if that FK constraint is trusted, the result is immediate. And if it is not trusted (like when I create the FK using WITH NOCHECK) ...
1
vote
1answer
63 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 ...
3
votes
4answers
2k views

Replication fails because of a non-existent foreign key

I have been trying to get a previously working replication to start working. I have tried many settings, but I can't seem to make it work. Here is the scenario. I dropped all the FK constraints ...
1
vote
1answer
63 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
0answers
10 views

Is it possible to cascade delete a column value? [duplicate]

So say I delete catID from table A and table B has the same foreign key value? It's hard to explain so I'll try and show you with the two images below: items table Is it possible to delete the ...
8
votes
3answers
373 views

zero-or-one to zero-or-one

How do I model zero-or-one to zero-or-one relationship in Sql Server the most natural way? There is a 'Hazard' table that lists hazards on a site. There is a 'Task' table for work that needs to be ...
1
vote
1answer
563 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 ...
3
votes
1answer
148 views

Enabling Constraint Silently Fails

Why would SQL Server silently fail to re-enable a foreign key constraint? I recently attempted to re-enable a number of constraints using ALTER TABLE. Most enabled successfully; however, minority of ...
3
votes
1answer
120 views

Dropping Foreign Key In Production Environment

I have been asked to drop foreign keys on a number of databases in our production environment. I am relatively new to MySQL... what do I need to be aware of when doing this? E.g. full table locks, ...
2
votes
1answer
77 views

Can Indexes mitigate untrusted Constraint

I using sp_blitz and found two tables that have foreign keys that is untrusted. tried to enable the trust with: ALTER TABLE MyTableName WITH CHECK CHECK CONSTRAINT MyConstraintName; GO But got this ...
0
votes
1answer
205 views

How to populate a table's foreign keys from other tables?

I have a database with the following tables: Airport ( iata PK, airport, city, state, country, lat, long) cancellation_cause ( cod_cancellation PK, description) Manufaturer (id_manufacturer PK, ...
0
votes
0answers
49 views

Foreign key to Table A, and foreign key to table with foreign key to Table A

I have a table of products. Each product has a vendor. Each vendor has several delivery categories, and each product also falls into one of these. What's the best way to design this? Currently, I'm ...
0
votes
0answers
72 views

Error while deleting a foreign key

I can't delete foreign key in MySQL, I get this error message: ERROR 1025 (HY000): Error on rename of './database/table_name' to './hp/#sql2-4b1-26' (errno: 152) My system is Ubuntu 14.4 lts
2
votes
1answer
337 views

computed column as foreign key allow null

I have two tables. TABLE A: One of them stores some integer identificators, which are unique, but actually are structured entities. I will call them full_id. Actually its a two parted ...
0
votes
0answers
11 views

Where to put a 0..1 fk column when already having 0..n fk’s on one side and 0..1 on the other

I have a transaction table that represents user’s credit transactions and two tables containing different entities, but both interacting with the transaction table. Say, payment_with_state and ...
1
vote
2answers
381 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
1answer
363 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) ...
2
votes
1answer
534 views

MySQL server won't start with foreign_key_checks=0

I'm running Ubuntu with MySQL version 5.5.44 mysqld Ver 5.5.44-0ubuntu0.14.10.1 for debian-linux-gnu on x86_64 ((Ubuntu)) I have a DB with foreign keys. I would like to have the foreign key ...
1
vote
0answers
73 views

How to pass the value of PK to FK

Ok, so here it goes. I have a table called employee. then I have another table called languages. I need to copy the value of PK of employee table to foreign key in Languages table, rather than ...
1
vote
1answer
209 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 ...
0
votes
2answers
19k 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 insert data into the Table3. See the code below : DROP TABLE Table1 CASCADE; ...
8
votes
1answer
169 views

Overcome MERGE JOIN(INDEX SCAN) with explicit single KEY value on a FOREIGN KEY

Added 7/11 The issue is deadlocks occur because of index scan during MERGE JOIN. In this case a transaction trying to get S lock on the whole index at the FK parent table, but previously another ...
1
vote
1answer
122 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 ...
0
votes
1answer
394 views

Can't add a foreign key constraint in a table on Postgresql

I'm trying to add a foreign key of "prefectures" table in "centresdevote" table. But I'm having this error while trying to add a foreign key to "centresdevote" table. I had no problem adding foreign ...
11
votes
2answers
7k views

What does 'ibfk' stand for in MySQL?

If I create a foreign key constraint for table 'photos' in phpmyadmin, I later see that the constraint is named 'photos_ibfk_1', and the next constraint is called 'photos_ibfk_2', etc. From this I ...
4
votes
1answer
50 views

How to create a database with “polymorphism”

I'm sorry I'm new when it comes to databases, I'm using Access right now to develop a database to store calibration data for a bunch of different types of transducers. Each type of item is ...
2
votes
1answer
37 views

Should I create many ID for a same Person/entity in my DB?

In my model, I must store the name of the teacher of some kids and the classroom in which the teachers work, let’s say. All of the hundreds of teachers in the schools have 1 and only 1 classroom, ...
0
votes
1answer
91 views

Multiple Cascade Paths (Not really) - Sql Server

In my database I have a table with 2 columns referencing the same column in a separate table. I know this creates a race condition, but I have a check in place to make sure they don't point to the ...
0
votes
1answer
88 views

Problems in Choosing Foreign Keys :S

I am designing my very first relational database. I am now in the conceptual design, building tables and views, selecting the primary and foreign keys and normalizing everything. But now, I am stock ...
2
votes
0answers
23 views

SQLITE Foreign key has to be re-added? [closed]

Currently I have a mysql database on my computer which I convert to sqlite database. On the sqlite database, I have transaction_detail table with a foreign key to transaction_master table. The ...
0
votes
0answers
145 views

What order do foreign key constraints run in? Why does my foreign key not work after import?

I have two tables tags and tag_memberships in a PostgreSQL database. On the tag_memberships table, I have two constraints. The first is this: ALTER TABLE tag_memberships ADD CONSTRAINT ...
0
votes
2answers
244 views

Performance impact of adding a foreign key to a 1M rows table

I have an ecommerce database running MariaDB + InnoDB, which has a table to store the transactions (T) and another which stores quotes (Q). I'd like to make a connection between T and Q by adding a ...
2
votes
1answer
388 views

Have foreign key constraint and index key for the same column(s)

I am creating a composite index key for 2 columns and then adding foreign keys to its appropriate table. Do I need to create a multiple index key reference and then create separate foreign keys? My ...
0
votes
0answers
85 views

Sybase: Delete foreign key IF exists fails

I've a database, on which we will have to delete of tables/foreign keys/view. We did deleted all the required element in PowerDesigner, then we did the "Apply model change to the database". We ...