填充MySQL数据库中的字段 [英] Populating fields in MySQL Database
问题描述
我目前在MySQL数据库中总共创建了12个表。这些表中的11个共享相关的数据,并且彼此索引。我在填充表的每个字段中的值并确保它们正确索引时遇到一些困难。我想知道是否有一种更简单的方法可以在一条记录中填充所有表中的每个字段而无需手动进行操作,而不必担心 foreign_key
与另一个的正确匹配表(索引)。查询可能可以完成这项工作,但我不确定如何构建这样的查询。
I have currently created a total of 12 tables in MySQL database. 11 of those tables share correlated data and are indexing with each other. I am undergoing some difficulties populating the values in each individual field of the tables and making sure they index properly. I was wondering if there is an easier way that I can populate each field in all tables for one record without doing it manually and having to worry for the proper foreign_key
matches with the other table(indexing). Possibly a query can do the job but I am not sure how I can build such query. Or any other suggestions specifically tying to this example.
下面是指向表设计和查询的链接,如果您想在数据库中创建表的精确副本。
Below are links to the desing of tables and a query if you will like to create an exact copy of tables in my database.
推荐答案
基于上一个问题的表格,您需要 INSERT
首先在独立表(或基表)上记录。其中一些表格是 event
, semester
, Major_Minor
,之所以称为独立表,是因为没有定义外键约束。
Based on your table from your previous question, you need to INSERT
records first on the independent table (or the base tables). Some of these tables are event
, semester
, Major_Minor
, etc. These are called independent tables because no foreign key constraints were defined.
要在独立表上插入的示例查询,
Sample Query to Insert on independent tables,
-- INSERTING records on table event
INSERT INTO event (ID, event_description, event_datetime) VALUES
(1, 'hello', NOW()),
(2, 'world', NOW()),
(3, 'stack', NOW()),
(4, 'overflow', NOW());
-- INSERTING records on table semester
INSERT INTO semester (ID, SEMESTER_NAme) VALUES
(1, 'First Semester'),
(2, 'Second Semester'),
(3, 'Summer');
-- INSERTING records on table Major_Minor
INSERT INTO Major_Minor (ID, Major_Minor_Name) VALUES
(1, 'Math'),
(2, 'Science'),
(3, 'English');
-- INSERTING records on table class
INSERT INTO class (ID, class_name) VALUES
(1, 'Alpha'),
(2, 'Beta'),
(3, 'Gamma'),
(4, 'Omega');
插入记录后,您现在可以插入
在依赖表上。这些表称为 dependent 表,因为在它们上定义了外键约束。您不能在其他表上不存在的某些字段上添加值。从属表的示例是 Major_Class_br
表,
After records has been inserted, you can now INSERT
on dependent tables. These are called dependent tables because foreign key constraints were defined on them. You can't add a value on certain fields it does not exist on the other table. Example of dependent table is Major_Class_br
table,
-- INSERTING records on table Major_Class_br
INSERT INTO Major_Class_br (ID, Class_ID, Major_Minor_ID) VALUES
(1,1,1),
(2,1,2),
(3,1,3),
(4,2,1),
(5,2,1),
(6,4,2);
如您所见, Class_ID
,并且表上已经存在 Major_Minor_ID
: class
和 Major_Minor
因为表 Major_Class_br
依赖于它们。为了进一步说明,请尝试执行以下查询,其中 Class
的值尚不存在 Class
表,
As you can see, the values for Class_ID
, and Major_Minor_ID
already existed on tables: class
and Major_Minor
because table Major_Class_br
is dependent on them. To illustrate more on that, try executing the query below wherein the value for Class_ID
doesn't exist yet on the Class
table,
INSERT INTO Major_Class_br (ID, Class_ID, Major_Minor_ID) VALUES (7,5,2);
,您将看到此错误
架构创建失败:无法添加或更新子行:外部
键约束失败(databaseName
。major_class_br
,约束
cc_fk1
外键(Class_ID
)参考class
(ID
)):
Schema Creation Failed: Cannot add or update a child row: a foreign key constraint fails (
databaseName
.major_class_br
, CONSTRAINTcc_fk1
FOREIGN KEY (Class_ID
) REFERENCESclass
(ID
)):
- 请参见SQLFiddle演示
这篇关于填充MySQL数据库中的字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!