mysql非空与唯一性约束

mysql> create table product(
    -> id int,
    -> name varchar(10),
    -> p_id int not null,
    -> )character set utf8
    -> collate utf8_general_ci;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')character set utf8
collate utf8_general_ci' at line 5
mysql> create table product(
    -> id int,
    -> name varchar(10),
    -> p_id int not null
    -> )character set utf8
    -> collate utf8_general_ci;
Query OK, 0 rows affected (0.16 sec)

mysql> insert into product
    -> values
    -> (1,'cainiao');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> (1,'cainiao',2);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,'cainiao',2)' at line 1
mysql> insert into product
    -> values
    -> (1,'cainiao',1001);
Query OK, 1 row affected (0.12 sec)

mysql> select * from product;
+------+---------+------+
| id   | name    | p_id |
+------+---------+------+
|    1 | cainiao | 1001 |
+------+---------+------+
1 row in set (0.00 sec)

mysql> alter table product
    -> change column p_id p_id int not null unique default 1002;
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc product;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| p_id  | int(11)     | NO   | PRI | 1002    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.07 sec)

mysql> insert into product
    -> values
    -> (2,'nima');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into product
    -> values
    -> (2,'cainiao',1002);
Query OK, 1 row affected (0.10 sec)

mysql> insert into product(id,name) values(3,'cainiao');
ERROR 1062 (23000): Duplicate entry '1002' for key 'p_id'
mysql> create table stu(
    -> id int primary key,
    -> name varchar(20)
    -> )character set utf8
    -> collate utf8_general_ci;
Query OK, 0 rows affected (0.20 sec)

mysql> insert into stu values(1001,'jack');
Query OK, 1 row affected (0.09 sec)

mysql> insert into stu values(1001,'lucy');
ERROR 1062 (23000): Duplicate entry '1001' for key 'PRIMARY'
mysql> create table test
    -> (
    -> x int unique,
    -> y int unique
    -> )character set utf8
    -> collate utf8_general_ci;
Query OK, 0 rows affected (0.21 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.09 sec)

mysql> create table test(
    -> x int,
    -> y int,
    -> unique(x,y)
    -> );
Query OK, 0 rows affected (0.18 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.05 sec)

mysql> create table test(
    -> x int,
    -> y int,
    -> primary(x,y)
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(x,y)
)' at line 4
mysql> create table test
    -> (
    -> x int,
    -> y int,
    -> primary key(x,y)
    -> );
Query OK, 0 rows affected (0.31 sec)

mysql> insert into test values(1,1);
Query OK, 1 row affected (0.09 sec)

mysql> insert into test values(1,2);
Query OK, 1 row affected (0.05 sec)

mysql> insert into test values(1,2);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'PRIMARY'