FAQ Database Discussion Community


Django - models - how to describe a specific bidirectional relation between two models?

django,django-models,foreign-keys
I have two models: Person and Department. Each person can work in one department. Department can be run by many people. I'm not sure how to construct this relation in Django models. Here's one of my unsuccessful tryings [models.py]: class Person(models.Model): department = models.ForeignKey(Department) firstname = models.TextField(db_column='first_name') lastname = models.TextField(db_column='last_name')...

how to populate dropdown list in Django from a foreignkey model

python,django,drop-down-menu,foreign-keys
I have two models. class ArticleCategory(models.Model): category = models.CharField(max_length=255,blank=False) class Article(models.Model): title = models.CharField(max_length=255,blank=False) body = models.TextField() pub_date = models.DateTimeField(auto_now_add=True) category = models.ForeignKey(ArticleCategory,default=1) Now I have to render a template and save the form for Article model. I have a foreignKey field in my Article Model and because of that...

Printing fields only related to foreignkey in django

django,foreign-keys
I am trying to print only entries associated with the foreignkey of my models. I have a series of lists and I want to print the list name and all the items that fall under that list. My current issue is that there is a div generating for each list,...

Python SQLite3 syntax error when creating a table

python,sqlite3,foreign-keys
I'm trying to create a table with the following command: 'CREATE TABLE users_profile(user_id INTEGER UNIQUE, FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE,\ firstname TEXT, lastname TEXT, email TEXT, website TEXT,\ picture TEXT, mobile TEXT, skype TEXT, age INTEGER, residence TEXT, gender TEXT,\ signature TEXT, avatar TEXT)' I get the error:...

Rails CSV Import switch string for foreign key

ruby-on-rails,csv,foreign-keys
I have set up a CSV import for my Ruby App. Everything works fairly well but I'm running into problems when I try to upload a string that then searches for an id to input as the foreign key in the hash. My model for the CSV looks as follows:...

Django: model.ForeignKey referring to field type

django,django-models,foreign-keys
I try to build a model, e.g. Userprofile, in which fields will be chosen in the admin panel. class UserProfile(models.Model): user = models.OneToOneField(User) regfield = models.ForeignKey(Regfields) This model refers to a second model Regfields: class Regfields(models.Model): field_name = models.CharField(max_length = 256, unique=True, blank=False) field_type = models.ForeignKey(FOREIGNKEY_TO_DATATYPES) I want to choose...

How can i directly set a 'manual' foreign key using ORMLite on Android?

android,foreign-keys,ormlite
I'm facing a little problem using ORMLite. I'm sure it's a really simple problem, but can't find any solution yet. Basically, i'm trying to create a foreign key to another object using directly its key. The code looks normally should look like: public class A { } public class B...

django - “Incorrect type. Expected pk value, received str” error

django,serialization,foreign-keys,django-rest-framework
I my django-rest-framework I have the following models: Basically every ride has one final destination and can have multiple middle destinations. models.py: class Destination(models.Model): name=models.CharField(max_length=30) class Ride(models.Model): driver = models.ForeignKey('auth.User', related_name='rides_as_driver') destination=models.ForeignKey(Destination, related_name='rides_as_final_destination') leaving_time=models.TimeField() num_of_spots=models.IntegerField() passengers=models.ManyToManyField('auth.User',...

Strange “Cannot add or update a child row: a foreign key constraint fails” error

mysql,foreign-keys
I'm currently in the process of converting one of our main ID columns from an INT to a STRING in a MySQL database. The process is pretty simple, Remove all foreign keys that reference the column (you can't modify it with foreign keys in place) Modify the ID column and...

foreign key is not equal to primary key

php,login,pdo,foreign-keys,admin
So my query looks like this `$dbUser = "SELECT * FROM accaount, role WHERE Username = :username AND Password = :password UNION SELECT * FROM role , accaount WHERE Role_idRole = idRole "; $query = $conn->prepare( $dbUser); $query->bindParam(":username", $Username); $query->bindParam(":password", $Password); $query->execute();` The mistake is UNION SELECT * FROM role...

How to Select Used Record in Foreign Key Table

mysql,sql,database,data,foreign-keys
I have two tables: artist A_id A_name 1 name 1 2 name 2 3 name 3 song s_id AID s_name 1 1 name 2 1 name 3 3 name My problem is how can I select artist names from artist table only using song table. I need to get result...

Laravel: how to import a sql file through phpmyadmin after you created a schema without throwing a constraint exception?

