FAQ Database Discussion Community


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...

Django - cycle relations between models

python,django,foreign-key-relationship
I have two models Set and Photo: class Set(models.Model): ... thumbnail = models.OneToOneField('Photo') ... class Photo(models.Model): ... set = models.ForeignKey('Set', related_name='photos') ... The problem is that django can't process this code, because rendering model Set requires model Photo, which is depends from model Set, which is not available yet at...

Error while trying to cascade-delete

c#,asp.net,entity-framework,foreign-key-relationship
I'm getting the following error message while trying to delete an item from the db: The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a...

Only foreign key constraints are allowed on this table AX 2012

foreign-key-relationship,axapta,dynamics-ax-2012,dynamics-ax-2012-r3
I have created three fields in a table: Country, State and City. I have given EDT as LogisticsAddressCountryRegionId for Country, LogisticsAddressStateId for State. The relations are MyTable.Country = LogisticsAddressCountry.CountryRegionId. I am getting a best practise error as Only foreign key constraints are allowed on this table. I tried few options...

How do I make sure each post in the Post feed shown in the home page has all its comments displayed under it?

ruby-on-rails,relationship,erb,foreign-key-relationship,instance-variables
I have a Post model, and a Comment model that belongs to Post. I was able to display the Post in the home view corresponding to the home controller and home action and the User/show view. Thus, in the home and user views, the posts are listed in order of...

Alembic SQLite ALTER TABLE with self-referencing foreign key

