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
2answers
162 views

How do automaticly delete rows from other tables

I have a MySql DB. I have a table - recipes. Also have 2 tables -ingredients and instructions. both have for each entry - a foreign key to the related recipe. I want that when I delete an entry from ...
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
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 ...
0
votes
1answer
29 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 ...
0
votes
2answers
9k views

PostgreSQL - insert/update violates foreign key constraints

I am a new one in postgreSQL. I have 3 tables, one table references the other 2 table's primary keys. But I couldn't able to insert data into the Table3. See the code below : DROP TABLE Table1 ...
0
votes
1answer
617 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
4answers
102 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 ...
0
votes
1answer
74 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
185 views

Get unmatched rows for foreign key

Consider two tables: CREATE TABLE Users( id INT PRIMARY KEY, name VARCHAR(128) ) Engine=InnoDB CREATE TABLE Tags( id INT PRIMARY KEY, user REFERENCES Users(id), tag VARCHAR(128) ) ...
0
votes
2answers
2k views

Delete rows in parent table based on criteria in child table - Oracle

I have a table B with a foreign key to table A. I want to DELETE some rows in table B, and I also want to DELETE their parent rows in table A. However, the delete criteria is based on table B. The ...
0
votes
3answers
123 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 ( ...
0
votes
2answers
131 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
393 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
222 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 ...
0
votes
2answers
138 views

Primary key for link/junction table with time-dependent data

I want to link two tables in such a way that for any given time period the relationship is one-to-one, but over time the relationship can change. As an minimal example, suppose I want to list the ...
0
votes
1answer
183 views

Implementing foreign keys on generalized data model

Suppose the ASCII tree below is a UML generalized concept model. To represent these entities/classes in UML, lets assume [mammal] a super class, and the level 1 classes, [wild] and [domestic] are sub ...
0
votes
1answer
140 views

Derived value not calculating as expected using combination of FK and Triggers (MySQL)

I'm having trouble with calculating a derived value using a combination of triggers and foreign key constraints. There are 3 tables involved in this arrangement responses ------------------------ ...
0
votes
1answer
25 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
1answer
31 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?
0
votes
2answers
23 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 ...
0
votes
1answer
35 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. ...
0
votes
2answers
90 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 ...
0
votes
2answers
111 views

What's the use of assigning foreign keys on MySQL [duplicate]

Aside from indexing the value of the foreign key, what is/are the advantage of assigning foreign keys to databases? I've seen a lot of databases that does not assign foreign keys, but is relational by ...
0
votes
1answer
41 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
121 views

Foreign key double inheritance SQLITE

I'm designing a domotic system database and I'm trying to figure out how to represent two foreign keys. This is the cardinality I want. Consider 1...N Areas (rooms, halls, kitchens, etc.): Area 1 - ...
0
votes
1answer
57 views

Why use foreign keys if cascade action is not need?

I'm testing some queries with MySQL and tried to create two tables were there should be a reference between IDs. Something like: CREATE TABLE user ( id int(11) UNSIGNED AUTO_INCREMENT, name ...
0
votes
1answer
143 views

CentOS and Ubuntu differences with information_schema

I have a Vagrant VM running CentOS 6.5. My localhost is Ubuntu 12.04, both running MySQL. I have the exact same database on both (structured exactly the same, different testing seed data in both). ...
0
votes
1answer
129 views

Multiple source CASCADE DELETE behavior

I'm having trouble understanding Postgres's CASCADE DELETE behavior. If table_A REFERENCES table_B, and the column referenced could have multiple instances of the same value, will the table_A row be ...
0
votes
1answer
4k views

MYSQL - Problem with foreign keys on same table

In Mysql 5.5, I have a table, in which I store some informations about "events".. In order to have a chronological order, I made two columns that reference to the previous and to the next event. So I ...
0
votes
2answers
75 views

Foreign key for rows added later

I'm using a queue to crawl pages and track links between them. I have a table called page with a primary key called page_id. I have a table called links with two columns: link_from, link_to The ...
0
votes
1answer
25 views

Which of tables key to reference?

MySQL. There is a table users with autoincrement primary key id. There is also a table employees (every employee is a user but not vice versa). The employees primary key is userid. The field userid ...
0
votes
1answer
2k views

Foreign Key - One Child Table to Multiple Parent Tables

Maybe I'm thinking of this problem in the wrong way, but we maintain an AccessControl table that is a child to several parent tables (e.g. CompanyItems, SystemFiles, etc.). I'm trying to make sure we ...
0
votes
2answers
526 views

Naming foreign key columns

Now I hear that the best practice for naming foreign key columns is the name of the table and field (and maybe the function if there are multiple foreign keys to the same table), for example, a ...
0
votes
1answer
76 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'); ...
0
votes
2answers
148 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
32 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
1answer
50 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
70 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
1answer
29 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
1answer
82 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
182 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 ...
0
votes
1answer
52 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
51 views

Save dataset within another table best practice

I want to save one dataset within another table, this dataset should be persistent. For example Table1: FIELDS (A,B,C) Table2: FIELDS (D, DuplicateTable1) I can think of 2 possiblities: Have ...
0
votes
1answer
127 views

Implementing a simple ERD in MySQL: is the implementation below 100% compliant with the ERD?

ERD Consider the following ERD: Tables From it, I derived the following tables: Company ------------------ c_id c_name ------------------ CompanyEmail ------------------ c_id ...
0
votes
1answer
37 views

Insert / Edit Rows by Selecting FK row

When inserting or editing rows ("Edit Top 200 Rows") for a table with a FK constraint, can the editor be configured to select the foreign row, rather than me having to look up the FK?
0
votes
2answers
62 views

MySQL foreign key error

I need to update rows in a child table,but I am getting following error while I run the code, Company Table CREATE TABLE `company` ( `Company_id` int(11) NOT NULL AUTO_INCREMENT, `Company_name` ...
0
votes
1answer
219 views

Can a foreign key have a default value equal to 0?

I have a column whose default value is 0 .Can I make that column as foreign_key
0
votes
1answer
345 views

Mysql foreign key cascade delete

Does mysql have a way to delete all sub links if a row was deleted from a "parent" table? Example table setup is below, the issue I am trying to avoid is having to maintain a script that deletes the ...
0
votes
2answers
56 views

Guaranteeing a FK relationship through multiple tables

I'm using MySQL / InnoDB, and using foreign keys to preserve relationships across tables. In the following scenaro (depicted below), a 'manager' is associated with a 'recordLabel', and an 'artist' is ...
0
votes
1answer
93 views

Foreign key in MySQL

I am learning about database, I need to know something about the foreign key in MySQL. Consider the following two tables. UserGroupType. UserGroup. UserGroupType: UserGroup: In this table ...