в 13:46, 26 мая 2010

2010-05-26T13:46:00Z

Admin: 1 версия

2010-05-26T10:16:40Z

1 версия

Новая страница

==Alter table: add unique ==



<source lang="sql">
/*
mysql> Drop table Product;
mysql> CREATE TABLE Product
-> (
-> ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
-> ModelID SMALLINT UNSIGNED NOT NULL
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> ALTER TABLE Product
-> ADD UNIQUE (ID, ModelID);
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> Describe Product;
+---------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+-------+
| ID | smallint(5) unsigned | | PRI | 0 | |
| ModelID | smallint(5) unsigned | | | 0 | |
+---------+----------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

*/
Drop table Product;

CREATE TABLE Product
(
ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
ModelID SMALLINT UNSIGNED NOT NULL
);
ALTER TABLE Product
ADD UNIQUE (ID, ModelID);
Describe Product;

</source>

==Create table: unique value and value from combined columns==



<source lang="sql">
/*
mysql> Drop table Product;
mysql> CREATE TABLE Product
-> (
-> ID SMALLINT UNSIGNED NOT NULL,
-> ModelID SMALLINT UNSIGNED NOT NULL,
-> ModelDescrip VARCHAR(40),
-> PRIMARY KEY (ID),
-> UNIQUE (ID, ModelID)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> Describe Product;
+--------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------------------+------+-----+---------+-------+
| ID | smallint(5) unsigned | | PRI | 0 | |
| ModelID | smallint(5) unsigned | | | 0 | |
| ModelDescrip | varchar(40) | YES | | NULL | |
+--------------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

*/
Drop table Product;

CREATE TABLE Product
(
ID SMALLINT UNSIGNED NOT NULL,
ModelID SMALLINT UNSIGNED NOT NULL,
ModelDescrip VARCHAR(40),
PRIMARY KEY (ID),
UNIQUE (ID, ModelID)
);
Describe Product;

</source>

==Setting a Unique Constraint==



<source lang="sql">
/*
mysql> Drop TABLE Employee;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE Employee (
-> Name VARCHAR(50) NOT NULL,
-> PhoneNo VARCHAR(15) DEFAULT "Unknown Phone" NOT NULL,
-> CONSTRAINT MyUniqueKey UNIQUE (Name));
Query OK, 0 rows affected (0.07 sec)
mysql> Describe Employee;
+---------+-------------+------+-----+---------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------------+-------+
| Name | varchar(50) | | PRI | | |
| PhoneNo | varchar(15) | | | Unknown Phone | |
+---------+-------------+------+-----+---------------+-------+
2 rows in set (0.00 sec)
mysql> INSERT INTO Employee (Name, Phone) VALUES ("Joe Wang", "666 2323");
ERROR 1054 (42S22): Unknown column "Phone" in "field list"
mysql> INSERT INTO Employee (Name) VALUES ("John Doe");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Employee (Name, Phone) VALUES ("Joe Wang", NULL);
ERROR 1054 (42S22): Unknown column "Phone" in "field list"
mysql> Select * from Employee;
+----------+---------------+
| Name | PhoneNo |
+----------+---------------+
| John Doe | Unknown Phone |
+----------+---------------+
1 row in set (0.00 sec)

*/
Drop TABLE Employee;

CREATE TABLE Employee (
Name VARCHAR(50) NOT NULL,
PhoneNo VARCHAR(15) DEFAULT "Unknown Phone" NOT NULL,
CONSTRAINT MyUniqueKey UNIQUE (Name));
Describe Employee;
INSERT INTO Employee (Name, Phone) VALUES ("Joe Wang", "666 2323");
INSERT INTO Employee (Name) VALUES ("John Doe");
INSERT INTO Employee (Name, Phone) VALUES ("Joe Wang", NULL);
Select * from Employee;

</source>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL/MySQL/Select Clause/Unique - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия