天道酬勤,学无止境

foreign-keys

MySQL error: Cannot add foreign key constraint?

There always be the error:"Cannot add foreign key constraint" when I create my last table. System: Mac OS X 10.9 DB : MySQL 5.6.14 DBM : Sequel Pro CREATE TABLE users ( uid INT AUTO_INCREMENT PRIMARY KEY, uname VARCHAR(20) NOT NULL, uemail VARCHAR(20) NOT NULL, ucity VARCHAR(20), upassw VARCHAR(20) NOT NULL ); CREATE TABLE songs( sid INT AUTO_INCREMENT PRIMARY KEY, sname VARCHAR(20) NOT NULL, srldate DATE NOT NULL ); CREATE TABLE albums ( albid INT AUTO_INCREMENT PRIMARY KEY, albname VARCHAR(20) NOT NULL, albrldate DATE NOT NULL, albrltime TIME NOT NULL ); CREATE TABLE artists ( aid INT AUTO

2021-09-24 16:16:24    分类:问答    mysql   sql   foreign-keys   constraints   mysql-error-1064

Creating relationship to non-primary column

I have a table which references a foreign key to its main table. But I want to add an other reference to another table. For instance, let's suppose I have three tables: Child, Parent, Transaction Parent table: ParentID Child table: ChildID ParentID Transaction table: TransactionID ParentID (references ParentID on Parent table, nullable) ChildID (references ChildID on Child table, nullable) I want to add a cascade on update reference to ParentID on Child table. So that when a relationship changes in parent-child then my ParentID on the Transaction table will be automatically updated.

2021-09-24 07:48:15    分类:问答    sql-server   foreign-keys

Does every column contained in a composite FK need an extra Index on it?

I usually put index on my FKs to speeds up my queries, but what if I have a grouped index on two columns which one of them is FK. Is it necessary to put index on it too? As you may know, in this condition dm_db_missing_index_details keeps suggesting one missing index. I would appreciate if anybody could tell if it's really necessary or not?

2021-09-24 06:50:57    分类:问答    sql-server   indexing   foreign-keys

Sequelize belongsToMany validation error

I'm facing a problem since 3 days. I would like to create a multiple relation n:m in Sequelize but I'm getting an error message : "Unhandled rejection SequelizeUniqueConstraintError: Validation error". So I have 3 tables : Serie. Episode. Season (in this table I would like to add relation to Serie and Episode). Here is the definition of Serie : var Serie = sequelize.define('Serie', { idSerie: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true }, name: DataTypes.STRING }, { classMethods: { associate: function(models) { // associations can be defined here } } }); Episode : var

2021-09-24 06:26:04    分类:问答    node.js   sqlite   foreign-keys   sequelize.js   sequelize-cli

添加外键时出错(Error while adding Foreign key)

问题 我正在使用 Mysql 工作台。 我已经做好了桌子。 现在我想在名为Personal_Details的表中添加外键,该键是Login表中的主键。 但是当我尝试这样做时,它显示了以下错误: ERROR 1005: Can't create table 'shaadiDB.#sql-404_25' (errno: 121) SQL语句: ALTER TABLE `shaadiDB`.`Personal_Details` ADD CONSTRAINT `Login_Id` FOREIGN KEY (`Login_Id` ) REFERENCES `shaadiDB`.`Login` (`Login_Id` ) ON DELETE NO ACTION ON UPDATE NO ACTION , ADD INDEX `Login_Id` (`Login_Id` ASC) 回答1 似乎表Personal_Details有数据,其中可能有一些带有 Login_Id 的行,其条目在表Login不存在。 如果是这种情况,那么解决方案是您需要将数据移动到另一个表,然后添加约束。 添加约束后,您需要将所有行逐一添加回表 1。 回答2 错误 121 :当键名重复时,会显式抛出此错误。 在运行您的Alter ...语句后,立即执行以下操作并观察结果。 SHOW ENGINE InnoDB STATUS

2021-09-24 06:24:49    分类:技术分享    mysql   sql   foreign-keys   innodb   foreign-key-relationship

How to make an Entity Framework property NOT NULL, but not required in form submission

I'm using Entity Framework 4.1 with a code-first model. A common pattern is that many objects reference the user who owns them, eg. public class Item { public User Owner { get; set; } } This creates a nullable column in the DB, but since every Item must have an owner I want the column marked NOT NULL. If I use the [Required] attribute then submitting the form to create an Item results in an error. That field is never set through a form, only manually in code.

2021-09-24 06:03:41    分类:问答    entity-framework   entity-framework-4.1   foreign-keys

这些外键有什么作用?(What will these foreign keys do?)

问题 我使用了一个在线 SQL 构建器来帮助设计我正在处理的一些 MySQL 表。 外键总是让我感到困惑。 我想出的代码试图添加这 4 个外键,但我想在添加它们之前确保我想要它们。 ALTER TABLE `users` ADD FOREIGN KEY (user_id) REFERENCES `settings` (`user_id`); ALTER TABLE `logins` ADD FOREIGN KEY (user_id) REFERENCES `users` (`user_id`); ALTER TABLE `mail_threads` ADD FOREIGN KEY (folder_id) REFERENCES `mail_folders` (`folder_id`); ALTER TABLE `mail_message` ADD FOREIGN KEY (thread_id) REFERENCES `mail_threads` (`thread_id`); 那么基本上什么会限制我做呢? 下面的所有 4 个表,我需要能够单独更新而不会与其他表混淆,所以我不应该对它们使用外键吗? 回答1 外键是数据库将关联分配到“父”表的一种方式。 我将从您列表的底部开始,然后逐步向上。 底部 FK 表明列mail_message.thread_id是对mail_threads

2021-09-24 06:00:08    分类:技术分享    mysql   foreign-keys

Django M2M relationships: Use just one intermediary table or one per entity pair?

When creating many to many relationships we use an intermediary table. Lets say I use the following entities video, category, tag, and VideoCategory, VideoTag to create the relations. I'm assuming that many tags/categories can have many videos and vice-versa. And I do it with through keyword 'cause I want to be able to use extra fields in the future if I want. class Category(models.Model): category = models.CharField(max_length=50) def __str__(self): return self.category class Tag(models.Model): tag = models.CharField(max_length=50) def __str__(self): return self.tag class Video(models.Model)

2021-09-24 01:26:18    分类:问答    django   database   foreign-keys   entity-relationship

SQL Server: Unable to create relationship

I was trying to create a table that has a one to many relationships. but it seems that adding a foreign key in Personal is not working. I am trying to link a Personal Information table to a address table? what is the solution for this error? Address table saved successfully Personal table Unable to create relationship 'FK_Personal_Address'. Cascading foreign key 'FK_Personal_Address' cannot be created where the referencing column 'Personal.ID' is an identity column. Could not create constraint. See previous errors.

2021-09-23 23:22:28    分类:问答    sql-server   sql-server-2008   sql-server-2005   foreign-keys   foreign-key-relationship

Implement/use foreign keys in SQLite?

How can I implement a foreign key in SQLite? I was thinking something like this: CREATE TABLE job (_id INTEGER PRIMARY KEY AUTOINCREMENT, employer_id INTEGER, ...); CREATE TABLE employer(_id INTEGER, employer_name TEXT NOT NULL, ...); Where employer_id is the _id from the table employer. Would this work? Is there another fast, maybe less prone to error way? Maybe with triggers?

2021-09-23 22:15:38    分类:问答    sqlite   foreign-keys