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
16 views

Get table name by foreign key

For example 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? Thank you.
1
vote
1answer
29 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
14 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
72 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
61 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 ...
0
votes
0answers
17 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
21 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
14 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
28 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
0answers
14 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 ...
1
vote
0answers
29 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, ...
0
votes
0answers
13 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!
0
votes
0answers
36 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 ...
1
vote
0answers
28 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 ...
2
votes
1answer
206 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, ...
1
vote
1answer
44 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
57 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 ...
0
votes
1answer
29 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 ...
0
votes
1answer
34 views

adding autoincrement to id already part of foreign key

I would like to add the autoincrement property for all my table's columns named id in a schema. However, most of them are part of a foreign key constraint. Is there any other way to do it without ...
1
vote
1answer
46 views

PostgreSQL: Foreign key on string column

I'm currently in a process of redesigning a legacy database. One of the tables primary key is a varchar(254). I need to create a table relating to it. So for example: create table "Item" ( ...
3
votes
1answer
50 views

Store different rows type in single table

I'd like to create a DB to store different rows type. It will be kind of event list with time stamp. So I'd like to have an Event table with relations to other tables - each table will be a row type ...
0
votes
0answers
47 views

MySQL: Load data with FK constraint into existing tables with same ids values already used

I have these tables: table1_main table2_main table1_test table2_test table1_main and table1_test have identical structure and table2_main and table2_test also have the same structure. table2_main ...
2
votes
1answer
56 views

Efficient manual cleanup of foreign keys in MySQL [closed]

I have an all-InnoDB database in MySQL (Percona) 5.6 that was previously using FK constraints. I had to give up on them as I kept running into locking issues, so I removed the constraints and added ...
1
vote
2answers
66 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 ...
2
votes
2answers
69 views

Foreign key set to cascading but fails to update - why?

As mentioned elsewhere, I am rather new to SQL and databases. Getting my hands dirty at the moment and trying all the different possibilities. I have a server running MySQL and set up the following ...
2
votes
0answers
68 views

Storing URLs in a database

I am thinking about storing URL's in my database, but I am not sure whether the way I use is an efficient way to do things or not. Table name : url url_id (Primary Key & Auto Increment) url_value ...
1
vote
1answer
57 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 ...
1
vote
1answer
112 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
64 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 ...
0
votes
2answers
104 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
24 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
74 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
19 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
42 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
355 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
749 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
1k 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
354 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
97 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
76 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
64 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
37 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
28 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
61 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
23 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
30 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
34 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
184 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 ...
12
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 ...