mysql,foreign-keys,laravel-5
Hi I have the following schema code: public function up() { Schema::create('states', function(Blueprint $table) { $table->increments('id'); $table->string('acronym'); $table->string('name'); }); Schema::create('cities', function(Blueprint $table) { $table->increments('id'); $table->string('name'); $table->integer('state_id')->unsigned(); $table->foreign('state_id')->references('id')->on('states'); }); Schema::create('users', function(Blueprint $table) { $table->increments('id');...

Error “Cannot add foreign key constraint”

mysql,sql,foreign-keys,foreign-key-relationship
I am suffering the same "Cannot add foreign key constraint" that other folks around here. The table client_partners contains a relationship between users. The error is raised at creating client_partners. I have checked that users.id have the same type than client_partners.clientid and client_partners.partnerid: INT UNSIGNED NOT NULL. The configuration options...

Entity Frame Code First The number of properties in the Dependent and Principal Roles in a relationship constraint must be identical

entity-framework,foreign-keys
Database build error: One or more validation errors were detected during model generation: AccountLinkPermission_AccountLink_Target_AccountLinkPermission_AccountLink_Source: : The number of properties in the Dependent and Principal Roles in a relationship constraint must be identical. User class: public class User { public User() { AccountLinks = new HashSet<AccountLink>(); } [Key] public int UserID...

MySQL check before soft delete

mysql,foreign-keys,constraints,soft-delete
I'm using MySQL. I have a record on A table with a soft delete column: active with a value of 0. This row is linked to 11 tables. All have the same active column. I need to be sure that the record on A is deleted only if all the...

mysql error “cannot add foreign key constraint”

mysql,foreign-keys,constraints,mysql-error-1064
Here is the mysql query: ALTER TABLE wp_juices_members ADD CONSTRAINT fk_juiceid FOREIGN KEY(juice_id) REFERENCES wp_juices(id) All the tables, fields exist with the correct names as specified in the above query, and i still get this error: "cannot add foreign key constraint" Here are the DESC for the two tables: wp_juices_members:...

author = models.ForeignKey('auth.User', null=True, blank=False) trouble

django,user,foreign-keys,author
I have a table where I save data(description, x, y, result and creation date) and until now everything works. I thought then to add a column with the author for each saved line eg: DES| X | Y | RESULT |CREATION DATE| AUTHOR | hi | 3| 1 | 4...

Symfony Doctrine fails to create foreign key

mysql,symfony2,doctrine,foreign-keys
Fields in table Ordertaxi: @ORM\Column(name="custom_id", type="integer", options={"unsigned"=true}) private $customId; // new "column", which i want to add @ORM\OneToOne(targetEntity="CustomOrder") private $custom; Field in table CustomOrder: @ORM\Column(name="id", type="integer") @ORM\Id @ORM\GeneratedValue(strategy="AUTO") private $id; Executing: php app/console doctrine:schema:update --dump-sql Getting: ALTER TABLE Ordertaxi ADD CONSTRAINT FK_D345B7FB614A603A FOREIGN KEY (custom_id) REFERENCES CustomOrder (id); CREATE UNIQUE...

Django count foreign key through relation

python,django,foreign-keys
I am building a simple forum application and I need some help with counting foreign key objects via through relation. Let's say my models look like this: class Forum(models.Model): title = models.CharField(max_length=255) description = models.TextField() slug = models.SlugField(unique=True, blank=True) class Thread(models.Model): title = models.CharField(max_length=255) forum = models.ForeignKey(to=Forum, related_name='threads') slug =...

Why I cannot assign foreign key to this column?

mysql,database,foreign-keys,sqlyog
I've got a main table of users create table `users` ( `id` int (10), `first_name` varchar (192), `last_name` varchar (192), `password` varchar (96), `phone` int (10), `email` varchar (768), `date_created` timestamp ); then I have a table of accounts, with foreign key user_id referring to main table of users create...

ERROR 1005 (HY000): Can't create table 'test_schema.#sql-44c_2a' (errno: 150)

mysql,database,foreign-keys,constraints
So, I've recreated the issue I'm having with more readable code. I run this sql file in my mysql prompt: CREATE SCHEMA IF NOT EXISTS test_schema DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ; USE test_schema ; -- ----------------------------------------------------- -- Table test_schema.table_one -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS table_one (...

Laravel getRelations() returns blank

php,laravel,foreign-keys
I'm trying to query a relationship for use with an accessor with getNameAttribute this is my code <?php namespace App; use Illuminate\Database\Eloquent\Model; class Conference extends Model{ public function getNameAttribute() { return $this->getRelation('event')->name; } public function event() { return $this->belongsTo('App\Event'); } public function speakers() { return $this->hasMany('App\Speaker'); } } But...

JPA, Hibernate can I do composite primary key which one element is foreign kay @OneToMany?

hibernate,jpa,foreign-keys,entity,composite-primary-key
I would like to have in my entity composite primary key consisting of 2 columns (attributes), and have one of them to be foreign key simultaneously. I write something like this, but don't know whether it works cause foreign key is marked as generated value in IntelliJ data source @Entity...

How to assign records of a table to a foreign key

php,mysql,foreign-keys
I have two tables vehicles and markers . I took the id veh_id of table vehicles as a foreign key into markers table . I have only two records in vehicles table . I want to assign 30 records of markers table to veh_id. I have tried alot but didn't...

Symfony2 entity foreign keys

symfony2,doctrine2,foreign-keys,entity
I can't figure it out.. Why I haven't access to Country table? countryName should show Great Britain but it doesn't. This is my dump($User): My my piece of code of User entity: /** * * @ORM\ManyToOne(targetEntity="Dashboard\MainBundle\Entity\Country", cascade={"persist"}) * @ORM\JoinColumn(name="country_id", referencedColumnName="id", nullable=true) * */ private $countryId; And my piece of code...

Entity Framework relationships between different DbContext and different schemas

c#,entity-framework,foreign-keys,repository,dbcontext
So, I have two main objects, Member and Guild. One Member can own a Guild and one Guild can have multiple Members. I have the Members class in a separate DbContext and separate class library. I plan to reuse this class library in multiple projects and to help differentiate, I...

Laravel Referencing Foreign Key Data

php,mysql,laravel,foreign-keys,laravel-5
Noob question incoming.. $data = DB::table('fquotes')->orderBy('created_at', 'DESC')->get(); I have this query which is pulling all data from fquotes. Each item has a cid which is linked to a customer id in another table. How do I include a column (name) from the customers table matching each row pulled? I want...

Surrogate Keys complicate insertion?

database-design,foreign-keys,key,relational-database,surrogate-key
I often see people using artifical / surrogate keys in relational databases. Thinking about it, it seems to me that while this simplifies join-Queries, it complicates the insertion of new tuples. Take the following example: R1(a, b, c) R2(c, d, e) c is a surrogate primary key of R2, referenced...

Using name attribute for Propel joins

php,mysql,join,foreign-keys,propel
I lately started to use Propel (PHP ORM) and I love it but I have one quite annoying issue I can't resolve even with a lot of trying. I use reverse engineering to create my schema.xml, which works great until it comes to joins. Sadly for all my foreign keys...

sqlalchemy core integrity error

python,mysql,database,sqlalchemy,foreign-keys
I'm working on parsing a file and inserting it into a database, using sqlalchemy core. I had it set up with the orm originally but that doesn't meet the speed requirements for the project. My database has 2 tables: Objects and Attributes. The Objects table has a primary key of...

How to remove duplicate rows with foreign keys dependencies?

database,postgresql,exception-handling,foreign-keys,plpgsql
I'm sure this is common place, but Google is not helping. I am trying to write a simple stored procedure in PostgreSQL 9.1 that will remove duplicate entries from a parent cpt table. The parent table cpt is referenced by a child table lab defined as: CREATE TABLE lab (...

Using Foreign Keys, but showing another column (not id) during a query in MySQL

mysql,database,foreign-keys,rdbms
I have a question regarding foreign keys. I have searched for the answer and was unable to location one. I have a table 'projects' that has the column 'owner_id' which references 'managers.owner_id' as a foreign key. Would it be possible to reference 'managers.owner_id' as a foreign key, but show the...

MySQL: Alter table to set Foreign key

mysql,foreign-keys,relational-database
Although there are some similar questions about the subject, I can't find the right answer for my problem. I have 2 tables called customer and car. What I want to do is this: When I delete a customer from database, I want the car that belongs to that customer will...

How to have a relationship based on a non-key field?

java,hibernate,jpa,foreign-keys,hibernate-mapping
I have two entities as following, when I try to add items to my car table it shows following error message;therefore, it does not allow me to have more than one car with 'Auto' transmission. Error: #1062 - Duplicate entry 'Auto' for key 'UK_bca5dfkfd4fjdhfh4ddirfhdhesr' Entities: Car @Entity public class Car...

SQL Select Text for multiple foreign keys to lookup table in same row

sql,table,join,foreign-keys,lookup
I have a table similar to the following that has a history of changes to an item and holds the old and new value for a status. The status number is a foreign key to a lookup table that holds the text. I.e. 1 = 'In Inventory', 2= 'Destroyed' etc.....

Sql: Selecting from other tables

mysql,sql,database,join,foreign-keys
I have 2 tables. Player and Stats. Player has the fields: Name, Age, DOB and SSN. Stats has the fields: Tackles, Goals, Assists, and SSN. SSN is a foreign key. How can I write a query to find the stats of players with DOB >= '1994'. DOB is not a...

KDB+ / Q table creation with foreign key

database,table,foreign-keys,q,kdb
My question is about creating a table with q and using foreign keys. I know how to do it the following way q)T1:([id:1 2 3 4 5]d1:"acbde") q)T2:([id:1 2 3 4 5]f1:`T1$2 2 2 4 4) But now lets say I want to create the table with the ! operator...

Get table name by foreign key

postgresql,foreign-keys
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?...

Postgres: Migrate data with FKs to multi-database

sql,postgresql,foreign-keys,migration
I currently have a single DB with data from a number of clients. I have been tasked with writing a script that splits the data up into separate DBs per client (each DB will have the same schema). For the purpose of example, let's say the DB is filled with...

With Rails Minitest, how do tests pass but fail when retested?

ruby-on-rails,postgresql,foreign-keys,fixtures,deferrable-constraint
I'm using transactional fixtures in Minitest, my tests run successfully (and pass) when I first run them: rake test test/models/number_test.rb Run options: --seed 31462 # Running: .. Finished in 0.271344s, 7.3707 runs/s, 7.3707 assertions/s. 2 runs, 2 assertions, 0 failures, 0 errors, 0 skips However, when I immediately run the...

Django - Feeding only part of Foreignkey in models

django,forms,filter,model,foreign-keys
i'm new to django and as a exercise I want to make "home expenses". I've already did simple model and form : (from models) #models.py #these are the type (groceries/clothes/etc. ) class TypWydatku(models.Model): typ_wydatku = models.CharField(max_length=25) data_wpisu=models.DateField(auto_now_add=True) #these are the actual input with dates/prices/etc class Wpisy(models.Model): data_zakupu=models.DateField(default=timezone.now()) data_wpisu=models.DateField(auto_now=True) typ_wydatku=models.ForeignKey(TypWydatku)...

Is it safe to drop and then create the foreign key constraints inside a transaction?

sql,postgresql,transactions,foreign-keys,populate
I have a table A that references a table B. Table B needs to be populated with updated data from an external source and for efficiency I use TRUNCATE followed by a COPY. This is done even when the application is live. To further improve efficiency, as suggested in the...

Cannot add foreign key constraint SQL when building schema

mysql,sql,foreign-keys
I'm taking a intro to database class right now. For a homework assignment I was given a piece of SQL code and told to just run some queries and give the results back. The problem is I can't even build the schema given with the code my teacher gave me....

Cannot add or update a child row: a foreign key constraint fails

php,mysql,foreign-keys,one-to-many
So I've been wrestling with this for a couple of days now, googled and googled and moved things around to no avail :/. Here's the scenario: A Company can have many products. When I try to create a new company and a catalog for it, it creates the catalog but...

MySQL - indexing innodb foreign keys

mysql,database,indexing,foreign-keys
Is there an improvement in performance in indexing foreign keys in InnoDB? As far as I have read, InnoDB automatically creates an index for the foreign key. Here is the query given to me for creating the table. DROP TABLE IF EXISTS `assignments`; CREATE TABLE `assignments` ( `id` INTEGER NOT...

Why is it necessary to explicitly specify foreign keys and references in databases?

mysql,sql,foreign-keys,foreign-key-relationship
Could you please explain me why it is necessary to specify those foreign keys when creating tables? I mean, I've created two tables which have a one-to-many relationship (on the ER-diagram) but I didn't specify the foreign keys and references. I can connect the tables using the where-clause and even...

Sharing composite foreign key based on the same columns across multiple tables in MS SQL Server

sql,sql-server,foreign-keys,composite-key
I have database with multiple tables which contains information about different instruments (Currencies, Funds, Equities etc.). All instruments have Type and Id fields which represents uniquie key for instrument. And I have one more table InstrumentMeta with collects statistic information about all instruments wich has pair (Type,Id) as a primary...

Embed Foreign Key Objects in JSON Response in .NET

c#,.net,json,entity-framework,foreign-keys
I would like to embed foreign key objects into my .NET JSON responses in my API. I come from a Rails background and formatting the JSON response to include any found foreign key records is fairly simple. However, I can't seem to find the way to do this. I have...

Defining relationships across Multiple Models - Django

python,mysql,django,model,foreign-keys
I have the following relationship between three Django models: class TestCase(models.Model): ''' Define the testcase model. A testcase is a python Class which contains a set of tests ''' name = models.BinaryField(blank=False) filename = models.BinaryField(blank=True) run_flag = models.IntegerField(default=0) run_as_root = models.BooleanField(default=0) num_tests = models.IntegerField(default=0) testsuite = models.ForeignKey(TestSuite) def __str__(self): return...

Entity Framework Code First - Optional/required two-way navigation

entity-framework,ef-code-first,foreign-keys,entity-framework-6,foreign-key-relationship
I have a code first model similar to the following: public class TestContext : DbContext { public DbSet<Class1> Class1s { get; set; } public DbSet<Class2> Class2s { get; set; } } public class Class1 { public int Class1Id { get; set; } } public class Class2 { public int Class2Id...

Django admin with inlines, 1 model with 2 foreigns keys to 2 different models

django,django-models,foreign-keys,django-admin
I have 3 models total. My main model has 2 foreign keys to 2 different models. So the relationships are setup as a many-to-one. When I try to customize the admin, I cannot get it to simply allow me to edit the main character model and have the 2 inlines...

Can we refer to two different tables for one foreign key?

mysql,foreign-keys,mysqladministrator
I have these tables: table1 ----------------------- tb1_id name other stuff table2 ------------------------------- tb2_Id other stuff table 3 -------------------------------- id ref Id ->either tb2_id or tb1_id Can this be achieved from the below code ? CREATE TABLE `eloan`.`table3` ( `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, `refId` VARCHAR(45) NOT NULL DEFAULT '',...

django: if model has related models (ForeignKey) display Fields in main modelform

django,forms,foreign-keys
I have two models: class Post(models.Model): image = models.ImageField(upload_to='%Y/%m/%d') title = models.CharField(max_length=200) class Addimg(models.Model): addimages = models.ForeignKey('Post', null=True) addimg = models.ImageField(upload_to='images') I wish to add images to my Post model with the "Addimg" model which works fine so far, but now i want that when I edit my parent model...

MySQL FOREIGN KEY error, ON DELETE CASCADE

mysql,sql,foreign-keys,ddl
I have absolutely no clue why MySQL is having an issue with the second CREATE TABLE statement. CREATE TABLE User( uid INTEGER, url CHAR(100), firstname CHAR(40), lastname CHAR(40), PRIMARY KEY(uid) ); The below is the one that causes problems: CREATE TABLE Follows( uid INTEGER, url CHAR(100), PRIMARY KEY(uid,url), FOREIGN KEY(uid)...

Can a Field act as both Foreign and Primary Keys in the same table?

database,foreign-keys,relational-database,primary-key,flask-sqlalchemy
I am working with Flask-SQLAlchemy, but I guess this question cuts across different database implementation platforms. Suppose I have two table models structured as: User (with fields: user_id as PRIMARY KEY, username, email, password) and UserDetails (with fields: f_name, l_name, dob, gender, address, contact, bio and user_id as FOREIGN KEY)....

How to insert data when there are multiple foreign keys in table

mysql,foreign-keys,foreign-key-relationship
How to insert data whenever multiple foreign keys in table means a database which contains patient, Doctor, and staff Patient: an appointment must be with a patient Doctor: every appointment must have a doctor My question how to insert data within appointment and another related table like patient, doctor at...

SQL alchemy - specifying relationships when foreign key may not exist

python,sqlalchemy,foreign-keys
I need to set up two tables in a database and I'm struggling to decide how to design the tables in SQL Alchemy. Table 1 contains raw address data, and the source of the address. Raw addresses may appear more than once if they come from different sources. Table 2...

Filemaker pro 13 portal tool

foreign-keys,filemaker,portal
I am using FileMaker Pro 13. I need to add Portal to show data from related table. Some fields which I wanna add to Portal are "Foreign keys" from that related table that are linked-related to third table. Results I am getting in Portal from those fields are numbers, but...

Many-to-many relationship with compound primary key constraint

mysql,foreign-keys,many-to-many
I got a requirement for many-to-many relationship design in DBMS with other constraints. There are two tables, t1 and t2 where t1.id and t2.id are primary keys respectively. t1 and t2 has many-to-many relationship, so a natural design is to add a third table t3 with foreign keys. e.g., t3:...

MySQL - Cannot add foreign key constraint

mysql,sql,foreign-keys
Please help I'm pretty noob at this and I don't know how I should put the foreign key If anybody can help, I'll appreciate it! mysql> create table Evento (idEven int NOT NULL auto_increment, denominacion varchar(20), horaInicio time, idLugar int, cupo int, constraint PK_Evento primary key (idEven)); Query OK, 0...

Add tags to photos mysql

php,mysql,foreign-keys,relational-database
I am implementing tag based search using PHP and MySQL where each photo has multiple tags. I will use this tags to search photos. For example, when I search for a query "building", I want all the photos that have the "building" tag. Here are my tables: photos(photo_id, name, url)...

CakePHP Entity contain without foreign key

php,cakephp,foreign-keys,cakephp-3.0
I have an entity Villa, and I want this Entity to contain other Villas which have the same 'complex' (Varchar(255)). class VillasTable extends Table { /** * Initialize method * * @param array $config The configuration for the Table. * @return void */ public function initialize(array $config) { $this->table('villas'); $this->displayField('name');...

Check Constraint Referencing Unique Column on another Table

sql,sql-server-2008,foreign-keys,constraints,check-constraints
I would like to limit the available values that can be entered in a column, but the values I would like to use are in another table, which is not the primary key of that table; the primary key of that table is not included in my table. As an...

C# - EF 6 Relationships Fluent API (1:1..0)

c#,entity-framework,ef-code-first,foreign-keys,ef-fluent-api
I'm having some trouble finding out the correct way to map my Client/Person classes in the EF 6.1.3. public class Client { public int ClientId { get; set; } public int PersonId { get; set; } // FK // Navigation Properties public virtual Person Person { get; set; } }...

MySQL syntax error when creating a table with 2 foreign keys as primary key

mysql,foreign-keys,primary-key
I keep getting a 1064 error when I run the below query but I don't understand what's wrong with it: CREATE TABLE hashmkb_mangatracker.group_release ( group_id int NOT NULL, release_id int NOT NULL, PRIMARY KEY (group_id, release_id), UNIQUE INDEX (release_id, group_id), FOREIGN KEY (group_id) REFERENCES group(id), FOREIGN KEY (release_id) REFERENCES release(id)...

Multiple Foreign Keys _ has_many

ruby-on-rails,ruby,foreign-keys,destroy
In Rails, I need a model to have a has_many with two foreign keys that need to be matched to make the list. Ex: Organization_Profiles table structure id : int profile_id : int organization_id : int associations belongs_to: :profile belongs_to: :organization has_many: :notifications, foreign_keys: [:profile_id, :organization_id], dependent: :destroy Notifications table...

Hibernate foreign key is null in OrderItem (order_orderID)

java,hibernate,foreign-keys
I have two table Orders and OrderItem as below: @Entity @Table(name = "orders") public class Order implements Serializable { @Id @GeneratedValue private Integer orderID; @Column(nullable = false) private Date orderDate; @ManyToOne(cascade = CascadeType.ALL) private User user; @OneToMany(cascade = CascadeType.ALL) private Set<OrderItem> orderItems = new HashSet<OrderItem>(); public Order() { } public...

Flask foreign_keys still shows AmbiguousForeignKeysError

python,mysql,flask,foreign-keys,flask-sqlalchemy
I have two foreign keys in an entity that refer to another entity. Here is how it looks class Review(db.Model): __tablename__ = 'Review' id = db.Column(db.Integer, primary_key = True) user_id = db.Column(db.Integer, db.ForeignKey('User.id'), nullable=False) business_user_id = db.Column(db.Integer, db.ForeignKey('User.id'), nullable=False) user = db.relationship('User', foreign_keys=[user_id]) business_user = db.relationship('User', foreign_keys=[business_user_id]) and class User(db.Model):...

Create two ForeignKey to one class

python,django,orm,foreign-keys
I have this problem in this class of the model. class Transferencia(models.Model): descripcion = models.CharField(max_length=200) importe = models.FloatField() tipo = models.ManyToManyField(Tipo) cuentaOrigen = models.OneToOneField(Cuenta) cuentaDestino = models.OneToOneField(Cuenta) monedaReferencia = models.ForeignKey(Moneda) fechaApunte = models.DateTimeField('date published') factura = models.ForeignKey(Factura) def __str__(self): return "Importe: " + self.importe And I've this error ERRORS: walletGestor.Transferencia.cuentaDestino:...

Drop a foreign key from a table in mysql

mysql,sql,database,foreign-keys
I have created a foreign key without giving it a name for a column in a table. Now i want do remove that column. First i've tried removing the foreign key constraint but there was an error I've used the following sql command ALTER TABLE passenger DROP FOREIGN KEY bookedBy...

'Delete' user but keep records (foreign keys)

php,mysql,database,database-design,foreign-keys
I have a table users with user accounts (user_id, username, ...). The user_id is related to multiple other tables - e.g. a table with his last actions, profile details, his products, his interests etc. Sometimes a user wants to be deleted, and then I set a field 'deleted' to 1....

MySQL Foreign Key Constraint Fail

php,mysql,pdo,foreign-keys,sql-insert
So, to clarify things: I know why this is failing. The question is: How to work around that? This is my setup: The referencing table is users and the referencing column is id, the referenced table is user_data, and the referenced column is id. I want to store the email,...

MySQL Insert Statement foreign key auto add pk

php,mysql,foreign-keys,primary-key,sql-insert
I have a fairly complicated database set up with lots of foreign key relationships and want to know if there is an easy way to insert an entry into one of these tables. This is the table that holds some of the foreign keys. CREATE TABLE package ( id INT...

Update even though using constraints

sql-server,sql-update,foreign-keys,primary-key,sql-server-2014
I'm trying to update a value (small spelling error) in the SQL Server 2014 database. I do like this: update t_Table set funID = 'References' where funID = 'Referencies' When doing this I get the error The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_t_Table_Language_t_Table". The conflict occurred in...

Get info about foreign key on delete action

sql,foreign-keys,constraints,sybase,sybase-ase
I have a Sybase database with two tables. One table refer to another with a foreign key. I need to know - is this foreign declared with any on cascade option? I have already tried: EXEC sp_helpconstraint <table_with_constraint> but this doesn't return any on cascade information. Is there any way...

belongs_to with foreign_key not working in one direction

ruby-on-rails,foreign-keys,belongs-to
class Position < ActiveRecord::Base belongs_to :product, foreign_key: :symbol end class Product < ActiveRecord::Base has_many :positions, primary_key: :symbol, foreign_key: :symbol end When I do Product.first.positions.first I am getting a Product back. But, when I do Position.first.product I am getting nothing. When I look at the SQL generated by the query, it...

MySQL structural integrity versus simplicity?

php,mysql,foreign-keys,referential-integrity
My problem: On a PHP/MySQL application I have to distinguish the registered accounts from the simple visitors. Both of them can perform actions which are written into the database. Now I use a separate table for visitors and a separate table for registered accounts. My example solutions so far: Option...

Do we need to explicitly mention which column is foreign key column?

database,database-design,foreign-keys,foreign-key-relationship
When we create relational database tables, we have to use foreign key columns. It is obvious, otherwise we can not create relationships. However, I noticed that it is enough to have a foreign key column, you do not need to say that there is a foreign key relationship in table...

SQLiteException no such table ITEMS (code1) : while compiling: SELECT * FROM ITEMS WHERE RECID =?

android,sqlite,foreign-keys,sqliteopenhelper
I'm trying to create a table ITEMS with a FOREIGN KEY that references the id of another table UTILISATEURS... While my table UTILISATEURS works just fine, the table ITEMS is not created for some reason. The error seems to be in the following query : SELECT * FROM ITEMS WHERE...

undefined method `loan_amount' for nil:NilClass

ruby-on-rails,ruby-on-rails-4,activerecord,foreign-keys,foreign-key-relationship
I am doing a jewel loan application using ruby 2.2.2p95 (2015-04-13 revision 50295) [i686-linux] and Rails 4.2.1 in that i am having two models: 1)jewelloan.rb 2)jltransaction.rb I have added foreign key jewelloan_id to get the loan_amount in jltransactions table. Where as loan_amount is a field in jewelloans table. My problem...

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails error in Laravel 4

mysql,laravel,laravel-4,foreign-keys,eloquent
I am recieving the following error: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`database`.`messages`, CONSTRAINT `messages_sender_id_foreign` FOREIGN KEY (`sender_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE) (SQL: insert into `messages` (`subject`, `message`, `draft`, `updated_at`, `created_at`) values (test, testgdsgds, 0, 2015-05-24 16:03:59,...

Delete data from table with foreign keys as primary

sql,postgresql,foreign-keys,sql-delete
I am programming a drawing surface with elements and connections between them. My two tables for the surface look like this: CREATE TABLE element ( elid character varying(60) NOT NULL, data json, surface_id character varying(60) NOT NULL, type character varying, CONSTRAINT element_primary_key PRIMARY KEY (elid), ) CREATE TABLE connection (...

Get all Foreign Keys for All dependent tables for One Table to nth Level

sql,sql-server,sql-server-2008,foreign-keys,truncate
I have one table Called Member with Column Name Member_Id. This Table Referred by more than 23 other tables as Primary Table with Member_Id as Foreign Column. Now these 23 tables also have Primary Keys, and some serve also as Primary table for other tables. So I want to fetch...

How to fetch foreign key relationships in mongoengine

python,mongodb,foreign-keys,mongoengine
I have the following two models. class URL(mongoengine.Document): codes = mongoengine.ListField(mongoengine.IntField(required=True), verbose_name='Codes') name = mongoengine.StringField(max_length=255, required=True) class BytesForURL(mongoengine.Document): url = mongoengine.ReferenceField(URL) value = mongoengine.FloatField(required=True) timestamp = mongoengine.DateTimeField(required=True) I need to get the data of BytesForURL given a url name within a time range. How do I query for that. bytes...

ERROR 1452 (23000)

mysql,foreign-keys,innodb
I'm getting this error on my script: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (hbm248_BucherFilms1.films, CONSTRAINT films_ibfk_1 FOREIGN KEY (country) REFERENCES countries (country)) I want to use INNODB and dont' understand what is causing that error. All my foreign keys line up....

MYSQL Balance Sheet Design

mysql,foreign-keys,primary-key,entity-relationship,foreign-key-relationship
I'm working on creating an ERD Diagram to map out a MYSQL database. One of my entities in the ERD Diagram is Balance Sheet. How I have it setup right now is: Company PK Company ID Company Name Balance Sheet PK Balance Sheet ID FK Company ID Year Month I...

declare two foreign key that are also primary key

mysql,foreign-keys,innodb
i'm facing a strange problem by declaring two foreign key on a table, that are also a primary key in my table. This is my sql code: CREATE TABLE IF NOT EXISTS guest ( id_guest varchar(50) NOT NULL, PRIMARY KEY(id_guest) ) ENGINE=InnoDb DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS product...

Can the field type of a foreign key be different to the referenced primary key?

mysql,database,foreign-keys,primary-key
I get a E-R model which is not designed by me. But I have no way to contact the author. There are something confusing me in this model. In Table A, the Primary Key is an integer field. Table B has a Foreign Key referenced to the PK of A....

Dealing with import of foreignKeys in django-import-export

python,django,foreign-keys,django-import-export
I don't understand how django-import-export module deals with ForeignKeys. Here is a simple exemple : models.py class TFamilies(models.Model): id_fam = models.AutoField(primary_key=True, unique=True) name_fam = models.CharField(max_length=1024, blank=True,verbose_name='Famille') class TGenus(models.Model): id_genus = models.AutoField(primary_key=True, unique=True) name_genus = models.CharField(max_length=1024,verbose_name='nom de genre') id_fam = models.ForeignKey(TFamilies, null=True, db_column='id_fam', blank=True,...

Foreign key not updating in entity frame work

c#,.net,entity-framework,foreign-keys
I have a class like public class Document { [Key] public int Id { get; set; } public User ModifiedBy { get; set; } public DateTime ModifiedDate { get; set; } } Here “User” is a class with some properties (all are primitive types ) Here is my mapping of...

SQLite in Android - Syntax Error in CREATE TABLE

android,sqlite,foreign-keys
I have been working on an Android app for a while and have recently started getting into making a database for the app. That being said I am currently having an issue with my CREATE TABLE commands. I have run these through SQL fiddle and it doesn't seem to think...

foreign key constraints. can child table have null values? ERROR 1452 (23000)

mysql,database-design,null,foreign-keys
I have a controlled vocabulary table set up as such: -- create the film_stock table DROP TABLE IF EXISTS film_stock; CREATE TABLE film_stock ( stock varchar (10) NOT NULL, PRIMARY KEY(stock)) ENGINE = INNODB; There are only two values assigned to 'stock' - Nitrate and Acetate. 'stock' serves as a...

EF Code First and indicating a simple foreign key

entity-framework,ef-code-first,foreign-keys,entity-framework-6
Let's say I have an entity EntityA with two unique columns, Key1 and Key2. At the moment only Key1 is the PK. Now I want another entity named EntityB, to have a FK from EntityB.AKey2 to EntityA.Key2. I don't need a navigation property from one to the other, just to...

Set Foreign Key Column In Child Table To Null If Parent Foreign Key Deleted

mysql,database,sqlite,foreign-keys,foreign-key-relationship
I have an SQLite database. I have learned how to insert a foreign key and now I would like to do this: 1. Delete the row which contains the foreign key in the parent table 2. Have any other table which references that foreign key set to null. I have...

Foreign Key relationships are not making sense, is there a better way?

mysql,database,foreign-keys
I'm having an issue with trying to understand how my tables should relate in my database. I am a beginner and have tried to research on my own. The tables are below CREATE TABLE PIZZA ( pizza_id INT AUTO_INCREMENT PRIMARY KEY, pizza_name VARCHAR (255), pizza_desc VARCHAR (255), pizza_price DOUBLE );...

Python SQLAlchemy - ForeignKey error

python,mysql,orm,sqlalchemy,foreign-keys
I'm trying to learn Python ORM using SQLAlchemy. I have these MySQL tables: CREATE TABLE address ( id INT NOT NULL AUTO_INCREMENT, street VARCHAR(45) NOT NULL, city VARCHAR(45) NOT NULL, PRIMARY KEY (id)) ENGINE = InnoDB; CREATE TABLE user ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(45) NOT NULL, email...

How to display some objects of model B that has a ForeignKey pointing to model A,in Django admin?

python,django,django-admin,foreign-keys
Here's my models: class Person(models.Model): name = models.CharField(max_length = 10) class Book(models.Model): title = models.CharField(max_length = 10) auther = models.ForeignKey("Person") When I'm in a certain Person in django admin,I want to see all the Books written by this Person.How can this be achieved? Thanks in adv....

Foreign key naming conventions, ModuleID vs ModuleId [closed]

foreign-keys,naming-conventions
When we use foreign keys in table Should we name them ModuleID or ModuleId? Due to foreign keys naming conventions....

MySQL how to make a column of child table point to the parent table?

mysql,foreign-keys,foreign-key-relationship
I've just started MySQL database and i am confused with what a foreign key could do. Suppose i have a parent table (teacher) of this and taxno is the primary key; taxno Tfname Tlname Tgender Tquali Thours 1111 TOH JIM M HONOURS 120 and my childtable (teacsub); taxno Tfname Tlname...

How to Mimic Postgres Foreign Keys into a Partitioned Table

postgresql,foreign-keys,postgresql-9.3,database-partitioning
I have a partitioned table (call it A) with a serial primary key that is referenced by another table (call it B). I know that I can't actually create a foreign key from one to the other (since I don't know from which partition the data is actually stored), so...