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
4answers
31 views

Selecting data from another table using a foreign key

I have two tables A & B. Table A id | name | num Table B id | date | roll id in table B is a foreign key which relates to the id in table A. I want to get the contents of table B where id ...
0
votes
0answers
11 views

Design - duplicating properties vs not allowing delete

I'm designing a database for a mobile app CMS that contains user data. These objects are Address and Job (a job can have 1 address and an address can have many jobs). The first version of a similar ...
3
votes
2answers
187 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 ...
2
votes
2answers
115 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
2answers
2k views

VARCHAR primary key - MySQL

Currently, I have a categories table with 2 columns - category VARCHAR(50) NOT NULL PRIMARY KEY and parent VARCHAR(50). The parent column is a foreign key (FK) to the category column. This seems like ...
1
vote
2answers
150 views

novice-design: Are FOREIGN KEYs always necessary? Joins on columns which are not PK

I've been wondering if following approach is bad or really very bad:). Question#1: can we use indicator_id to combine data from indicator_values and indicator_details tables (without using FOREIGN ...
3
votes
5answers
328 views

How do I prove that the act of removing foreign keys doesn't corrupt existing data?

TL;DR: Prove that in practice, the execution of the alter table table_name drop foreign key constraint_name statement does not corrupt existing data. The important consideration is the execution of ...
4
votes
2answers
535 views

Foreign Keys doesn't work as I expect

Here are my tables; CREATE TABLE `borclular` ( `id` int(11) NOT NULL AUTO_INCREMENT, `tc` char(11) COLLATE utf8_turkish_ci NOT NULL, `borclu` varchar(255) COLLATE utf8_turkish_ci NOT NULL, ...
2
votes
1answer
24 views

Surrogate vs composite key in hierarchical data structure

I'm currently evaluating a schema for a hierarchical data structure. My main problem is how I should design the schema to prevent inconsistent data (reference of foreign key in another hierarchy). The ...
2
votes
1answer
215 views

How to resolve foreign key references across different databases (iSeries and SQL Server)

I've got a system that has a DB2 iSeries database. We are rewriting a portion of the system and implementing it using SQL Server and Entity Framework. Originally, I was lead to believe that there ...
0
votes
1answer
31 views

Phpmyadmin / mysql error defining primary keys as foreign keys of two separate tables

I am designing a mysql database using phpmyadmin 3.3.10.4. Everything in the database is InnoDB. I have two tables, person with primary key person.id_numberand e-mail_address with pk ...
16
votes
2answers
5k views

Why would a table use its primary key as a foreign key to itself

Looking through a database, I came across a table that used its primary key as a foreign key to itself. I've seen that a table can have a foreign key to itself to build a hierarchy structure, but it ...
0
votes
0answers
35 views

Cannot add foreign key constraint'?

Can someone please correct this code for me? When I click enter it says 'cannot add foreign key constraint'? CREATE TABLE entries ( Subject_id smallint auto_increment, Student_id VARCHAR(45) ...
1
vote
4answers
35 views

Foreign and primary keys question

I have have looked for an answer for my questions everywhere but they seem confusing to me.Can a foreign key be a primary key in the same table? Can a foreign key be a primary key in another table? ...
1
vote
1answer
196 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 ...
2
votes
1answer
1k views

How to add FK on a column of composite type in PostgreSQL?

I have a table which contains a composite type and I am trying to connect a column of it to another table's PK. CREATE TABLE aaa (col1 SERIAL PRIMARY KEY); CREATE TYPE bbb AS (col2 INTEGER); CREATE ...
1
vote
2answers
321 views

Multiple relationships between the same entities

Is there a name for when 2 entities can have multiple relationships between them?
4
votes
1answer
532 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 ...
3
votes
1answer
180 views

Table structure for validating data using category and subcategory fields

I am creating a simple budget application, and the primary table will be a transaction table that contains things like the amount of the transaction, date, account, the category and subcategory (the ...
1
vote
1answer
42 views

Why does In-Memory feature not support foreign keys?

Why does Sql-Server 2014 In-Memory feature not support primary/foreign key relations? Can anyone explain what the problem with foreign key relationships and in-memory is? I guess just and only this ...
0
votes
1answer
24 views

Name of ForeignKey Back Relation?

Table detail has a ForeignKey to table master. How to call the back relation? master has ??? to table detail I could not find a canonical word in wikipedia or other resources.
3
votes
1answer
28 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 ...
3
votes
0answers
49 views

How does mysql(innoDB) handle same foreign key for join

I have a table called X and another called Y. There is a primary key on Y that is a foreign key on X. Now, my data is such that there will be many rows in X that will have the exact same foreign key ...
1
vote
1answer
37 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
31 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
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
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
27 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 ...
11
votes
1answer
4k views

MATCH FULL vs MATCH SIMPLE in foreign key constraints

I've noticed a MATCH SIMPLE or MATCH FULL option in phpPgAdmin, but I can't find a description in the docs. The default is MATCH SIMPLE. How do they function?
3
votes
1answer
91 views

Store different rows type in single table

I'd like to create a DB to store different rows type. It will be kind of event list with time stamp. So I'd like to have an Event table with relations to other tables - each table will be a row type ...
0
votes
1answer
14 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
36 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
2answers
403 views

Should I use a compound or surrogate PK in a many-to-many table?

I have a database: DROP TABLE IF EXISTS `books`; CREATE TABLE `books` ( `isbn` VARCHAR(255) NOT NULL, `title` VARCHAR(255) NULL DEFAULT NULL, PRIMARY KEY (`isbn`) ) COMMENT 'Books used at this ...
2
votes
2answers
359 views

Performance effect of indexing foreign keys

My database is for an HR application with performance reviews and I am interested in whether I should index my foreign key. There are two tables involved. The first is a ratings table with 2 columns: ...
3
votes
2answers
730 views

Is there a standard for naming foreign keys when two columns reference the same table? [closed]

I've always used the name FK_CurrentTableName_ReferencedTableName. I just ran into a situation where I've got a table with CreatedById and ModifiedById both pointing to the same (users) table, so this ...
5
votes
1answer
10k views

How to get the foreign and primary key details for a table in SQL Server 2008

I would like to use 2 queries, one that lists all the foreign keys for a given table with the following information Schema name, foreign table name and the other that lists all the primary keys. Is ...
2
votes
1answer
6k views

Using a foreign key on multiple fields in one table

Simple question probably. For two tables. A one to many relation is created from A -> B, TeamID -> Fk_Team, however there are multiple fields that need to reference this one relationship how does ...
4
votes
2answers
25k views

How to Find the Foreign Key Associated with a Given Primary Key

All, I want a way to establish which columns in a given database are joined via PK/FK relationships. I can return the PK/FK information for a given table via SELECT * FROM ...
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
64 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
43 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 ...
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 ...
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
1k views

How to check foreign keys related to a table

How to see foreign keys related to a table in MySql? Background : I wanted to drop a table in MySql which has a foreign key constraint. When I do it I get this: Error Code: 1217. Cannot delete or ...
0
votes
0answers
42 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
46 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
30 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
27 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
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 ...
0
votes
1answer
23 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] ...