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
0answers
23 views

Transactional Replication - Replication Order of Related Tables

** This was previously asked on Stackoverflow - I removed it from there and recreated here ** I have an issue with SQL Server Transactional Replication. My topology is as follows (and this cannot be ...
0
votes
1answer
17 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 ...
1
vote
1answer
34 views

How to insert values into a junction table?

I add values into my two tables using the code below, but I also have a junction table called book_main_cover and I want to add data into this table to make a connection between book_id and ...
-1
votes
1answer
21 views

Problem in adding foreign key

I'm having an error while adding a foreign key constrain to my database. In my database, I'm having a table named user, student, staff, complaint and review. The table user contains fields such as ...
0
votes
2answers
35 views

insert inserted id to another table

Here's the scenario: create table a ( id serial primary key, val text ); create table b ( id serial primary key, a_id integer references a(id) ); create rule a_inserted as on insert to a do ...
0
votes
1answer
17 views

can I use many-to-many relation with weak entity

I have an entity, for example employee(key employee ID), and every employee can have multiple responsibilities. Every responsibility can be tasked to multiple employees. I have 3 options: 1) Create ...
0
votes
0answers
10 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 ...
1
vote
1answer
18 views

Relation between tables [closed]

This question may be too easy to ask, but I can't understand how to do it. I am new in SQL. I have two tables. Table I book_id (AI & Primary Key) book_name book_year Table ...
0
votes
1answer
24 views

Can I use `on delete set null` on a compound foreign key which contains a primary key column?

The docs say: Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the ...
0
votes
1answer
60 views

Create relationship between two tables with composite primary key

I've created a table [t1] with two fields and a composite primary key, show in below: create table t1( id int, name nchar(10), primary key(id,name) ); I want to create a second table ...
0
votes
3answers
45 views

Reset Key Names to Defaults?

I'm working with some tables/columns that were renamed at some point, but the names of the primary/foreign keys are still based on the previous names. In SSMS is there any (preferably automatic) way ...
2
votes
3answers
215 views

Cascade primary key update to all referencing foreign keys

Is it possible to update a primary key column value with cascading the update among all the foreign keys referencing it ? # EDIT 1: When I run followinq query select * from sys.foreign_keys where ...
2
votes
1answer
155 views

Column name or number of supplied values does not match table definition error

I have a table, with two foreign keys defined on two columns of that table. The problem is that, when I insert the values from SQL Server, I get an error: Column name or number of supplied values ...
4
votes
1answer
344 views

One foreign key for two columns

I have two tables, Results and Teams Teams has the following columns: team_id team_name Results has the following columns: match_id home_team (foreign key references teams [team_id]) away_team ...
3
votes
1answer
81 views

Normalized Data Store - Confused with prefixes to use

I'm designing a Staging+NDS+DDS Data Warehouse system, where an ETL is going to normalize data from [Staging] and load it into [NDS], which will hold all history. I've pretty much finished the T-SQL ...
-1
votes
1answer
23 views

Foreign keys can't be added because they exist but they don't exist!

I have a conundrum. I am re-engineering a sqlserver 2005 database, and in the course of doing so have attempted to correct a simple lookup table flaw between tblUsers, tblUserRoles, and tblRoles ...
0
votes
1answer
39 views

MySQL #1005 - Can't create table 'startet' (errno: 150)

Can anyone figure out why phpMyAdmin won't create the last table? It works fine if I drop the last foreign key, but I need it in there to get my assignment approved... CREATE TABLE nasjon ( ...
0
votes
0answers
27 views

INSERT missing FK rows at the same time

