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

Use single table with foreign key or two twin tables for text entries?

I have a table answers that has several columns including the content iself such as ID | q_id | list_id | user_module_id | content | updated_timestamp The content column is a varchar since we want ...
1
vote
1answer
10k views

To drop a table, do I have to drop all foreign key constraints or can I just NOCHECK them?

I know you cannot drop a table that has active foreign key constraints. So I was wondering is it safe to: ALTER TABLE myTable NOCHECK CONSTRAINT all DROP TABLE myTable Rather than dropping the ...
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
29 views

Tricky condition for PostgreSQL UPDATE query

I need help with a PostgreSQL query. I have a Django app + pg backend, where users post interesting URLs (or links) and can then comment under such links (called replies). There are two pg tables to ...
1
vote
1answer
51 views

SQL Server Management Studio: The columns in table do not match an existing primary key or unique constraint

I have two tables: Students and Courses. I want to set a foreign key in the Students table from the couseId column to the courseId column in the Courses table. I have set primary keys to both ...
1
vote
1answer
53 views

SQL Query; Admin Log System; Foreign Keys; Retriving multiple rows form different tables at once

Table A (users) id | name | ip 52433 Yashas 1.1.1.1 512 Admin 5.6.7.8 Table B (admin_record) id | admin | time | date | reason | action 52433 512 2312352 ...
1
vote
1answer
189 views

“Merge” two rows in a Postgres table, with foreign keys

I am keeping a database of books I've read, using the following two tables in PostgreSQL: CREATE TABLE authors ( id SERIAL PRIMARY KEY, name text ); CREATE TABLE books ( id SERIAL ...
1
vote
1answer
36 views

What is the best approach to find all fields that has (Foreign Key and Primary Key) constraints in a Mysql database?

I have a mysql database that has 250 tables. I would like to run a query to retrieve: tableName ; FieldName ; ConstaintName (Primary or foreign key). I could use ...
1
vote
1answer
194 views

MySQL Dump Restoration when FOREIGN_KEY_CHECKS=0

I currently use a SaaS DBMS. Backups of my database are provided to me in the form of a MySQL dump, and I noticed that FOREIGN_KEY_CHECKS=0. I am annoyed by this because it appears that this dump is ...
1
vote
1answer
74 views

How to reference a UNIQUE KEY CONSTRAINT in parent table as a foreign key in child table?

