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

Mysql auto insert record in primary table if not exist

Is it possible to auto insert a record into the primary table if the record does not exist when adding a foreign key? For example, assume these tables: - user(id, name, age) - topic(id, name) - ...
1
vote
0answers
45 views

How to reference Foreign Key(s) to the parent table where data goes on increasing?

Hello i am very new to learning PHP and MySQL. I have just begun learning MySQL. As the question mentioned above, will be kind if anyone can give a solution. Please see the example of a simple ...
1
vote
2answers
47 views

How to find out if rows are refenced from other tables?

I have a table named contacts (primary key id) which is referenced by a number of other tables via foreign keys. When a list of contacts is displayed in the application, I get their primary keys in an ...
3
votes
2answers
63 views

What are the best practises for using Code Tables in Relational Database?

Code Tables are usually a collection of possible values that can be given for a column and it is stored as a Table. For example, suppose we have a Code Table party_code, the possible values are ...
3
votes
1answer
111 views

Is it Ok to use GUID as the Foreign Key?

Here's my current situation: We have a table storing data submitted from the website form, say "MyTable". The table uses bigint (auto-increment) as the primary key. Everything is good so far. Now, ...
4
votes
1answer
84 views

Foreign Key Constraint Set Back to Trusted

We have some production tables with numerous constraints that are not trusted at the moment. We're having them checked/trusted so they can be used again. We know that the optimizer has not been ...
1
vote
0answers
27 views

Key/value table: what is a good way to organize it if value could be foreign key?

Suppose a have a structure of two tables Table Key names: |_Key name_|_Parent_key_id_| | key1 | NULL | | key2 | key1 | Table Key values: |_Key__|_Value__|_Version__| | ...
2
votes
0answers
50 views

Constraint violation in a trusted constraint

I have a table with a constraint on it that has an existing record that is in violation of that constraint. While this is possible if someone applied the constraint without a check and after ...
0
votes
1answer
46 views

Error text: “Key … is not present in table” even though the key is there?

Using Postgresql 9.5 on Ubuntu 16.04. Importing large .csv files into a new database using COPY FROM. The database is created by the same script that attempts to import the .csv files, so I know the ...
2
votes
3answers
124 views

Can't add Foreign Key Constraint

As far as I can tell it should work, I went through other similar questions but it seemed to be a different thing causing the same error. CREATE TABLE Job ( JobNumber INT NOT NULL, JobName ...
1
vote
1answer
49 views

Distinguish between unknown and non-present foreign keys

I'm in the situation of importing data from multiple other applications. The data is stored in multiple tables that contain foreign keys. Some of these applications don't provide all the needed data ...
0
votes
0answers
35 views

Writing query for sorting based on foreign attribute

I am creating a voting system. My database looks like . Vote is a 'wrapper' for subitems. A vote has many subitems. When an action on that wrapper is performed (create/update/edit),a row is added in ...
2
votes
2answers
58 views

Foreign Key (references) constraint on PostgreSQL Foreign Data Wrapper?

It seems to be disallowed to add a Foreign Key constraint to a foreign table. Is there any other way to do this? My two tables have these constrains on the remote server. More specific details: ...
3
votes
2answers
79 views

Enforce uniqueness across optional attributes for an entity

I have an entity that has multiple attributes, and these attributes are themselves entities. No two entities may have the same attributes, so there needs to be a uniqueness constraint across them. ...
5
votes
1answer
154 views

Data inconsistency prohibition if a table refers to another via two many-to-many relationships

I have following database design (by => foreign key constraints are depicted): Company[id] CompanyRealm[id, company_id=>Company.id]many2many Project[id, company_id=>Company.id] ProjectRealm[id, ...
1
vote
0answers
45 views

My simple FK constraints appear to have been ignored. How?

I have two tables, device and platform where device.platform_id enforces the following constraint: Foreign-key constraints: "device_platform_id_fkey" FOREIGN KEY (platform_id) REFERENCES platform(...
0
votes
0answers
21 views

Postgresql relation from array column to multiple ids [duplicate]

I`m trying to create a simple postgresql database for payments accounting. Here are sql codes for several tables in my db. CREATE TABLE "buh"."campaign" ( "id" SERIAL NOT NULL, "campaign_state_id"...
4
votes
1answer
34 views

Oracle SQL 12c How to correctly use compound foreign keys in compound primary keys?

DISCLAIMER: I am a student. I do not work for an airline. I do not pretend to be a professional programmer. Greetings, I want understand what I need to correct in order for these tables to work ...
0
votes
2answers
37 views

How to find referencing tables? [duplicate]

I'm on SQL Server 2014 and I have a User table: +----+------+-----+ | ID | Name | ... | +----+------+-----+ | 11 | John | ... | | 12 | Jack | ... | | .. | .... | ... | +----+------+-----+ And I ...
0
votes
0answers
28 views

Foreign keys not trusted but they were not disabled [duplicate]

Is there any other way that a foreign key would be marked as "is_not_trusted" besides disabling them and re enabling them incorrectly? They were created without the NOCHECK option initially (meaning ...
0
votes
0answers
34 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
34 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
35 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
47 views

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
83 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 ...
4
votes
1answer
165 views

Storing Friendships: Should I use a multivalued column to retain Friend Identifiers?

Say I have a table called UserFriendList, which has the columns: Id (set as Primary Key) UserId FriendListIds And let us suppose that said table holds the following data: +----+--------+--------...
2
votes
1answer
39 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
27 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
45 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
4answers
122 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
47 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
26 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
30 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
32 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
46 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
39 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
111 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 ...
1
vote
1answer
43 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
107 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
42 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
26 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
84 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
58 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
129 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
56 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
454 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
169 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
84 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
89 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 ...