Among the many database constraints available to us, the UNIQUE key constraint ensures the uniqueness of data in a column or a set of columns. Read on to find out more about the UNIQUE key constraint and how to define it in Vertabelo.
In this article, we’ll focus on the
UNIQUE key constraint. We’ll start with its basic definition and usage and gradually build up to more advanced options. Also, we’ll jump into Vertabelo and create an ER diagram that uses the
UNIQUE key constraint. You’ll see that the
UNIQUE key offers different options in various database engines.
Let’s get started.
UNIQUE key is one of the database constraints that allow you to set rules for your data. It prevents the column from storing duplicate values.
You can learn about the OTHER DATABASE CONSTRAINTS IN THIS ARTICLE.
Let’s look at the various ways we can define the
UNIQUE key constraint on a single column.
UNIQUE key constraint can be defined with the column definitions, like this …
CREATE TABLE Persons ( Id int PRIMARY KEY, FirstName varchar(50), LastName varchar(50), SSN varchar(9) UNIQUE);
… or after all the columns are defined, like this:
CREATE TABLE Persons ( Id int PRIMARY KEY, FirstName varchar(50), LastName varchar(50), SSN varchar(9), CONSTRAINT unique_ssn UNIQUE(SSN));
Sometimes, we decide to make a column unique after table creation. Here’s how we do it:
CREATE TABLE Persons ( Id int PRIMARY KEY, FirstName varchar(50), LastName varchar(50), SSN varchar(9)); ALTER TABLE Persons ADD UNIQUE(SSN);
We use the
ALTER TABLE statement to add or remove database constraints.
The constraint implemented on the
SSN column is ready to be tested.
First, we insert some data into our
INSERT INTO Persons VALUES(1, 'David', 'Anderson', '123123123');
But what if we insert another data row with the same
INSERT INTO Persons VALUES(2, 'Anne', 'Johns', '123123123');
This results in an error:
SQL Error : ERROR: duplicate key value violates unique constraint "unique_ssn" Detail: Key (ssn)=(123123123) already exists.
The UNIQUE key constraint works as expected!
Let’s see how to define the UNIQUE key on a set of columns:
CREATE TABLE Persons ( Id int PRIMARY KEY, FirstName varchar(50), LastName varchar(50), SSN varchar(9), CONSTRAINT unique_name UNIQUE(FirstName, LastName));
Or, after table creation:
ALTER TABLE Persons ADD UNIQUE(FirstName, LastName);
Please note that in this case, only the combination of columns must be unique, but not each column individually. So, the following
INSERT statements are all valid.
INSERT INTO Persons VALUES(1, 'David', 'Anderson', '123123123'); INSERT INTO Persons VALUES(2, 'Anne', 'Johns', '123123123'); INSERT INTO Persons VALUES(3, 'David', 'Johns', '123123123'); INSERT INTO Persons VALUES(4, 'Anne', 'Anderson', '123123123');
UNIQUE key constraint implemented on a set of columns doesn’t let duplicate value groups sneak in, just like the one implemented on a single column doesn’t let duplicate values in.
A candidate key is a column or a set of columns that identify each row uniquely. So, the
UNIQUE key qualifies to be the candidate key. Check out this ARTICLE TO LEARN MORE ABOUT DATABASE KEYS.
Have you heard about the foreign key constraint? It lets you link data stored in different tables. Get the BASICS ON FOREIGN KEYS IN THIS ARTICLE and then see how to IMPLEMENT FOREIGN KEYS IN A PHYSICAL MODEL HERE!
In this section, you’ll learn how to define the
UNIQUE key in Vertabelo. Also, we’ll take a look at some of the more advanced options.
Let’s define the
UNIQUE key constraint in Vertabelo. To do so, create a table and navigate to the Alternate (unique) keys section in the right-side panel.
Now, we are ready to add the
Click on the Add key button and expand the data. Don’t forget to name your
UNIQUE key constraint by filling in the Name field. To give it some context, use the Comment field. And, most importantly, add the column(s) that will implement this constraint.
That’s all! The
UNIQUE key constraint is now ready.
You can also generate an SQL code like this:
And here it is:
In the next section, you’ll learn about the additional options available in various database engines.
There are quite a few advanced constraint options that are database-specific. Let’s look at them one by one.
The available options are
Here, we have either
|Index tablespace||This option lets us define the tablespace where the unique index (associated with the |
|With||This clause is optional. It specifies storage parameters for a table or index.|
|MySQL||Using||This clause lets us define the type of the |
|Key block size||This specifies the size of index key blocks in bytes. The database engine treats it as a hint.|
|Microsoft SQL Server||Is clustered||This indicates whether we deal with a clustered or non-clustered index.|
|WITH index options||The |
|ON clause||The |
In Vertabelo, you can define your database when creating a new physical model. All the
UNIQUE key constraint options are available in the right-side panel.
For PostgreSQL database engine:
For more info, see our article on DATABASE CONSTRAINTS IN POSTGRESQL.
For the MySQL database engine:
For further reading, we have an article about DATABASE CONSTRAINTS IN MYSQL.
For the Microsoft SQL Server database engine:
And here’s an ARTICLE about MICROSOFT SQL SERVER DATABASE CONSTRAINTS.
Database constraints are a crucial part of any database design. Make sure to check out our database-specific articles to learn more about the different constraints available.
UNIQUE key constraint is a very straightforward concept. It simply prevents duplicate values in a column. Try out some examples on your own and you’ll see!
There are many more database constraints, such as primary and foreign keys, the
CHECK constraint, the
DEFAULT constraint, and the
NOT NULL constraint. Continue to our article on DATABASE CONSTRAINTS: WHAT THEY ARE AND HOW TO DEFINE THEM IN VERTABELO to get a glimpse of them all.
Original article source at: https://www.vertabelo.com/
The UNIQUE keyword or constraint is used to ensure that all values in a column are unique.
CREATE TABLE <TABLE_NAME> ( <COLUMN_NAME> DATATYPE UNIQUE );
CREATE TABLE Employee ( EMP_NO Integer UNIQUE );
ALTER TABLE <TABLE_NAME> ADD UNIQUE (<COLUMN_NAME>);
ALTER TABLE Employee ADD UNIQUE(EMP_NO);
The UNIQUE constraint makes sure that each value in a column is distinct.
Original article source at: https://www.c-sharpcorner.com/
Delight and reach each customer with moLotus targeted and personalized campaigns, offering discounts and coupons to customers based on their profile and preferences. For more information, visit www.novosol.biz
#customers #personalized #customized #videomessages #unique #offer #molotus #revenue #videoadvertising #advertisingplatform #marketingplatform #mobilemarketing #digitaladvertising #directmarketing #marketing #mobileadvertising #mobilevideoadvertising #mobilevideocustomerinteractionplatform #mobileadvertisingcompany #novosol #telcos #telecom
The pandemic has also changed the role of Public Relations (or PR) in ways that were previously difficult to anticipate. The communications plan and strategy have undergone a major transformation as a result of developments such as remote working.
Will businesses continue to rely on conventional PR without compromising their brand-building efforts? Let’s take a look.
Predictive Analysis: Predictive analysis techniques may be used to forecast subjects and problems that are likely to pique viewer interest based on historical data patterns. It can also assist in the identification of potential customers by using real-time customer data.
Live Streaming and Virtual Events: Another new trend is live streaming of events such as award shows and business conferences, which is growing website traffic and online viewership from around the world.
The Impact of Fake News on Public Relations: The rise of many media outlets and social media sites such as Facebook and YouTube has aided the spread of fake news. With the dissemination of disinformation at an all-time high, public relations practitioners must contend with false statements regarding their business or industry.
It will be fascinating to see how corporate PR reacts to this transition and develops their organizational strategy for the next decade as more companies go remote and to online platforms.
Look out for these three powerful PR trends in 2021. With more businesses going remote and to online platforms, these trends are here to stay over the next decade.
**Three Unique PR Trends to Consider in 2021
#pr #trends #three #unique #public #relations
Unique visitors in google analytics are the first time visitor on the site or app. A unique visitor or active user chart shows the engagement with your site or app. Google analytics the foremost powerful tool for digital analytics out there these days it’s a tool for data analysis of your site or app.