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
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 ...
2
votes
0answers
41 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
0answers
22 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 ...
2
votes
0answers
170 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" ...
2
votes
0answers
73 views

Why are foreign keys ignored on subscribers?

We are using merge replication on SQL Server 2008 R2. There are many tables with foreign keys providing a logical dependence on each other. For this question, let's say there are User and File tables, ...
2
votes
0answers
113 views

Storing URLs in a database

I am thinking about storing URL's in my database, but I am not sure whether the way I use is an efficient way to do things or not. Table name : url url_id (Primary Key & Auto Increment) url_value ...
2
votes
0answers
444 views

AFTER DELETE trigger will not fire during a cascade delete

I've got two tables, corresponding to Nodes in a graph and Aisles to which they may belong. In addition, each Aisle has a Node at which it starts and ends. Aisle Table: Aisle char(4) Start_Node ...
2
votes
0answers
40 views

See indices implicitly created for foreign key constaints in MySQL

When I add a foreign key constraint to my table, SHOW INDEX doesn't show any new indices, but the overall space used by the indices in that table increases. Apparently, the index for the field I put a ...
2
votes
0answers
207 views

Is it recommended to have redundant foreign key columns?

Imagine I have the models A, B, C e D, where A have many Bs, B have many Cs and C have manyDs: Without redundance A | id | ... | ------------ | ... | ... | B | id | a_id | ... | ...
1
vote
0answers
49 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
0answers
68 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
0answers
47 views

Update all foreign keys to a different primary key

In a MySQL database, I have a table full of business information. Lots of businesses have been inserted twice due to human error, so the same information exists with two different primary keys. ...
1
vote
0answers
39 views

Compound key that only allows nulls if they are null in the referenced table

I want to create a compound foreign key constraint that only allows a null value in one of the columns if there is a matching row with a null value in the referenced table. Additionally, the ...
1
vote
0answers
44 views

Implementing a consistent mailbox

