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
0answers
50 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
260 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 . | . ...
2
votes
1answer
885 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
271 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 ...
1
vote
1answer
107 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
305 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 ...
2
votes
2answers
581 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
1answer
40 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
91 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
386 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
1answer
2k 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
120 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 don'...
1
vote
1answer
2k 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
40 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
37 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
264 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
88 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 ...
1
vote
0answers
48 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
260 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
1k 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 `MyDatabase`.`...
0
votes
0answers
57 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
105 views

MySQL - Cannot add new Foreign Key [closed]

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
77 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
753 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
268 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
38 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 ...
2
votes
1answer
474 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" ( "...
1
vote
1answer
2k 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
282 views

Proper implementation of child (derived) table PK/FK during single and multiple inheritance hard coded generalization physical data model

Below is an image of hard coded generalization UML logical model of entities. I'd like to implement these classes as tables in a physical data model. Two derived classes/entities (Class2 and ...
0
votes
0answers
92 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 ...
1
vote
1answer
155 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 ...
2
votes
1answer
107 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 ...
2
votes
2answers
686 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
168 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
345 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
1k 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
65 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
2k 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
30 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
2k 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 [...
13
votes
2answers
2k 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
3answers
50 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 ...
7
votes
3answers
20k 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 ...
3
votes
1answer
15k 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
1k 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 (...
4
votes
1answer
176 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
1k 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
128 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
40 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 ...
1
vote
2answers
181 views

Usage of foreign key when the referenced table data will not change

I have to design a table where user has 10 fields and 6 are dropdown. Some are single selections and some multiple. Dropdown data is supposed to change very rarely. For e.g., the user is presented ...