sqlite,foreign-key-relationship,database-migration,alembic
The Alembic migration for a SQLite database: def upgrade(): with op.batch_alter_table('my_table') as batch_op: batch_op.add_column(sa.Column('parent_id', sa.String(24))) batch_op.create_foreign_key('parent_constraint', 'my_table', ['parent_id'], ['id']) which is supposed to create a foreign key parent_id referencing id of the same table my_table, creates a reference to a table called _alembic_batch_temp: CREATE TABLE "my_table" ( id VARCHAR(24) NOT...

Correctly use Eloquent relationships

laravel,orm,eloquent,relationship,foreign-key-relationship
I am developing a Laravel 4 application with 4 database tables: Therapist TherapistType Municipality County I'm using foreign keys to reference the data from other tables. The relationships are the following: A therapist can have one therapist type, municipality and county A therapist type and municipality can belong to many...

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...

RestKit 0.24 mapping using addConnectionForRelationship

restkit,foreign-key-relationship
It's been days i'm struggling with this relationship connection. Data model I have the following json: { "response":{ "pageInfo":[ ], "threads":{ "userId":"1", "threadId":"58", "status":"", "timestamp":"2015-02-03T13:24:29.000Z", "messages":[ { "messageId":"23", "threadId":"58", "participantId":"106", "text":"test", "status":"", "timestamp":"2015-02-03 13:24:29" } ], "participants":[ { "participantId":"106", "threadId":"58", "userId":"1", "user":{ "id":"1", "username":"admin", "email":"[email protected]", "medias":[ { "id":"181",...

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...

How do I filter a model field on a form that is a foreignkey but not the main foreignkey

django,foreign-key-relationship,inline-formset
I have an inlineformset_factory that is a group of ShoppingListItemForm View: class ShoppingListItemForm(ModelForm): @property def __name__(self): return self.__class__.__name__ def __init__(self, *args, **kwargs): if kwargs.get('instance'): theList = kwargs['instance'] self.fields['category'] = forms.ChoiceField(choices=[(cat.id, cat.name) for cat in ListItemCategory.objects.filter(shoppinglist__id=theList.id)]) return super(ShoppingListItemForm, self).__init__(self, *args, **kwargs) class Meta: model = ShoppingListItem fields = ('item',...

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...

Errors in the OLAP storage engine: The attribute key cannot be found when processing

ssas,foreign-key-relationship,data-warehouse,olap-cube,dimensional-modeling
I know this is mainly a design problem. I 've read that there is a workaround for this issue by customising errors at processing time but I am not glad to have to ignore errors, also the cube process is scheduled so ignore errors is not a choice at least...

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...

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...

How to use two columns in a foreign key constraint

sql,sql-server,foreign-key-relationship
I have two tables: Article Subscription In the Article table I have two columns that make up the primary key: id, sl. In the Subscription table I have a foreign key 'idsl`. I use this constraint : constraint FK_idsl foreign key (idsl) references CSS_SubscriptionGroup(id, sl) But when I run the...

MVC creating a list as a model property not being added as foreign key to DB with code first entity framework

c#,asp.net-mvc,entity-framework,foreign-key-relationship
I am starting a code first EF MVC project. Below is the code for a Message model that I am creating. Is it possible to create a tags property that is a list of tags (another model I created) like I am attempting below? public class Message { public int...

How to implement primary key / foreign key between two parse tables?

ios,parse.com,primary-key,foreign-key-relationship
Is there a way to make primary key and foreign key relationship with Parse tables? If yes, how? I've two tables: User Table: uId (primary key/string) | uName (string) | uAge (number) | uGender (string) | uLocation (string) Employment Table: eId (unique/string) | eType (number) | eSalary (number) | uId...

PhpMyAdmin: How to find the foreign key that is missing in the parent table?

mysql,phpmyadmin,foreign-key-relationship
In a mySQL database I have two populated tables. Their structures are as in the image. When trying to relate them on idArticol (in PhpMyAdmin), an error appears: "Relation could not be added". I have more than 6000 records in each table. Apparently, the error comes from a missing foreign...

postgresql foreign key syntax

sql,postgresql,foreign-keys,relational-database,foreign-key-relationship
I have 2 tables as you will see in my posgresql code below. The first table students has 2 columns, one for student_name and the other student_id which is the primary key. In my second table called tests, this has 4 columns, one for subject_id, one for the subject_name, then...

How to relate model property to same model in ASP.NET MVC?

asp.net-mvc,entity-framework,data-annotations,foreign-key-relationship,ef-fluent-api
Sometimes, you want to store who registered or created a user account. It's either the user registered himself/herself or some other user account registered him, such as Admin accounts. So, the User table would something like: public class User : Identity { public int Id { get; set; } public...

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...

Link in django admin to foreign key object

django,django-admin,foreign-key-relationship
I have a model A with a ForeignKey to a model B. In Django admin, how can I add a link in the admin page of model A next to the ForeignKey field which open the admin page of the model B ?

Creating an Eloquent Object with relation included

php,laravel,eloquent,laravel-5,foreign-key-relationship
I'm pretty much new to opps and laravel both So, to insert the values into my users and profiles table which hav OneToOne relationship, Here is how my store() method looks like public function store(Requests\StoreNewUser $request) { // crate an objct of user model $user = new \App\User; // now...

EF 6 optional FK , delete still produces error

entity-framework,foreign-key-relationship,sql-delete
So i have these two simple models public class Blog { public int BlogId { get; set; } private string _Name; [Column("sNameColumn")] public string Name { get { return _Name; } set { _Name = value; } } public virtual List<Post> Posts { get; set; } } public class Post...

Django Models.py N to N relationship, always says one object is not defined

django,django-models,foreign-key-relationship,cross-reference
I'm doing a simple test with a Django project with Academies, Teachers and Students. I'm trying this: class Student(models.Model): name = models.CharField() direction = models.CharField() city = models.CharField() telephone = models.CharField(max_length=9) email = models.EmailField() teachers = models.ForeignKey(Teacher) class Teacher(models.Model): name = models.CharField(max_length=50) direction = models.CharField(max_length=150) city = models.CharField(max_length=50) telephone =...

Laravel References 2e en 3th level

php,laravel,reference,foreign-key-relationship
I have 3 tables. Table news (hasMany Writers) id name Table Writers (hasMany News and BelongsTo Country) id Name country_id Table Countries (hasMany Writers) id name Now I want to display this in a view: Name(news), Name(Writer), Name(Country) I only can display: Name(news), Name(Writer), country_id How do I display the...

Fetch Data from ForeignKey Relationahip in Django - Filtering

django,foreign-key-relationship
I have two models: class Card(BaseModel): name = models.CharField(max_length=100, blank=True) content = models.TextField(max_length=500) mentioner = models.ForeignKey(Profile, related_name='Mentioner') class Profile(BaseModel): name = models.CharField(max_length=100) card_mention = models.ForeignKey(Card, related_name='Mentionee') When a user creates a card, he or she can "mention" another user in this card (as the mentioner). He or she can actually...

belongs_to and references actually create relations in table?

mysql,ruby-on-rails,ruby-on-rails-4,foreign-key-relationship
I am in ruby on rails 4.2. Now working with relation and try to create one-one relation ship between Instructor and Office_Assignments shown in the ContosoUniversity of .net MVC sample. Please click here to see the relation details. In ruby i am following this tutorial. I added Instructor model using...

Setting a Foreign Key using ebean in Play Framework

playframework,foreign-key-relationship,ebean
I have two model classes CustomerAccount @Entity public class CustomerAccount extends Model { @Id @Column(columnDefinition = "int(11)") public int customer_id; @Column(columnDefinition = "varchar(50) not null unique") public String customer_email; @Column(columnDefinition = "varchar(50) not null") public String password; } Customer @Entity public class Customer extends Model { @Column(columnDefinition = "int(11) unique")...

MySQL to MSSQL keep foreign key relationships

mysql,sql-server,foreign-key-relationship,database-migration
I am currently trying to migrate from an old MySQL (5.0) to MSSQL. Because I must keep the primary key relationships, I am now facing a problem. Some data inside a table begin with the id of 6102 instead of one. I can solve this by increasing the seed, which...

Trouble filtering information with relational databases in MySQL/PHP

php,mysql,database,relational-database,foreign-key-relationship
I'm working on my first project (a project management system) to understand PHP and MySQL better. I currently have 3 tables in my database, one that lists all the projects ('projects' table), one that stores the users username and password ('users' table) and then a third, small 'permissions' table that...

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...

save one to one relationship. foreign keys in both tables

laravel,eloquent,foreign-key-relationship,one-to-one
I have two tables as follow: CREATE TABLE pets( id int NOT NULL AUTO_INCREMENT, user_id int, any_data varchar(255), foreign key (user_id) references users(id), primary key(`id`) ); CREATE TABLE users( id int NOT NULL AUTO_INCREMENT, pet_id int, any_data varchar(255), foreign key (pet_id) references pets(id), primary key(`id`) ); And my models have...

Django REST Framework: Two models nested via a third one (with 2 FKs )

python,django,django-rest-framework,foreign-key-relationship
I have two main models in my Django project: class User(Model): # let's say this is the built-in User model ... class Thing(Model): ... and I have a helper model defining their relationship: class ThingUsage(Model): user = ForeignKey(User, related_name='thing_usages') thing = ForeignKey(Thing, related_name='thing_usages') role = CharField() What would be the...

JPA EclipseLink oneToMany Derived Ids Fail

mysql,jpa,eclipselink,foreign-key-relationship
Hello I'm trying to make an example of persistence of a OneToMany relationship in which I get the following error: Exception Description: Invalid composite primary key specification. The names of the primary key fields or properties in the primary key class [entitys.OrderItemPK] and those of the entity bean class [class...

“The DELETE statement conflicted with the REFERENCE constraint” while there is no data in referenced table

sql,sql-server,delete,foreign-key-relationship
I have two related tables: [GameDataGroup] with PK [Arena_GameData] with FK I try to execute query: DELETE FROM [ACP_MAIN_STABLE_DB_content].[dbo].[GameDataGroup] WHERE [key] LIKE '%' + '_test_group' + '%' And have a message: The DELETE statement conflicted with the REFERENCE constraint "FK__Arena_GameData__GameDataGroup". The conflict occurred in database "ACP_MAIN_STABLE_DB_content", table "dbo.Arena_GameData", column 'gameDataGroupId'....

Proper database model for a user feedback system (an interesting case)

mysql,database,database-design,relational-database,foreign-key-relationship
I am developing an application using PHP and Yii Framework. I've been thinking about the most suitable database structure for the given functionality and here's what I've come up with. Yet I'm not 100% positive that's how it should be done so I've decided to ask the community. App...

Add files to multiple tables M:N

sql,database,database-design,foreign-key-relationship,multiple-tables
What is The best Data model for Add multiple files to The multiple tables? I have for example 5 tables articles, blogs, posts... and for each item I would like to store multiple files. Files table contains only filepaths (not physicaly files). Example: Im using The links table, but when...

MVC-EF Reverse Engineer Code first include another table returns nulls when there should be data

c#,sql-server,entity-framework,asp.net-mvc-5,foreign-key-relationship
I was having trouble with the syntax for the .Include() and this related question got me to the point where it both compiled and ran: MVC-EF Reverse Engineer Code first include another table I use: [ForeignKey("Id")] public OtherTableModel OtherTableModel {get; set;} Both tables have the exact same primary key: string...

Laravel 5.0: Querying and displaying child objects in view from nested relationship

php,laravel,eloquent,foreign-key-relationship,laravel-5
i am trying to access nested data in my blade view, yet everything i try seems to cause one error or another. I am guessing it has to do with the nested related data being a collection, yet i thought if i looped through this i could access what i...

Many to many relationship in SQL server - To add reference in foreign table when insertion does not happen in base table

sql-server,stored-procedures,foreign-key-relationship
I have two tables User and Item. The user table will have user details and item table will have unique entries of the item. Now I am creating a many to many relationship where there is a separate table called List which references the primary keys of both the table...

Azure Mobile Services : Foreign key table objects are null in Query

azure,foreign-key-relationship,azure-mobile-services
I have two classes on my Azure Mobile Service, I am using a .NET backend and using the code first approach for my database tables. The two tables I am having issues with are Chip and PlayerChip. I can insert data and query from them individually without any problems, however...

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...