I have created two tables, the parent in this case being "department" and "employee" being child. CREATE TABLE department ( department_id NUMBER(4) CONSTRAINT department_id_not_null NOT NULL, ...
1
vote
1answer
91 views

Should child comment be treated as separate table?

I'm building a commenting system, currently my table indicates which comment is child with parent id in 1 table, I would like to know what is the best way of doing this? Current Table Scheme (1 ...
1
vote
1answer
102 views

Foreign key constraints failure when cross updating between two databases

I have two databases (com and usa) with a table with the following schema: Create Table: CREATE TABLE `eav_attribute` ( `attribute_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT ...
1
vote
1answer
67 views

Adding restaurants in database

I wanna store some vendors (like McDonald ,etc) and it's branches in a table. a vendor may,or may not have branches. I have created two tables: 1- A vendors table which have name,logo, etc. 2-A ...
1
vote
1answer
224 views

Foreign key default value

I have a master table and another table with foreign key. I can easily add data to master table. But whenever I want to add data to the connected table, I get a message saying Foreign key doesn't have ...
1
vote
1answer
131 views

Rename PK column names when they are FK in another tables

I need to change the names on PK columns and that has relations with another tables meaning they are FK also so this is what I am doing: SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET ...
1
vote
1answer
131 views

How to get a surrogate key value in order to put a foreign key in and create relationship?

I am trying to create two relationships between three tables, using foreign keys with values of primary keys, which are surrogate keys. But I don't understand how to enter the right value in the ...
1
vote
1answer
241 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, ...
1
vote
2answers
242 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 ...
1
vote
1answer
146 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
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 ...
1
vote
1answer
322 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
vote
1answer
29 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 ...
1
vote
1answer
390 views

ON DELETE CASCADE not working

Trying to implement a simple user follower system in MySQL, using a junction table: CREATE TABLE users ( id int NOT NULL AUTO_INCREMENT, email varchar(255) NOT NULL, username varchar(25) ...
1
vote
1answer
74 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
40 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 ...
1
vote
2answers
84 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
vote
1answer
588 views

Adding foreign keys constraint on databases used

We have a server with about 20 databases, all duplicates of the same original db, and we just noticed the original is missing all the foreign keys constraint for some unknown reason. So of course all ...
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 ...
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
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
227 views

How to design to avoid two-way reference between two tables [duplicate]

I am having a problem with foreign keys between two tables. I am trying to model a relationship between members of a club and their relatives. This is a straightforward many-to-one relationship, with ...
1
vote
2answers
91 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` ...
1
vote
1answer
279 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 ...
1
vote
1answer
3k views

Allow self-referential foreign keys to be null from an alter-table

I am trying to establish a hierarchical relationship between rows in an existing table. There are two fields of interest: a not-nullable primary key field id, and a second nullable field parentId. ...
1
vote
2answers
208 views

How to normalize data en-masse?

Goal: Extract address fields from a users table into a separate addresses table linked by foreign key. I have created the structure below, and have millions of rows to deal with. I need to do this ...
1
vote
1answer
5k views

Removing foreign key when it is not named

I've created a table named 'student' as : create table student( stud_id number primary key, stud_name varchar2(30) not null, branch_id varchar2(4) references branch(branch_id), course_id ...
1
vote
1answer
632 views

Multiple foreign keys with shared columns for weak entities

this is my first question at stackexchange. I have a parent table (P) with two child (CC and CD): CREATE TABLE P ( A CHAR(3), B CHAR(2), CONSTRAINT PK_P PRIMARY KEY(A, B) ) CREATE TABLE CC ...
1
vote
2answers
2k views

Foreign key with multiple references

I have the following three tables in mysql database named "THE_COLLEGE" mysql> desc students; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | ...
1
vote
1answer
141 views

Can I place the attribute in the Entity, when the foreign key is also already present?

I am using DBdesigner Fork for generating ER diagram. I have the 'Company' as an entity for which can I place the attribute of 'idShares', despite of having the foreign key of Shares Entity in the ...
1
vote
1answer
2k views

Is there a way to skip errors in procedures and resuming procedure

When there is a unique key in a table (not PRIMARY KEY) and procedure is running, on duplicate key error the whole process will be halted. I want to resume on error and call the procedure again. ...
1
vote
0answers
43 views

My simple FK constraints appear to have been ignored. How?

I have two tables, device and platform where device.platform_id enforces the following constraint: Foreign-key constraints: "device_platform_id_fkey" FOREIGN KEY (platform_id) REFERENCES ...
1
vote
0answers
26 views

MySql Error 1451 [closed]

I am by no means a database professional, but I've come here to get some help from people who may understand what's going wrong with my database. The error occurs on this line: update team set ...
1
vote
1answer
63 views

Foreign key not working

This is a very basic foreign Key issue, I presume. Unfortunately I am not a dba so it is a bit baffling to me. I have two tables, Report and Request. The Report table has a primary key of [RP_ID], ...
1
vote
0answers
53 views

Foreign key violations detected upon UPDATE on another column

I have a table country that is referenced by many tables like so: db=# \d client Table "public.client" Column | Type | Modifiers ...
1
vote
1answer
49 views

Postgres Can't Add Foreign Key Constraint

I have a table with about 220 million records :( and I need to add a foreign key constraint. My command looks something like this: ALTER TABLE events ADD CONSTRAINT events_visitor_id_fkey FOREIGN ...
1
vote
1answer
35 views

Cities and States (a few data): single table or splitted?

I am designing a DB schema and I need to store cities and states. I expect only a few rows, let's say, 20 cities. Example: id | city | state 1 | Rio de Janeiro | RJ 2 | Niteroi ...
1
vote
0answers
37 views

Many-to-many relationship, what's the best choice for primary key? [duplicate]

In advance, I'd like to apologize if it's a duplicate question... I can't find a direct answer, probably because I'm not a native english speaker. Straight to the subject: I have 2 tables: company, ...
1
vote
1answer
25 views

Foreign key: in which table?

I made the tables blood_types and persons. Should the foreign key be created in the blood_types or in the persons table like I did? A person can have 1 blood type. A blood type can be assigned to ...
1
vote
0answers
53 views

Inexplicable differences between 2 almost identical databases on the same server

Our situation is as follows: We've got 1 MySQL (5.5.44) database server. The server hosts 2 databases. 1 database is the production database. The other is a copy database that gets copied from ...
1
vote
1answer
52 views

Indirect constraints (foreign keys amongst three/four tables) [duplicate]

I have the next layout of tables: table COMPANY: - primary key pk_company table USERS: - primary key pk_users, - foreign key (fk_company) references COMPANY (pk_company) table GROUPS: <br> - ...