How to create a data table----------------\primary key (primary KEY constraint)-------\unique key (Unique constraint)----------\default (default constraint)-------------->> >
Data Table OperationsNot NULL (non-null constraint)-------------/record insert----------------------------/find record---------------------------/
Constraints1. Constraints to ensure data integrity and consistency 2. Constraints are divided into table-level constraints and column-level constraints 3. Constraint types include: NOT NULL (non-null constraint) PRIMARY key (primary KEY constraint) Unique key (default constraint) FOREIGN key (FOREIGN KEY constraint)
FOREIGN KEYEnsure data consistency and integrity for one-to-one or one-to-many relationships
requirements for FOREIGN KEY constraints:1. The parent and child tables must use the same storage engine at the same time, and temporary table 2 is prohibited. The storage engine for the data table can only be InnoDB3. Foreign key columns and reference columns must have similar data types. Where the length of the number or whether the sign bit must be the same, and the length of the character can be different 4. Foreign key columns and reference columns must be indexed. MySQL will automatically create an index if the foreign key column does not exist
Edit the default storage engine for a data tableMySQL configuration file (my.ini) default-storage-engine=innodb use test; CREATE TABLE provinces (id samllint unisigned frimary KEY auto_increment, PName VARCHAR () not NULL,); SHOW CREATE TABLE Provinces; CREATE TABLE users (id SMALLINT unisigned PRIMARY KEY auto_increment, username VARCHAR (Ten) not NULL, PID SMALLINT unisigned, FOREIGN KEY (PID) REFERENCES provinces (id),); SHOW INDEXES from Provinces\g; #显示provinces中是否有索引SHOW CREATE TABLE users;
referential actions for foreign KEY constraints1.CASCADE: Delete or update from parent table and automatically delete or update matching rows in child table 2.SET null: Deletes or updates rows from the parent table, and sets the foreign key column in the child table to null. If you use this option, you must ensure that the child table column does not specify not NULL3. RESTRICT: Reject Delete or update action on parent table 4.NO action: Standard SQL keyword, same as RESTRICT in MySQL CREATE TABLE users1 (id SMALLINT unisigned PRIMARY key A Uto_increment, username VARCHAR (Ten) not NULL, PID SMALLINT unisigned, FOREIGN KEY (PID) REFERENCES provinces (id ) on DELETE CASCADE), insert provinces (pname) VALUES (' A '), insert provinces (pname) VALUES (' B '); Insert provinces (pname) VALUES (' C '); SELECT * from provinces;
table-level constraints and column-level constraintsA constraint established on a data column, called a column-level constraint, establishes a constraint on multiple data columns, called table-level constraint column-level constraints, which can be declared when a column is defined, or a table-level constraint can be declared after a column is defined only after a column definition
Modifying data Tables
Add a single columnALTER TABLE tbl_name add [COLUMN] col_name column_definition [first 丨 after col_name] ALTER table users1 add age TINYINT UNI Signed not NULL DEFAULT 10; ALTER TABLE users1 ADD password VARCHAR (+) not NULL after username; ALTER TABLE users1 ADD turename VARCHAR (a) not NULL first;
Add multiple ColumnsALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition,......)
Delete ColumnALTER TABLE Tbl_name drop [COLUMN] col_name ALTER table users1 drop turename; ALTER TABLE users1 DROP password,drop age;
Add a PRIMARY KEY constraintALTER TABLE tbl_name ADD [Constraint[symbol]] PRIMARY KEY [Index_type] (Index_col_name,...) CREATE TABLE users2 (username VARCHAR (Ten) not NULL, PID SMALLINT UNSIGNED); ALTER TABLE users2 ADD ID SMALLINT UNSIGNED; ALTER TABLE users2 ADD CONSTRAINT pk_users2_id PRIMARY KEY (ID);
Add a UNIQUE constraintALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [index 丨 key] [index_name] [Index_type] (Index_col_name,...) ALTER TABLE users2 ADD UNIQUE (username);
Add a FOREIGN KEY constraintALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] [Index_type] (index_col_name,...) reference_ Definition ALTER TABLE users2 ADD FOREIGN KEY (PID) REFERENCES provinces (ID);
Add/remove Default ConstraintsAlter TABLE Tal_name ALTER [COLUMN] col_name {SET default literal 丨 drop default}
Delete a primary KEY constraintALTER TABLE tbl_name DROP PRIMARY KEY
Remove UNIQUE constraintALTER TABLE tbl_name DROP [index 丨 key] Index_name
Delete a foreign key constraintALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
Modifying a column definitionALTER TABLE Tbl_name MODIFY [COLUMN] col_name column_definition [first 丨 after col_name] ALTER TABLE USERS2 MODIFY ID Smalli NT unisigned not NULL first;
Modify column namesALTER TABLE tbl_name change [COLUMN] old_col_name new_col_name column_definition [first 丨 after col_name] ALTER table User2 Change PID p_id TINYINT unisigned not NULL;
Data Table RenamingMethod 1:alter Table Tbl_name RENAME [to 丨 as] New_tbl_name method 2:rename table Tbl_name to New_tbl_name [tbl_name2 to New_tbl_name 2] ... ALTER TABLE users2 RENAME users3; RENAME TABLE Users3 to Users2;
Summary
Constraintsby function: Not null,primary key,unique key,default,foreign KEY by data column number is divided into: table-level constraints, column-level constraints
Modifying data TablesActions for fields: Add/Remove fields, modify column definitions, modify column names, and more for constraints: Add/Remove various constraints for data table operations: Data table renaming (two ways)
Constraints and modifying data tables
