A UNIQUE Constraint is either a < Table Constraint> or a <Column Constraint> and defines a rule that constrains a unique key to non-duplicate values only. The required syntax for a UNIQUE Constraint is: 

UNIQUE  ::= 
[ CONSTRAINT  ] 
UNIQUE ( [ {,}... ]) | UNIQUE (VALUE) 
[  ] 

UNIQUE  ::= 
[ CONSTRAINT  ] 
 UNIQUE 
[  ]
A Base table may be constrained by zero or more UNIQUE Constraints, each
specifying a rule that a group of one or more Columns (the unique key) may
contain only unique values. You can't define a unique key with Columns that
have a <data type> of BLOB, CLOB, NCLOB, or ARRAY. A unique key
is also known as a candidate key of the Table. The main reasons you need
candidate keys are (a) to get row-level addressing, (b) so that foreign
keys can reference the candidate key and (c) to prevent duplication
(keyboard errors, etc).


Each UNIQUE Constraint must name a set of Columns that is different from
the set of Columns named by any other UNIQUE or PRIMARY KEY Constraint
defined for the Table. If you use UNIQUE (VALUE) to define a UNIQUE
Constraint, you're constraining the Table that owns the Constraint to have just
that one UNIQUE Constraint and — since a PRIMARY
KEY Constraint is a type of UNIQUE Constraint —
you're also constraining that Table not to have any PRIMARY KEY Constraint.
UNIQUE (VALUE) constrains the entire row of the Table to be unique from
any other row.


Here are some examples of UNIQUE Constraint definitions:


CREATE TABLE Table_1 ( 
  column_1 SMALLINT, 
  CONSTRAINT constraint_1 UNIQUE(column_1) DEFERRED INITIALLY DEFERRED);
-- defines a UNIQUE <Table Constraint> in CREATE TABLE

CREATE TABLE Table_1 ( 
  column_1 SMALLINT 
      CONSTRAINT constraint_1 UNIQUE DEFERRED INITIALLY DEFERRED, 
  column_2 CHAR(5));
-- defines a UNIQUE <Column Constraint> in CREATE TABLE

ALTER TABLE Table_1 ADD CONSTRAINT constraint_2 
  UNIQUE(column_1,column_2) DEFERRED INITIALLY DEFERRED;
-- defines a UNIQUE <Table Constraint> in ALTER TABLE
Once created, a UNIQUE <Column Constraint> logically becomes a UNIQUE
<Table Constraint>. The <Column Constraint> in this SQL statement:


CREATE TABLE Table_1 ( 
  column_1 SMALLINT UNIQUE);
is therefore equivalent to the <Table Constraint> in this SQL statement: 


CREATE TABLE Table_1 ( 
  column_1 SMALLINT, 
  UNIQUE(column_1));
A UNIQUE Constraint makes it impossible to COMMIT any operation that
would cause the unique key to contain any non-null duplicate values. (Multiple
null values are allowed, see below.) A UNIQUE Constraint is violated if its condition is
FALSE for any row of the Table it belongs to. Consider these SQL
statements:


CREATE TABLE Table_1 ( 
  column_1 SMALLINT, 
  column_2 VARCHAR(5), 
  CONSTRAINT constraint_1 
      UNIQUE(column_1,column_2) DEFERRABLE INITIALLY DEFERRED);

INSERT INTO Table_1 (column_1, column_2) 
VALUES (1, 'hello');
For this example, CONSTRAINT_1 would be violated only if you tried to
INSERT another {1, 'hello') row into TABLE_1: a {1, 'bye') row, a {2,
'hello') row, a {null, 'hello') row, a {1, null) row and a {null, null) row
would all satisfy the Constraint.


If you want to restrict your code to Core SQL, don't use the
"UNIQUE(VALUE)" form to define a UNIQUE Constraint and don't add a
NOT NULL Constraint to any Column that is part of a unique key for a
UNIQUE Constraint.


One aspect of a UNIQUE constraint that is often overlooked
is related to null values. In SQL any NULL is never equal to
anything, not even to another NULL.
Consequently, a UNIQUE constraint will not prevent one from storing
duplicate rows, if they contain null values:


CREATE TABLE Table_1 (a int not null, b int, unique (a,b));
insert into Table_1 values (1,1),(2,NULL),(2,NULL);
select * from Table_1;

->

+---+------+
| a | b    |
+---+------+
| 1 |    1 |
| 2 | NULL |
| 2 | NULL |
+---+------+
Indeed, in SQL two last rows, even if identical, are not equal to each other:


SELECT (2, NULL) = (2, NULL);

->

+---------------------- +
| (2, NULL) = (2, NULL) |
+---------------------- +
| 0                     |
+---------------------- +
In MariaDB you can combine this with virtual columns to
enforce uniqueness over a subset of rows in a table:


create table Table_1 (
  user_name varchar(10),
  status enum('Active', 'On-Hold', 'Deleted'),
  del char(0) as (if(status in ('Active', 'On-Hold'),'', NULL)) persistent,
  unique(user_name,del)
)
This table structure ensures that all active or on-hold users have distinct
names, but as soon as a user is deleted, his name is no longer part of the
uniqueness constraint, and another user may get the same name.






        
        

        
Comments

        

    

        Comments loading...