mysql填充字段数据库_填充MySQL数据库中的字段

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.

解决方案

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');

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);

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);

and you will see this error

Schema Creation Failed: Cannot add or update a child row: a foreign

key constraint fails (databaseName.major_class_br, CONSTRAINT

cc_fk1 FOREIGN KEY (Class_ID) REFERENCES class (ID)):