> For the complete documentation index, see [llms.txt](https://zpliu.gitbook.io/booknote/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://zpliu.gitbook.io/booknote/mysql/03-zi-duan-yue-shu-tiao-jian.md).

# 字段约束条件

* `NULL`
* `default`
* `PRIMARY KEY`
* `FOREIGN KEY` 属于表约束，字段约束无效
* `check`
* `UNIQUE`

## 1.基本语法

### 1.1字段约束

```sql
CREATE TABLE teb_test(
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(20)
);
```

### 1.2表约束

```sql
CREATE TABLE `teb_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
)
```

还可以为外键约束添加约束的索引名称

```sql
CREATE TABLE tab_test(
 id INT AUTO_INCREMENT PRIMARY KEY,
 job_id INT,
 CONSTRAINT fg_job_id
 FOREIGN KEY(job_id) REFERENCES employees(employee_id)
);
```

通过`SHOW INDEX FROM tab_test;`可以查看表的具体索引值

|   Indexes   | Columns | Index Type |
| :---------: | ------- | ---------- |
|   PRIMARY   | id      | Unique     |
| fg\_job\_id | job\_id |            |

## 2.修改约束条件

### 2.1修改字段约束

```sql
ALTER TABLE tab_test MODIFY COLUMN id INT AUTO_INCREMENT UNIQUE ;
```

### 2.2修改表约束

```sql
# 主键只有一个
ALTER TABLE tab_test DROP PRIMARY KEY;
# 外键约束需要指定索引名称
ALTER TABLE tab_test DROP FOREIGN KEY fg_job_id;
# 也可手动为unique设置约束名
ALTER TABLE goodstest ADD CONSTRAINT GNAME_UNI UNIQUE KEY(GNAME)
```

### 2.3添加约束条件

```sql
ALTER TABLE tab_test ADD PRIMARY KEY(id);
```

## 参考

1. <https://www.cnblogs.com/sdnbyzq/p/10909479.html>&#x20;