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
1answer
14 views

insert error foreign key permission

I have a problem with permissions, and I don't find where I messed up (v 9.4) I have a database DB1 owned by Bill, with 2 schemas pat and tom. Both schemas are identical , pat is owned by pat, tom by ...
0
votes
0answers
29 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 \ ...
1
vote
2answers
218 views

Multiple relationships between the same entities

Is there a name for when 2 entities can have multiple relationships between them?
0
votes
1answer
898 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
1answer
128 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 ...
1
vote
0answers
14 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 ...
0
votes
0answers
30 views

How to interact with a foreign key in database [migrated]

I have been working on a database for sometime now and i got stocked a particular section. I am trying to write a code to control the foreign key column in a database. mysql is the back-end and Java ...
1
vote
1answer
251 views

Adding foreign keys constraint on databases used

We have a server with about 20 databases, all duplicates of the same original db, and we just noticed the original is missing all the foreign keys constraint for some unknown reason. So of course all ...
0
votes
0answers
30 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
2answers
58 views

In mysql, find all instances of a named column that are not foreign keys

This one has had me trumped for a while! I want to run a query to retrieve all instances of a column (from information_schema, I guess) that ARE or ARE NOT foreign keys. Now, let me back of a little ...
0
votes
1answer
37 views

How to enforce that one row in a parent table MUST be associated with at least a row in a child table [duplicate]

I have a one to many relationship enforced via FK, classic example order and order_item, whereby a fk on the order_item to the order table enforces that one item belongs to one order, and one order ...
0
votes
1answer
34 views

Index cleanup/usage help

