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
0answers
20 views

Create a not unique FK in MySQL Workbench

I'm trying to model a database using MySQL Workbench (version 6.3.6, 64 bits, using linux mint). After syncing my model with a MariaDB (running in localhost, version 10.0.25), I discovered that the ...
0
votes
0answers
18 views

Cannot drop constraint due to FK references

I have detected a duplicate index on an id field on one of our tables, as that column is the PK, but there's also a separate UNIQUE index on such column, which is useless. But I cannot delete the ...
1
vote
1answer
23 views

Tricky condition for PostgreSQL UPDATE query

I need help with a PostgreSQL query. I have a Django app + pg backend, where users post interesting URLs (or links) and can then comment under such links (called replies). There are two pg tables to ...
0
votes
1answer
40 views

Do I have to use Foreign key as part of composite primary key or not?

I'm designing a relational database. This is what I'm trying to design: I will have production orders and products. A production order will have one product, but a product could be on zero or more ...
1
vote
1answer
47 views

Automatic index creation for primary vs. foreign keys in Postgresql

For PostgreSQL 9.4 (http://www.postgresql.org/docs/9.4/static/ddl-constraints.html) it is stated that A foreign key must reference columns that either are a primary key or form a unique ...
0
votes
1answer
41 views

MySQL InnoDB constraint does not work

I stumble upon strange behavior with innoDB constraint, and cannot find cause of it. I have tables with data. Below listed their structures: CREATE TABLE `contents` ( `id` int(10) unsigned NOT ...
2
votes
1answer
32 views

How to see, if a foreign key is enforced?

How can I see in IBM DB2, if a foreign key is in enforced Status? In the systemviews REF_CONSTRAINTS and REFERENTIAL_CONSTRAINTS I see them, but I cannot see, if they are in enforced Status. Is there ...
3
votes
1answer
26 views

enforcing data integrity for sequential relations

These are my tables: create table trips ( trip_id serial primary key, trip_nm text ); create table trip_segments ( segment_id serial primary key, departure_ts timestamp with time zone, -- ...
1
vote
2answers
41 views

Parent -> Child -> Grandchild with a foreign key back to Parent, Bad Practice?

Basically the foreign key on options is there to make it so that if you want to know what product an option belongs to you don't have to go through variants. A variant is like Color, Size Etc.. An ...
0
votes
1answer
45 views

Main Purpose of FOREIGN KEY

When I did my first interview, I faced this confusing question. By using join we can join the tables, but what is the main purpose of FOREIGN KEY in mysql? Please suggest me the best answer.
1
vote
1answer
40 views

PostgreSQL Foreign Key

Very basic questions. I have 3 tables, very obvious whats the use of them: -------- CONTACT -------- ID (SERIAL) - PK NAME (VARCHAR) --------------- CONTACT_DETAILS ---------------- ID (SERIAL) - PK ...
0
votes
1answer
23 views

Primary key which is foreign key in an relationship is specified NOT NULL. Why?

I have question about a foreign key. Primary key which is foreign key in an relationship is specified NOT NULL in some book's example. Why? I was thinking that primary key couldn't take on value ...
0
votes
1answer
24 views

Can I reference a specific column in a remote table (MySQL)?

I have two databases (MySQL) on two different servers, and one table references a table in the other. However, I am unable to point the reference to a specific column in the remote table for some ...
1
vote
0answers
24 views

MySql Error 1451 [closed]

I am by no means a database professional, but I've come here to get some help from people who may understand what's going wrong with my database. The error occurs on this line: update team set ...
1
vote
1answer
45 views

Relationships mess

Say I have a table ads, and each ad has several medias - held in a medias table. Each media can be one of several types. Types are in a media_types table. So, for example, an ad has two medias: one ...
0
votes
0answers
31 views

How to Know Why Foreign Key Constraint Fails in MySQL

I'm using Laravel's Query Builder to put together some tables. I am trying to set up a foreign key, but it's failing with the following error: [PDOException] SQLSTATE[HY000]: General error: 1215 ...
3
votes
1answer
61 views

“Cannot resolve table name” error when applying MySQL dump to RDS

I'm setting up replication of an external MySQL instance to RDS. Both the external MySQL instance and RDS run MySQL 5.7.10. I've followed the procedure, but when dumping the database with mysqldump ...
0
votes
1answer
25 views

Is inserting into a table with a foreign key constraint slower than inserting without constraint?

When using MySQL (actually, Aurora), will inserting into a table with a foreign key constraint be slower than the same table without the constraint?
4
votes
1answer
97 views

Foreign key with additional constraints?

There is a table called Item(id, name, cost) and Orders(id, bill_id, item_id, units), which is created to track orders placed, where same bill_id means it belongs to a single order. How to impose an ...
0
votes
0answers
38 views

primary and foreign keys used in object-relational table

Currently I am designing an object-relational database based on an existing relational database with multiple primary and foreign keys, and was wondering if it is OK to use primary and foreign keys in ...
1
vote
1answer
22 views

MySQL getting errno: 150 on table creation from Workbench

I'm inexperienced with MySQL and I am trying to build a few tables and getting errno 150 which I believe could be to do with Foreign Keys. Here is my SQL as generated by Workbench. Thanks in advance ...
1
vote
1answer
51 views

Foreign key not working

This is a very basic foreign Key issue, I presume. Unfortunately I am not a dba so it is a bit baffling to me. I have two tables, Report and Request. The Report table has a primary key of [RP_ID], ...
0
votes
0answers
37 views

postgreSQL FK on delete set null not working

I want to set FK constraint on delete to null but it is not working. In table transactionauditlogentry I have altered the FK constraints for Packet and Credit table to on delete set null. If I ...
1
vote
1answer
29 views

SQL Server Management Studio: The columns in table do not match an existing primary key or unique constraint

I have two tables: Students and Courses. I want to set a foreign key in the Students table from the couseId column to the courseId column in the Courses table. I have set primary keys to both ...
2
votes
0answers
50 views

Should I move repeating foreign keys into separate table?

Idea Users can upload lots of items and send them from processing. From the upload settings I can set a channel to which the uploaded items belong to. Channel is a separate entity. Currently I set ...
1
vote
3answers
196 views

ORA-02270: no matching unique or primary key for this column-list

I getting an error report of: Error report - SQL Error: ORA-02270: no matching unique or primary key for this column-list 02270. 00000 - "no matching unique or primary key for this column-list" ...
1
vote
1answer
94 views

Database design for dentists

I have a homework, where I should create a database for a dental clinic using MySQL Workbench. This database will be used later for a web app for dentists. Here is a picture of the database design: ...
3
votes
1answer
63 views

Convert a lookup value to foreign key when a query is run

Currently we have a legacy system running queries on a Postgres server to update/insert records. One of the obstacles we are having is that we have to convert text values to the correct foreign key ...
-2
votes
1answer
73 views

Assign foreign key using one of two tables

Question 1: There are 3 Tables Direct Customers, Agent Customers, Sales Primary keys are marked in red underline: How to set foreign key for sales table using other two tables. I want foreign key ...
1
vote
0answers
51 views

Foreign key violations detected upon UPDATE on another column

I have a table country that is referenced by many tables like so: db=# \d client Table "public.client" Column | Type | Modifiers ...
1
vote
1answer
49 views

SQL Query; Admin Log System; Foreign Keys; Retriving multiple rows form different tables at once

Table A (users) id | name | ip 52433 Yashas 1.1.1.1 512 Admin 5.6.7.8 Table B (admin_record) id | admin | time | date | reason | action 52433 512 2312352 ...
0
votes
1answer
20 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 ...
1
vote
4answers
105 views

Selecting data from another table using a foreign key [duplicate]

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 ...
1
vote
2answers
157 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 ...
5
votes
2answers
543 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
44 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 ...
0
votes
1answer
56 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 ...
0
votes
0answers
40 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
38 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
55 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
25 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
0answers
61 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
46 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
30 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
33 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
33 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
657 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
21 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, ...