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

3
votes
2answers
36 views

Reasons to maintain FKs on an empty, deprecated table?

So I have a few dozen empty tables with foreign keys to other, populated tables. These tables are not being accessed by any CRUD operations, but have about 6-12 months before they can be unlinked from ...
4
votes
1answer
542 views

Partialy Solved - Problems Deleting Data

I did a workaround that solved the problem for today but I want to know what I did wrong for the next project. I'm stuck on trying to delete some information on my MySQL database. I've never used a ...
0
votes
0answers
78 views

SET FOREIGN_KEY_CHECKS=1; Commands out of sync; you can't run this command now

I am getting errors that I do not know how to solve using MySQL and php. The error is this... SET FOREIGN_KEY_CHECKS=1; Commands out of sync; you can't run this command now I am trying to run the ...
0
votes
1answer
35 views

Removing duplicate rows and changing related foreign keys in PostgreSQL

I have found all duplicate rows and now want to delete them after I update related foreign keys in other tables so they point to the correct row. How do I get a list of foreign keys related to my ...
0
votes
0answers
30 views

How to update a column with foreign key?

In two tables with a foreign key as CREATE TABLE table1 ( id int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci; CREATE TABLE table2 ( id2 ...
1
vote
1answer
159 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 ...
0
votes
1answer
24 views

Composite foreign key not verify all columns

Table1 (Num [PK NOT NULL], Dat [PK NOT NULL], ...) Table2 (ID [PK], Num, Dat, ...) I added this FK: ALTER TABLE [dbo].[Table2] WITH CHECK ADD FOREIGN KEY([Num], [Dat]) REFERENCES [dbo].[Table1] ...
1
vote
1answer
76 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 ...
2
votes
0answers
53 views

Any drawbacks to referencing multiple tables from a single column?

I'm working on a system with the following entities: Projects Sections Activities There is one important constraint that needs to be enforced: Activities are always part of one project. ...
2
votes
1answer
33 views

sqlite3 foreign keys ignored

I am working on a small sqlite3 database for a small simple mailserver. I have created a new sqlite3 database and add executed this to pürepare it for data: BEGIN TRANSACTION; CREATE TABLE ...
0
votes
1answer
55 views

is_not_trusted = 1 after Error-Free WITH CHECK CHECK CONSTRAINT

I'd like SQL Server 2005 SP4 to trust a certain foreign key constraint. Executing an ALTER TABLE statement that enables + checks the constraint returns Command(s) completed successfully yet ...
0
votes
1answer
47 views

cross database table references

I try to create table GroupArticle in database CmsGroup, one column ArticleId references another table in another database? is this possible and correct syntax? CmsArticle CREATE DATABASE ...
1
vote
1answer
36 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
159 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
3answers
88 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 ...
0
votes
1answer
75 views

Two ways to define may-have-a (1..0-1) relationship

Today I have discovered for myself that may-have-a (1-0..1) relation can be implemented in two different ways. Let A be the "main" table (left side of ..) and B be the "linked" table (right side of ...
1
vote
1answer
25 views

Foreign key: in which table?

I made the tables blood_types and persons. Should the foreign key be created in the blood_types or in the persons table like I did? A person can have 1 blood type. A blood type can be assigned to ...
1
vote
1answer
70 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, ...
2
votes
1answer
69 views

Is there a way to limit the number of foreign key references for a single table index

I'm having difficulty finding an answer because I'm having difficulty expressing the question. So I apologize if their is a duplicate (or similar) question on here. Is there a way to limit the number ...
3
votes
0answers
324 views

Restoring from pg_dump with foreign key constraints

In restoring a database from a pg_dump, a number of errors are being generated and the whole table is subsequently being ignored. An example: ERROR: insert or update on table "channelproducts" ...
1
vote
0answers
52 views

Inexplicable differences between 2 almost identical databases on the same server

Our situation is as follows: We've got 1 MySQL (5.5.44) database server. The server hosts 2 databases. 1 database is the production database. The other is a copy database that gets copied from ...
1
vote
1answer
87 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 ...
3
votes
2answers
490 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 ...
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 ...
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] ( ...
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> - ...
2
votes
2answers
135 views

Is there any reason not to use foreign keys in MySQL?

I've never really used foreign keys before but I am building a project where they could come in handy. I have looked at some other large software such as some forum systems and they don't use foreign ...
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 ...
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 | | ...
1
vote
1answer
419 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 ...
3
votes
1answer
109 views

Soft ON DELETE CASCADE

I am familiar with the ON DELETE CASCADE method, but I don't want to do a real delete, just a soft delete. This is implemented with a column named isDeleted (0/1). Is there a way for me to cascade ...
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 ...
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 ...
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 ...
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
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
1answer
203 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
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 ...
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
208 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 ...