I am trying to implement something like a mailbox for short messages between users. I am using Postgresql 9.4.1 with SQLAlchemy (Python3). There is a table of users: users = Table('users',metadata, ...
1
vote
0answers
116 views

Select all foreign key relationship chains from any set of potentially gapped table names

I am using this query to find all foreign key relationships: SELECT con.relname AS child_table, att2.attname AS child_column, cl.relname AS parent_table, att.attname AS parent_column ...
1
vote
0answers
153 views

Is it best practice to include partition keys within foreign keys when referencing partitioned tables?

The large tables in our database are partitioned by an id in order to separate data and speed up IO. The table of the data is partitioned by the partition key, but the primary key is only on an ...
1
vote
0answers
4k views

getting SQL Error (1452): Cannot add or update a child row: a foreign key constraint fails (Unknown error code)

I am getting error with MySQL NDB Cluster 7.3.5 as described below: I have two tables: -User Profiles -User Services User services is a child table which contains foreign key constraint ...
1
vote
0answers
922 views

Crow Feet notation and identifying -vs- non-identifying relationships

Is it possible to distinguish between identifying and non-identifying relationships using the Crow Feet notation? Case in point: In table Book, part of the book's Primary Key is a FK to the Author ...
1
vote
0answers
266 views

Why is adding a FK constraint slow in MySQL?

I'm doing some experimentation in MySQL 5.1.53 and I've run across a case that I just don't understand. CREATE TABLE A ( `pk` INT(11) NOT NULL, `fk` INT(11) NOT NULL, PRIMARY KEY `pk_A` ...
1
vote
0answers
162 views

join performance, index vs. index+foreignkey

I'm using an orm framework that handles data (and its integrity) on top of mysql. So far I ignored foreign keys because the orm handles that just fine. I recently read that foreign keys can enhance ...
1
vote
0answers
834 views

How to represent class table inheritance (current DBMS-specific way please)?

I want to implement class either-or table inheritance (Account, CatAccountDetails, DogAccountDetails) in SQL Server. I found a recommendation for MySQL here (How do I map an IS-A relationship into a ...
1
vote
0answers
44 views

Classes and objects (but not ORD)

I am looking for help laying out a database that stores information similar to a class definition and then also instances of those 'classes' (but not referring to an ORD). The specific example is ...
1
vote
0answers
89 views

Dealing with Invisible Referencers

Using Oracle database, suppose I have a parent table parent defined in schema A, and a child table child defined in schema B. To achieve this, schema A granted references privilege on parent to schema ...
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 ...
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) ...
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 ...
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
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 ...
0
votes
0answers
26 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
0answers
57 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
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 ...
0
votes
0answers
127 views

What order do foreign key constraints run in? Why does my foreign key not work after import?

I have two tables tags and tag_memberships in a PostgreSQL database. On the tag_memberships table, I have two constraints. The first is this: ALTER TABLE tag_memberships ADD CONSTRAINT ...
0
votes
0answers
59 views

Sybase: Delete foreign key IF exists fails

I've a database, on which we will have to delete of tables/foreign keys/view. We did deleted all the required element in PowerDesigner, then we did the "Apply model change to the database". We ...
0
votes
0answers
24 views

Part of composite foreign keys as primary key

I have a table A with a,b,c as primary key. Table B reference has a,b,c as foreign keys and it wants to set its own column d and foreign key a as primary key, Is it legal? Or does it have to set the ...
0
votes
0answers
26 views

Synchronization of DB

I have 2 databases and I need to synchronize data between them. I need to delete all the data from DB1 and then insert data to DB1 from DB2. What solution will you suggest drop all the foreign ...
0
votes
0answers
225 views

Deadlocks due to foreign keys (MySQL)

We are in the process of creating a web application using mysql as a data source. We are using spring with hibernate as an ORM framework. We have hundreds of entities with relations (manyToMany \ ...
0
votes
0answers
78 views

Cleaning up orphaned metadata in information_schema

OK - so the server (Percona 5.6 in this case) 'went away' in the middle of declaring a constraint like so: alter table mytable add constraint fk_mytable_myothertable foreign key (myothertable_id) ...
0
votes
0answers
1k views

SQL Error 1452 : Cannot add or update a child row: a foreign key constraint fails

I am trying to install Zabbix by following this guide. I ran into the following problems when trying to run data.sql after running scheme.sql successfully mysql -D zabbix -uzabbix -pPassword < ...
0
votes
0answers
23 views

Postgres: Migrate data with FKs to multi-database

I currently have a single DB with data from a number of clients. I have been tasked with writing a script that splits the data up into separate DBs per client (each DB will have the same schema). For ...
0
votes
0answers
48 views

Foreign keys in MySQL Workbench

I am defining an EER model in MySQL Workbench. I have a library with multiple copies of each book and each book has its location in the library listed in the system. When I'm creating one-to-many ...
0
votes
0answers
74 views

Indexing on cached data from related table using triggers — Is there a better way?

Edit: Schema details per request from Aaron Betrand -- I'll break the schema down to the relevant fields of the two tables. Orders (ID int IDENTITY, Status char(1)) Services(OrderID FOREIGN KEY ...
0
votes
0answers
42 views

Forcing FK constraint to validated in system catalog

I have a big table (~60gigs, 35mill+ rows) that I need to add a FK to. Adding a FK directly to the table causes a validation which runs for about 30secs that I want avoid as it's a 30sec access ...
0
votes
0answers
219 views

Allow only parent DELETE cascade to delete child rows

I have a Product database with the following components: ProductOptions is a list of option names for each Product, ProductVariants is a list of variants for each Product and ProductVariantOptions is ...
0
votes
0answers
51 views

Is there a workaround for the lack of foreign keys constraint to a partitioned table in PostgreSQL?

I have a big table A (~30 million lines) at the core of my model. Another table B has a foreign key referencing A. I would like to partition A using the well-known inheritance hack. Is there a ...
0
votes
0answers
114 views

Converting Entity Relation(ER) Model to Relational Schema?

I have the following ER Model I am trying to convert this to a relational database schema but I am not sure about several things: PORT_VISIT and PORT are weak entities. How do I figure on which ...
0
votes
0answers
35 views

Get reference count of reverse foreign key relations

Image I have a simple user table like this: ID|Name ------- 12|Foo I want to know: How many reverse foreign key relations point to the user with ID 12? I mean the number of rows which are a foreign ...
0
votes
0answers
42 views

Medical History - Is this DB properly design?

Could you please tell me if this DB is properly design? Are relationships type correct? Is there anything I should delete or add? Thanks you all very much!
0
votes
0answers
70 views

How can I make a foreign key from a fragment of the primary key?

I have this table: CREATE TABLE Phones ( Brand VARCHAR(20), Model VARCHAR(20), PRIMARY KEY (Brand, Model) ); A mobile phone can't be uniquely identified just by its model (for example: HTC (One) or ...
0
votes
0answers
87 views

MySQL: Load data with FK constraint into existing tables with same ids values already used

I have these tables: table1_main table2_main table1_test table2_test table1_main and table1_test have identical structure and table2_main and table2_test also have the same structure. table2_main ...
0
votes
0answers
1k views

Mysql Workbench Foreign Key problem

I'm working on a simple company database design but i can't figure out why i can't create foreign keys for some tables. Let me give a screenshot of the design; I'm trying to create foreign key for ...