那些年的记忆

习惯改变行为,行为决定命运。

复习(三) 约束及修改数据表

约束的概念

约束保证数据的完整性和一致性。

约束分为表级约束和列级约束

约束类型包括:

    NOT NULL(非空约束)

    PRIMARY KEY(主键约束)

    UNIQUE KEY(唯一约束)

    DEFAULT(默认约束)

    FOREIGN KEY(外键约束)


前面四种上面已说过,现在我们只看外键约束

外键约束的要求:

    父表和子表必须使用相同的存储引擎(InnoDB),而且禁止使用临时表。

    外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同;而字符的长度则可以不同。

    外键列和参照列必须创建索引。如果外键列不存在索引的话,MySQL将自动创建索引。


配置MySQL默认存储引擎

my.ini

[mysqld]

default-storage-engine=INNODB


外键约束

例如:

mysql> CREATE TABLE provinces(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> pname VARCHAR(20) NOT NULL
    -> );
mysql> CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY (pid) REFERENCES provinces (id)
    -> );


外键约束的参照操作

CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行

SET NULL:从父表删除或更新行,并设置子表中的外键列为NULL。如果使用该选项,必须保证子表列没有指定NOT NULL

RESTRICT:拒绝对父表的删除或更新操作

NO ACTION:标准SQL的关键字,在MySQL中与RESTRICT相同


例如(CASCADE):

mysql> CREATE TABLE users1(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE
    -> );


表级约束与列级约束

对一个数据列建立的约束,称为列级约束

对多个数据列建立的约束,称为表级约束

列级约束既可以在列定义时声明,也可以在列定义后声明

表级约束只能在列定义后声明


修改数据表

添加单列

ALTER TABLE table_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]

例如:

mysql> ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 0;
mysql> ALTER TABLE users1 ADD pasword VARCHAR(32) NOT NULL AFTER username;


添加多列

ALTER TABLE table_name ADD [COLUMN] (col_name column_definition,...)


删除列

ALTER TABLE table_name DROP [COLUMN] col_name

例如:

mysql> ALTER TABLE users1 DROP age;
mysql> ALTER TABLE users1 DROP age, password;


添加主键约束

ALTER TABLE table_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)

例如:

mysql> CREATE TABLE users2(
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED
    -> );
mysql> ALTER TABLE users2 ADD id SMALLINT UNSIGNED FIRST;
mysql> ALTER TABLE users2 ADD CONSTRAINT pk_users2_id PRIMARY KEY (id);


添加唯一约束

ALTER TABLE table_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (index_col_name,...)

例如:

mysql> ALTER TABLE users2 ADD UNIQUE (username);


添加外建约束

ALTER TABLE table_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition

例如:

mysql> ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces (id);


添加/删除默认约束

ALTER TABLE table_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

例如:

mysql> ALTER TABLE users2 ALTER age SET DEFAULT 15;
mysql> ALTER TABLE users2 ALTER age DROP DEFAULT;


删除主键约束

ALTER TABLE table_name DROP PRIMARY KEY;

例如:

mysql> ALTER TABLE users2 DROP PRIMARY KEY;


删除唯一约束

ALTER TABLE table_name DROP {INDEX | KEY} index_name

例如:

mysql> ALTER TABLE users2 DROP INDEX username;


删除外键约束

ALTER TABLE table_name DROP FOREIGN KEY fk_symbol

例如:

mysql> ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;

注:users2_ibfk_1 可以通过SHOW CREATE TABLE users2;查看到


修改列定义

ALTER TABLE table_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

例如:

mysql> ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST;


修改列名称

ALTER TABLE table_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]

例如:

mysql> ALTER TABLE users2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;


修改数据表名称

1、ALTER TABLE table_name RENAME new_table_name

2、RENAME TABLE table_name TO new_table_name [, table_name2 TO new_table_name2] ...

例如:

mysql> ALTER TABLE users2 RENAME users3;
mysql> RENAME TABLE users3 TO users4;


发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。