I found on this site the following SQL-Query to perform an insert (see this post): WITH sel AS ( SELECT val.description, val.type, f.id AS foo_id FROM ( VALUES ('testing', ...
0
votes
1answer
27 views

multi-column FKs with NULL values

I understand that in PostgreSQL if there is a multi-column FK from table B to table A and one or more of the FK columns in table B are nullable then the FK is still considered as "honored" for the ...
0
votes
0answers
30 views

How to insert a row which contains a foreign key in mysql

Here's the original question regarding PostgreSQL. I'd like to know of a similar solution for MySql. The simplest syntax provided in the answer works fine as long as the relevant rows already exist ...
0
votes
1answer
15 views

MySQL: Is there a way to update a field so that all foreign key related fields are updated as well?

I'm assuming the answer to this question is yes but it is hard to Google this type of question and not get overly ambiguous results. I am working with a database that was dumped from a Magento ...
1
vote
1answer
29 views

is there some way to give me the order in which inserts should be made so all FK constraint are met? [closed]

I have a mysql 5.6 schema with about 60 tables. I need to write some code (not sure if it will be Sql or java) to populate the data. The schema has lots of FK etc. Is there some good way (I am ...
0
votes
1answer
26 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 dump-file into MyISAM-based DB, I got the set of tables without any connections between them via foreign keys. ...
2
votes
0answers
74 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 ...
11
votes
2answers
1k views

Why would a table use its primary key as a foreign key to itself

Looking through a database, I came across a table that used its primary key as a foreign key to itself. I've seen that a table can have a foreign key to itself to build a hierarchy structure, but it ...
0
votes
1answer
22 views

Which foreign keys do I use if they are repeated

I have the following two tables company_relationships |ID|Company_ID|Affiliate_ID|<Relationship Info>| +--+----------+------------+-------------------+ jobs |ID|Company_ID|<Job Info>| ...
1
vote
2answers
56 views

Insert New Foreign Key Row for new Strings

Lets say I have a table new_data like this: ndx | someval | somemachine serial | integer | integer ---------------------------- And let's say I have a reference table new_data_someval_ref: ...
0
votes
3answers
72 views

Can i have one foreign key to both parent and it's childs in postgresql?

I want to have a foreign key to reference table and it's children. Here's how I tried and failed: CREATE TABLE parent ( id serial, CONSTRAINT parent_pk PRIMARY KEY(id) ); CREATE TABLE child ( ...
1
vote
1answer
52 views

Foreign Keys to tables where primary key is not clustered index

I'm focusing on SQL Server here, but I think this is a database agnostic question, really. I have a table whereby a Guid is logically the primary key. However, uniqueidentifiers apparently make ...
0
votes
2answers
50 views

One field with multiple values?

I know that what I'm asking can't be done or shouldn't be done, but I really don't know how to do it. This is the problem: 2 tables: - Drivers (id_driver, name, etc...) - Trucks (id_truck, model, ...
0
votes
2answers
85 views

VARCHAR primary key - MySQL

Currently, I have a categories table with 2 columns - category VARCHAR(50) NOT NULL PRIMARY KEY and parent VARCHAR(50). The parent column is a foreign key (FK) to the category column. This seems like ...
0
votes
1answer
45 views

create foreign keys for pivot tables

I'm trying to optimise and speed up a mysql 5.6 datatabase. I have two tables with a many to many relationship: Reservation and Resource. Each Reservation can have many Resources and each Resource ...
0
votes
3answers
48 views

Mysql Create table shows ERROR 1005 errno 150

I am trying to make a junction table, but I getting a ERROR 1005 errno 150 CREATE TABLE 61furiousFistPokemon( cardNumber int NOT NULL PRIMARY KEY, pokemonName VARCHAR(12), type VARCHAR(10), stage ...
1
vote
1answer
32 views

Help on MySQL joins

I'm building a system to monitor currency prices. Part of my database structure looks like this: My system is trying to pull the api ticker for each exchange with this query: SELECT distinct ...
0
votes
0answers
42 views

insert record to child table which doesn't map to parent table - possible?

Actually I've 2 relevant questions: insert record to child table which doesn't map to parent table - possible? ON DELETE NO ACTION - when a record in parent table is deleted, it should affect ...
1
vote
2answers
29 views

Suggestion for Tooth Chart Data Structure

Hello I'm not sure if this is an OK question here but here goes, I'm quite confused on how am I going to make the data structure of my tooth chart database. I had first this thought of making a data ...
-1
votes
1answer
20 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) ...
9
votes
2answers
289 views

Clustered columnstore indexes and foreign keys

I am performance tuning a data warehouse using indexes. I am fairly new to SQL Server 2014.Microsoft describes the following: "We view the clustered columnstore index as the standard for storing ...
0
votes
2answers
63 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
29 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 ...
0
votes
0answers
26 views

how to disable and enable foreign key constraints for a table in sql server [duplicate]

can i disable the foreign key constraints for temporary time in SQL SERVER 2014? and how to enable the same constraints back? also i don't want to enable or disable constraints for all tables. i want ...
1
vote
1answer
533 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
96 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 ...
0
votes
4answers
90 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 ...
3
votes
2answers
201 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
437 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 ...
1
vote
1answer
68 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 ...
0
votes
0answers
15 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
2answers
101 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
24 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 ...