My question is this: When running sp_blitz, I get a lot of these 2 warnings/suggestions: Index Hoarder: Unused NC index and Feature-Phobic Indexes: Potential filtered index (based on column ...
0
votes
0answers
12 views

Does foreign key and cascade take advantage of filtered index?

I have a table T2 referencing another table T1 The referring column ref_T1 has a nonclustered unique index on it, filtered for value <> 0 When I delete a few rows from T1 and the forein key ...
2
votes
1answer
40 views

Is there any point in specifying the precision and scale of a number for a foreign key?

I am running Oracle 11gR2 11.2.0.3. I have two tables: table1 contains a column named SUBJECT_ID, whose datatype is NUMBER(7,0) (numbers range from 13 to 48480) table2 contains a a column named ...
1
vote
3answers
4k views

Difference between On Delete Cascade & On Update Cascade in mysql

I have two tables in MySQL database- parent, child. I'm trying to add foreign key references to my child table based on the parent table. Is there any significant difference between ON UPDATE CASCADE ...
0
votes
0answers
48 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
14 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
1answer
27 views

can't create foreign key in sqlite

I'm trying to run this code in sqlite: sqlite> CREATE TABLE test(id integer primary key AUTOINCREMENT, page varchar(10), dp_record_id integer, foreign key (webserver_id) REFERENCES ...
1
vote
1answer
50 views

How to get a surrogate key value in order to put a foreign key in and create relationship?

I am trying to create two relationships between three tables, using foreign keys with values of primary keys, which are surrogate keys. But I don't understand how to enter the right value in the ...
3
votes
1answer
161 views

Enforcing constraints “two tables away”

I ran into some trouble modeling an electrical schematic in SQL. The structure I'd like to capture is part ←────────── pin ↑ ↑ part_inst ←───── pin_inst where "inst" is short ...
0
votes
0answers
20 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
37 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
1answer
44 views

Query perfomance with multiple JOINs on MyISAM-based DB

I'm working with Enron Email Dataset in MySQL database. After I executed data import from the dump file into MyISAM-based DB, I got the set of tables without any connections between them via foreign ...
0
votes
0answers
35 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
2answers
29 views

MySQL - Creating an associative table

I have the following table structures: Cases: (PK) case_id | case_notes | time 1 | blah | timestamp 2 | blah | timestamp 3 | blah | timestamp . | . ...
0
votes
1answer
78 views

How to temporarily disable foreign keys in Amazon RDS PostgreSQL?

I'm migrating existing test environment to Amazon RDS PostgreSQL. The test framework has a feature of reloading data in certain tables to an earlier state. For this it disables foreign keys, deletes ...
1
vote
2answers
59 views

Oracle: finding rows without children in a table with a foreign key?

Suppose I have two tables with a foreign key relationship. How can I find the rows in the parent table for which there is no corresponding child record? For example: create table a(x number primary ...
0
votes
1answer
36 views

Oracle: Traversing a graph of my foreign keys?

Given a table, what is a query that will show foreign key constrains, both immediate and nested? I.e. if T2 has an FK on T1, and T3 has an FK on T2, I would like to show T1 -> T2 -> T3.
0
votes
0answers
43 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
25 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 ...
2
votes
2answers
63 views

Unique across tables

I have a table containing users. Each user has a primary email and a flag indicating if the user is deleted or not (we never hard-delete users). However, each user can also have additional emails. ...
0
votes
1answer
48 views

Can't add a foreign key constraint in a table on Postgresql

I'm trying to add a foreign key of "prefectures" table in "centresdevote" table. But I'm having this error while trying to add a foreign key to "centresdevote" table. I had no problem adding foreign ...
0
votes
0answers
26 views

Why are foreign keys ignored on subscribers?

We have a merge replication set up 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 the "User" and ...
0
votes
1answer
28 views

Yet another “ERROR 1005 can't create table” question

Well, I've been looking at the ERROR 1005 questions, but I haven't been able to see my error among the issues addressed by those questions. This code is generated by MySQL Workbench under Linux. Can ...
0
votes
1answer
34 views

Get table name by foreign key

I have a two tables in the database. First table has a foreign key to second table. Can I get second table name by the foreign key from first table?
2
votes
1answer
51 views

Is it possible to create a foreign key with a condition?

I have a table site that has a field categoryid_root and a table category which has the fields id and parentid I want to create a relationship between: site.categoryid_root and category.id but with ...
0
votes
0answers
40 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
1answer
93 views

ON DELETE CASCADE not deleting entry for foreign key

I am running the below query in ideone CREATE TABLE AUTHOR ( AUTHOR_ID NUMBER(5) PRIMARY KEY, AUTHOR_NAME VARCHAR2(18) ); INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1234, 'MC GRAW HILL'); ...
-1
votes
2answers
74 views

Question about choosing primary key

My scenario is that I have a super type named PERSON and two sub types EMPLOYEE and CUSTOMER. I use the Primary key from PERSON, PersonID, in both EMPLOYEE and CUSTOMER as foreign keys. However, I ...
1
vote
1answer
332 views

#1025 - Error on rename of table errno: 150 : Table was deleted while tried to assign foreign key

When I tried to assign foreign key to a table named mydb.table1 it was deleted from my database, I could not understand the reason and tried to move on. For this tried to create same table with create ...
0
votes
0answers
24 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
2answers
26 views

Check attribute table referenced foreign key

I have two tables a and b like this: a: id, type b: a_id, ... There is a limitation for which the references of a in b can only be those which have type equal to a limited set of values. Is it ...
3
votes
2answers
332 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 ...
0
votes
1answer
19 views

postgres finding child tables for particular table

I want to find all child tables corresponding to particular parent table. SELECT * FROM information_schema.table_constraints tc right JOIN information_schema.constraint_column_usage ccu ...
0
votes
1answer
34 views

How can a primary key be inserted as foreign key?

Hi I am building an order intake form that will have information submitted belonging to 3 tables. The order table will have the primary key used for the property and customer table. How can I ...
8
votes
3answers
1k views

Is it bad idea to create foreign keys on the tables in a different schemas in the same database for big applications?

I am working on the transfer a big pl/sql web-based application to the dedicated server. This application is located in one schema with 70 packages of programm code. This application was made ...
1
vote
0answers
34 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
2answers
93 views

Mysql might have too many indexes

I am using the php framework Laravel for my application, together with mysql 5.6, in order to have full-text search with innoBD. But I am not sure if I have too many indexes. My application allows ...
0
votes
2answers
930 views

MySQL cannot create foreign key, errors 1005, 150

I'm trying to add a foreign key to a MySQL InnoDB table, linking a BIGINT with a table within the same database. I'm using MySQL Workbench to do this. It generates a script: ALTER TABLE ...
0
votes
0answers
17 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!