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
1answer
103 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
1answer
23 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
83 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
0answers
20 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
48 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
73 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
57 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
50 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 ...
2
votes
1answer
47 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
8k 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
503 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
21 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
88 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
73 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
191 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
30 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
52 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
50 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
40 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
51 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 . | . ...
1
vote
1answer
230 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
85 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
53 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
107 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
38 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
120 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
0answers
44 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
33 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
40 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
113 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
63 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
261 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
85 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
681 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
28 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
27 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
359 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
52 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
49 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
41 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
139 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
997 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
24 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!
1
vote
0answers
69 views

MySQL - Cannot add new Foreign Key

I'm using magento and it does some re-index for a product flat table structure. In this process a new table is created. The SQL for that table is something like: CREATE TABLE ...
0
votes
0answers
64 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 ...
2
votes
1answer
404 views

Foreign Key NULL vs Foreign Key to an empty string

This is a table for a catalog of medicines. Some have a pharmaceutical brand, others are generic (i.e. they will never have brand information) CREATE TABLE medicine ( id serial PRIMARY KEY, ...
0
votes
1answer
137 views

Dropped connections, corrupted schema … is MySQL sick?

Lot's of problems and I wonder if it's all tracable to some root cause. I'm trying to load a simple schema related to product marketing. The symptoms are: Not being able to forward engineer said ...
1
vote
1answer
94 views

How to write a query which recursively returns all foreign key references to a column?

I need to obtain a list of foreign keys which refer to a given column, as well as all foreign keys which refer to those keys, and so on. Order does not matter. I have SELECT s.name, t.name, ...
0
votes
1answer
33 views

Advisable number of foreign keys in a single table [closed]

I was wondering, keeping performance and optimizations in mind, while designing the database, is there any advisable number of foreign keys you should keep in a single table? At present, few of my ...