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

Postgres Can't Add Foreign Key Constraint

I have a table with about 220 million records :( and I need to add a foreign key constraint. My command looks something like this: ALTER TABLE events ADD CONSTRAINT events_visitor_id_fkey FOREIGN ...
0
votes
1answer
33 views

Do Foreign Keys Impose Uniqueness

Do FOREIGN KEYs impose the uniqueness of the the referent? I.e. if I have a foreign key that refers to a UNIQUE constrained column, will the FOREIGN KEY column be unique? I'm particularly wondering ...
1
vote
2answers
32 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?
2
votes
1answer
36 views

Replace content of only one table with new version (should disable foreign keys?)

I have 2 tables. 2nd has foreign keys to reference 1st. I need to replace the whole content of the 1st table with the new version from another database. The new version satisfies all constraints and ...
1
vote
1answer
35 views

Cities and States (a few data): single table or splitted?

I am designing a DB schema and I need to store cities and states. I expect only a few rows, let's say, 20 cities. Example: id | city | state 1 | Rio de Janeiro | RJ 2 | Niteroi ...
3
votes
2answers
942 views

Database design of Room Booking system in a Hotel

Intro and System Description I am currently designing a Database for a Room Booking system in a Hotel. Client fills a Form / Request with following information about Room: amount of people in ...
0
votes
1answer
26 views

load data infile in mysql server 5.5 can't load data when foreign key exist

I have a database (TPC-DS benchmark base) with 24 tables(17 dimensions tables, 7 facts tables) I generated this database then I created it in mysql server 5.5 I used the instruction load data local ...
1
vote
0answers
37 views

Many-to-many relationship, what's the best choice for primary key? [duplicate]

In advance, I'd like to apologize if it's a duplicate question... I can't find a direct answer, probably because I'm not a native english speaker. Straight to the subject: I have 2 tables: company, ...
3
votes
1answer
31 views

Integrity Issues in this structure?

Imagine the situation. There are three tables, Student table[PK Student ID] Enrollment Table [PK EnrollID, FK Student ID] Fees Paid [PK Fee ID, FK EnrollID, FK StudentID] Edit: Fee_Paid Table ...
0
votes
2answers
94 views

What are some properties that a Foreign key must satisfy always?

I know about Primary Keys. PKs must satisfy: (1)Unique, (2) Not NULL, (3) Fully Functional Dependency, (4) Indexed.... these 04 properties. What are some properties that a Foreign key must always ...
0
votes
0answers
45 views

MySQL/MariaDB: Can I drop KEY declatations in sql dump for CREATE TABLE for foreign keys?

I prepare Liquibase schema creation changelog for legacy DB. I started with SQL dump and refined it by little sed processing so I have only CREATE TABLE statements. It looks like: CREATE TABLE ...
3
votes
2answers
37 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
544 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
83 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
37 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
185 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
80 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
54 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
57 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
52 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
186 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
89 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
86 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
72 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
80 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
377 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
90 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
558 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
51 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
138 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
99 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
97 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
500 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
113 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
273 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
64 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
645 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
66 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
404 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 ...