you are at Encyclopedia of SQL >> UNIQUE
UNIQUE
With UNIQUE we can specified which columns have to be unique. It means all column with specified UNIQUE keyword have to be unique in each record in table
Column specified as primary key is automatically unique
Each table can have many unique columns.
Syntax for specifing unique column in MySQL
CREATE `table_name`
(
`column_name1` data_type ,
`column_name2` data_type ,
UNIQUE (`column_name)
)
Syntax for specifing unique column in SQL Server / Oracle / MS Access
CREATE `table_name`
(
`column_name1` data_type UNIQUE ,
`column_name2` data_type [UNIQUE],
)
Syntax for specifing many unique columns
CREATE `table_name`
(
`column_name1` data_type ,
`column_name2` data_type ,
CONSTRAINT unique_constraint
UNIQUE (`column_name1` operator 'value'
AND|OR `column_name2` operator 'value')
)
Specifing name for unique constraint helps us to modify and dalete this constraint.
Syntax for creating unique constraint for existing table
ALTER TABLE `table_name`
ADD CONSTRAINT unique_name
UNIQUE (`column_name1`,`column_name2`)
Syntax for removing constraint for existing table in MySQL
ALTER TABLE `table_name`
DROP INDEX unique_name
Syntax for removing constraint for existing table in SQL Server / Oracle / MS Access
ALTER TABLE `table_name`
DROP CONSTRAINT unique_name
Example
Create table `people` with name and surname column which have to be unique
query
CREATE TABLE `people`
{
`order_no` int,
`name` text,
`surname` text,
`evidence_id` int,
CONSTRAINT `unique_name`,
UNIQUE ( `name`,`surname` )
}
Now delete constraint `unique_name` for existing table from previous example
query
ALTER TABLE `people`
DROP (CONSTRAINT or INDEX for MySQL) `unique_name`
[ wróć na górę strony ]
