Foreign Key Management
Foreign key concept
If a public key is the primary key in a relationship, it is called the foreign key in another relationship.Thus, the foreign key represents the correlation between the two relationships.Tables whose primary key is a foreign key to another relationship are called primary tables, and tables with foreign keys are called secondary tables of the primary table.
In practice, the values of one table are placed in the second table to represent the association, using the values of the primary key of the first table (including composite primary key values if necessary).In this case, the attributes that hold these values in the second table are called foreign keys.
Foreign Key Action
To maintain data consistency and integrity, the primary purpose is to control the data stored in foreign key tables, constraints.To associate two tables, a foreign key can only refer to the values of columns in the outer table or use null values.
Create Foreign Key
Specify foreign key constraints when building tables
# One way to create a foreign key is to create a subtable while creating a foreign key # Grade table (id\grade name) CREATE TABLE `grade` ( `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'grade ID', `gradename` VARCHAR(50) NOT NULL COMMENT 'Grade Name', PRIMARY KEY (`gradeid`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 # Student Information Sheet #(School number, name, gender, grade, mobile phone, address, date of birth, mailbox, ID number) CREATE TABLE `student` ( `studentno` INT(4) NOT NULL COMMENT 'School Number', `studentname` VARCHAR(20) NOT NULL DEFAULT 'anonymous' COMMENT 'Full name', `sex` TINYINT(1) DEFAULT '1' COMMENT 'Gender', `gradeid` INT(10) DEFAULT NULL COMMENT 'grade', `phoneNum` VARCHAR(50) NOT NULL COMMENT 'Mobile phone', `address` VARCHAR(255) DEFAULT NULL COMMENT 'address', `borndate` DATETIME DEFAULT NULL COMMENT 'Birthday', `email` VARCHAR(50) DEFAULT NULL COMMENT 'mailbox', `idCard` VARCHAR(18) DEFAULT NULL COMMENT 'ID number', PRIMARY KEY (`studentno`), KEY `FK_gradeid` (`gradeid`), CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`) ) ENGINE=INNODB DEFAULT CHARSET=utf8
Modify after table building
# Create a foreign key by modifying the subtable to add a foreign key when the subtable is created ALTER TABLE student ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);
Delete foreign keys
Note: When deleting a table with a primary foreign key relationship, delete the subtable before deleting the main table
# Delete foreign keys ALTER TABLE student DROP FOREIGN KEY FK_gradeid; # Find that the index is still there after execution, so delete the index # Note: This index is generated by default when a foreign key is created ALTER TABLE student DROP INDEX FK_gradeid;
Database Data Management
- data storage
- data management
Methods for managing database data
- Manage database data through administrative tools such as SQLyog
- Manage database data through DML statements
- DML (Data Operation Language)
- Used to manipulate data contained in database objects
- INSERT (Add Data Statement)
- UPDATE (Update Data Statement)
- DELETE (Delete Data Statement)
INSERT INTO table name [(field 1, field 2, field 3,...)] VALUES('value 1','value 2','value 3')
- Separate fields or values with English commas.
- Field 1, Field 2... can be omitted, but add values that correspond to the table structure, data columns, order, and number.
- Multiple data can be inserted at the same time, separated by English commas after values.
# How do you add statements using statements? # Syntax: INSERT INTO table name [(field 1, field 2, field 3,...)] VALUES('value 1','value 2','value 3') INSERT INTO grade(gradename) VALUES ('Freshman'); # The primary key increases itself, can you omit it? INSERT INTO grade VALUES ('Sophomore'); # Query: INSERT INTO grade VALUE ('Sophomore') Error code: 1136 # Column count doesn`t match value count at row 1 # The conclusion is that'Field 1, Field 2...'can be omitted, but the added values must correspond to the table structure, data columns, order and quantity. # Insert multiple pieces of data at once INSERT INTO grade(gradename) VALUES ('Junior'),('Senior');
Use the INSERT statement to add data to the subject of the timetable. Use foreign keys.
UPDATE Table Name SET column_name=value [,column_name2=value2,...] [WHERE condition];
- column_name is the data column to change
- value is modified data and can be a variable, specifically an expression, or a nested SELECT result
- Condition is a filter condition, modify all column data of the table if not specified
WHERE Conditional Clause
It can be simply understood as conditionally filtering data from a table
DELETE FROM Table Name [WHERE condition];
Note: condition is a filter condition, delete all column data of the table if not specified
- Used to completely empty table data, but table structure, indexes, constraints, etc. remain unchanged;
- Syntax: TRUNCATE [TABLE] table_name;
Note: This is different from the DELETE command
Same: can delete data without deleting table structure, but TRUNCATE is faster
- Resetting the AUTO_INCREMENT counter using TRUNCATE TABLE
- Using TRUNCATE TABLE does not affect transactions
# Create a test table CREATE TABLE `test` ( `id` INT(4) NOT NULL AUTO_INCREMENT, `coll` VARCHAR(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 # Insert several test data INSERT INTO test(coll) VALUES('row1'),('row2'),('row3'); # Delete table data (delete without where condition) DELETE FROM test; # CONCLUSION: If Where is not specified, all column data of the table will be deleted and the log will be recorded as the current value increases. # Delete table data (truncate) TRUNCATE TABLE test; # CONCLUSION: truncate deletes data and restarts after increasing the current value; no logs are recorded. # Also use DELETE to empty database table data for different engines. After restarting the database service # InnoDB: The auto-increment column starts from the initial value (because it is stored in memory, power loss occurs) # MyISAM: Self-adding columns still start from the previous self-adding data (there are files, no loss)