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
votes
1answer
73 views

alternative foreign key syntax

in mysql you can assign primary key constraint to a column using two different syntax: after the column is defined, separated by coma, optionally in a new line id int auto_increment, primary key(id) ...
0
votes
2answers
156 views

How to I establish a 1 to 1 relationship

I have a table Nurse: CREATE TABLE [dbo].[Nurse]( [EmployeeId] [int] IDENTITY(1,1) NOT NULL, [WardId] [int] NOT NULL, CONSTRAINT [PK_Nurse] PRIMARY KEY CLUSTERED ( [EmployeeId] ASC ...
1
vote
0answers
143 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 ...
3
votes
2answers
300 views

Foreign key without referenced columns specification

I noticed in the SQL Server documentation that the list of referenced columns is not a required parameter of a foreign key constraint: <column_constraint> ::= [ CONSTRAINT constraint_name ...
2
votes
1answer
8k views

Composite primary key from multiple tables / multiple foreign keys

I want to build a composite primary key made up of foreign keys from two different tables. I'm still pretty green to this... A great answer was given to building a composite key from multiple foreign ...
5
votes
1answer
598 views

Should an INSERT cause an exclusive lock on a foreign key?

I'm working on a deadlock issue. Process A is doing a simple INSERT into TableA which has a FK to TableB Process B is doing a complicated SELECT on a join that includes TableA and TableB I'll ...
8
votes
1answer
6k views

Many to Many and Weak Entities

I have an entity that cannot exist without being defined by another, and I want this entity to participate in a many-to-many relationship. Example: An artist has an album (the album cannot exist ...
3
votes
1answer
3k views

Why dropping all foreign keys from a script is not working? [closed]

I have sysadmin rights on a SQL Server 2008 R2 box. I found a script that can drop all foreign keys and re-add them for us when we need to after a testing import. So I execute the below code with no ...
0
votes
4answers
124 views

SqlServer: can one table's PK be the FK of other two table's? [duplicate]

I have three tables project, users and proj_user. The PK of proj_user table is the FK of project and users table's pk e.g.(project_id,user_id) My question is, is it possible for the project_id and ...
2
votes
3answers
639 views

What is the optimal solution for converting a database schema?

I actually want to get rid of the existing constraints in the database which I know sounds crazy but we are upgrading our application to a new framework which relies on the foreign key constraint ...
1
vote
1answer
101 views

“Transitive” keys

I have a central table, A, which will have a key that I'll call A_ID. Now, I have a table B in a "one to many" relationship with A (many B's map to one A). Each link will, of course, have a link ...
1
vote
0answers
19 views

MySQL - Foreign Key Problem [duplicate]

I'm trying to get to grips with Foreign Keys in MySQL (5.6), and can't figure out why the below doesn't work: CREATE TABLE `test`.`parenttable` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, ...
2
votes
1answer
1k views

Retrieving all PK and FK

I have a big database that I need to extract all primary keys and foreign keys from each table. I have pgAdmin III. Is there a way to do this automatically and not go over each table manually?
3
votes
2answers
673 views

Postgresql - Do Foreign Key Constraints Automatically Create Indexes?

Per the PGSQL 9.3 docs: Because this is not always needed, and there are many choices available on how to index, declaration of a foreign key constraint does not automatically create an index on ...
2
votes
0answers
42 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 ...
0
votes
0answers
60 views

Adding foreign key to MySQL does nothing

I have two systems: a dev system and a production system. On the dev system, I added a foreign key to a table using this statement, which has been created by phpMyAdmin: ALTER TABLE ...
3
votes
2answers
610 views

multivalued weak key in ER database modeling

I was wondering since i didnt find out any clarification for this. I want to store movies that exist in different formats (dvd, bluray etc) and the price for each format differs from each other as ...
4
votes
2answers
5k views

How much do foreign keys affect performance?

I'm reviewing an SQL Server 2008R2 installation having large performance problems. Currently I'm looking into its indexes and foreign keys and have found out that none of the tables have any foreign ...
0
votes
1answer
792 views

deleting a table referenced by many foreign keys

I have a table that I want to drop and create again, because I want to add the IDENTITY property to a column, wich is not possible directly. The problem is that I can't drop it, because my column is ...
4
votes
4answers
21k views

Recursive self joins

I have a comments table, which can be simplified down to this: comments ======= id user_id text parent_id where parent_id is nullable, but might be a key for its parent comment. Now, how can I ...
2
votes
1answer
135 views

Foreign key, key split in two tables

I have to record a list of events made of action, reason (optional, may be multiple), user. I have a master table with all the valid action, and another with all the valid action-reason combination. ...
5
votes
2answers
3k views

Partitioning a Table Referenced by a Foreign Key

I have one table: CREATE TABLE [dbo].[entry] ( [ID] [bigint] IDENTITY(1,1) NOT NULL, [EntryDate] [datetime] NOT NULL, [createddate] [datetime] NOT NULL, CONSTRAINT [PK_Entry_ID] ...
1
vote
0answers
165 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 ...
8
votes
2answers
249 views

Should I add transitive foreign keys?

Simple example: there is a table of customers. create table Customers ( id integer, constraint CustomersPK primary key (id) ) All the other data in the database should link to a Customer, so ...
10
votes
3answers
2k views

Foreign keys - link using surrogate or natural key?

Is there a best practice for whether a foreign key between tables should link to a natural key or a surrogate key? The only discussion I've really found (unless my google-fu is lacking) is Jack ...
1
vote
0answers
20 views

Can I include extra columns in a m->m mapping table? [duplicate]

I guess this is sort of an odd question, but consider this scenario. table classes (id_pk) table students (id_pk) table students_classes_map (students_id_fk_pk, classes_id_fk_pk, finalExamScore ...
0
votes
0answers
205 views

Same column relational Id to multiple tables

Is it appropriate to use a column as a relational id to multiple tables? For example: referenceId - int relational id to various tables tableReference - String defining the table This way I can ...
2
votes
1answer
1k views

size of a primary key and foreign key

Do a primary key and a foreign key referencing it must have the same column size in sql server 2008 ? could someone indicate me a link to a documentation explaining this ? Thank you for help.
2
votes
2answers
388 views

Should I drop a foreign key if I use a soft delete?

"ON UPDATE" scenario never happens in my application. As soft delete is used, "ON DELETE" cascade and restrict scenario never happen as well now. Then, should I just drop foreign key because I have ...
2
votes
1answer
662 views

MySQL InnoDB table accepts Non-Existent Foreign Key

Using EasyPHP 14.1 VC11 and phpMyAdmin (4.1.4) to create a website project in my home computer. The MySQL version is 5.6.15 according to the EasyPHP administration page. Working on Windows 7 64-bit. ...
3
votes
1answer
141 views

Many to many unique combination

So I originally thought I was going to have a one to many relationship in a database with the following tables ENTITY_JSON_MAP ATTRIBUTE_JSON_MAP Originally I was going to have an FK in ...
4
votes
2answers
563 views

How to write a query which finds all circular references when a table references itself? (II)

I have the following schema (names changed), which I cannot change: CREATE TABLE MyTable ( Id INT NOT NULL PRIMARY KEY, ParentId INT NOT NULL ); ALTER TABLE MyTable ADD FOREIGN KEY ...
1
vote
1answer
69 views

Schema design - Reference slightly different data in different tables

I'm trying to design a schema for a MySQL database and got stuck on the following. My database stores documents which should have relations with blocks of content. create table document ( `id` ...
0
votes
0answers
57 views

Problems with foreign keys in a mysql database

I am trying to link both these tables with a foreign key. But I keep getting error message "ERROR 1452: Cannot add or update a child row: a foreign key constraint fails " the foreign key is pwalk_id ...
1
vote
1answer
30 views

Determining Homogeneity by Common Foreign Keys

I manage a MySQL database of ten or so tables. In higher code, I have a list of primary keys belonging to entries of some table A. These A entries have foreign keys to entries in another table B. The ...
2
votes
1answer
105 views

Conditionally join table

I'm new to database design and have designed a database that seems fine for the application but looks like it has a major red flag to me. I have a master table which is then joined to another table ...
2
votes
1answer
172 views

Can triggers be used to implement automated polymorphic association?

(Apologies for any bad formatting or ignorance of common practices within this community; this is my first question here.) I'm attempting to (re)design a MySQL database for a personal social ...
0
votes
1answer
53 views

Move Commonly-Repeated Fields into a new Table

I'm still at the design stage for a new system, and I've noticed there are several places where different types of object share similar fields. In this case, we have customers, offices, warehouses ...
0
votes
1answer
119 views

Batch converting indexes to keys [closed]

I have a legacy MS SQL Server database where all the primary and foreign keys were created as indexes. I am developing the new version of our software with Entity Frameworks, which I have learned ...
0
votes
1answer
43 views

Database relationship refresher

All, After several years of programming applications, I'm looking to refresh my understanding of databases. What I knew to begin with was fairly basic, and honestly, it's disappeared over time. I ...
0
votes
1answer
66 views

Query executes fast, but keeps the table locked for some time

Executing the following query is very fast (75ms), but removing the comment from the last row it becomes slow (2.5s), and the time increases exponentially with the number of rows in the table. It ...
0
votes
1answer
49 views

How to relate to information_schema

Is it possible to create a foreign key that relates to information_schema? For an application implementation, I would like to store extra column information in another table, but I want to ensure ...
2
votes
0answers
236 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 | ... | ...
2
votes
1answer
5k views

Error Creating Foreign Key in Oracle

I have 2 tables: PRODUCTS PREFIX SUFFIX ATTR1 ATTR2 ----------------------------------------------------- 001 AAA 50 100 001 BBB 20 300 010 ...
2
votes
1answer
4k views

MySQL Foreign Key Prevents Deleting Table — But Table is Empty

Why would dropping an empty table produce a foreign key error? In MySQL, I have a table with the following definition CREATE TABLE `enterprise_rma` ( `entity_id` int(10) unsigned NOT NULL ...
1
vote
1answer
151 views

More Details for MySQL's errorno:150

When MySQL can't create a table due to a foreign key constraint problem, it returns the following error Can't create table 'dbname.table_name' (errno: 150) Is there anyway to have MySQL tell you ...
1
vote
1answer
5k views

Cannot add foreign key constraint while creating table

My Error Code is 1215 which finds problem with the foreign key constraint. I know all fields are the same data types. I can't see where the problem is. Any help would be much appreciated! Thanks! ...
1
vote
2answers
12k views

Foreign Key Constraint fails

I have the following tables: // Base Scans CREATE TABLE `basescans` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(100) NULL DEFAULT NULL, `status_id` INT(10) UNSIGNED ...
1
vote
1answer
9k views

How to insert data into tables which reference each other?(MySQL)

I'm a student and right now I have a problem that I couldn't solve by myself(after searching on the Internet). I'm not going to give my «homework» because I want to learn what I'm doing but I will ...
1
vote
1answer
2k views

Slow delete caused by many foreign keys

My DB has about 90 tables. Most of the tables have an UpdatedBy and AddedBy column that have foreign keys pointing back to the user table. With even moderate amounts of data spread throughout the ...