This will produce the following result, where there is no duplicate entry. Sqlite> SELECT DISTINCT name FROM COMPANY Now, let us use DISTINCT keyword with the above SELECT query and see the result. SELECT DISTINCT column1, lumnNĬonsider COMPANY table with the following records.įirst, let us see how the following SELECT query returns duplicate salary records. Syntaxįollowing is the basic syntax of DISTINCT keyword to eliminate duplicate records. While fetching such records, it makes more sense to fetch only unique records instead of fetching duplicate records. There may be a situation when you have multiple duplicate records in a table. In this tutorial, you have learned how to use the SQLite UNIQUE constraint to ensure all values in a column or a group of columns are unique.SQLite DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only the unique records. Let’s query data from the lists table: SELECT * FROM lists Īs you can see, even though the email column has a UNIQUE constraint, it can accept multiple NULL values. The following statement inserts multiple NULL values into the email column of the lists table: INSERT INTO lists(email) The following statement creates a new table named lists whose email column has a UNIQUE constraint: CREATE TABLE lists( SQLite treats all NULL values are different, therefore, a column with a UNIQUE constraint can have multiple NULL values. Here is the error: Error while executing SQL query on database 'chinook': `UNIQUE` constraint failed: shapes.background_color, shapes.foreground_colorĬode language: SQL (Structured Query Language) ( sql ) SQLite UNIQUE constraint and NULL However, the following statement causes an error due to the duplicates in both background_color and foreground_color columns: INSERT INTO shapes(background_color,foreground_color) The following statement works because of no duplication violation in both background_color and foreground_color columns: INSERT INTO shapes(background_color,foreground_color) The following statement inserts a new row into the shapes table: INSERT INTO shapes(background_color,foreground_color) UNIQUE(background_color,foreground_color) The following statement creates the shapes table with a UNIQUE constraint defined for the background_color and foreground_color columns: CREATE TABLE shapes( Here is the error message: Error while executing SQL query on database 'chinook': UNIQUE constraint failed: contacts.emailĬode language: SQL (Structured Query Language) ( sql ) Defining a UNIQUE constraint for multiple columns example VALUES ( 'Johnny', 'Doe', language: SQL (Structured Query Language) ( sql ) If you attempt to insert a new contact with the same email, you will get an error message: INSERT INTO contacts(first_name,last_name,email) VALUES ( 'John', 'Doe', language: SQL (Structured Query Language) ( sql ) The following example inserts a new row into the contacts table: INSERT INTO contacts(first_name,last_name,email) The following statement creates a new table named contacts with a UNIQUE constraint defined for the email column: CREATE TABLE contacts( Defining a UNIQUE constraint for one column example Let’s take some examples of using the UNIQUE constraint. Once a UNIQUE constraint is defined, if you attempt to insert or update a value that already exists in the column, SQLite will issue an error and abort the operation. The following illustrates how to define a UNIQUE constraint for multiple columns: CREATE TABLE table_name( Or at the table level: CREATE TABLE table_name( The following shows how to define a UNIQUE constraint for a column at the column level: CREATE TABLE table_name(Ĭode language: SQL (Structured Query Language) ( sql ) Only at the table level, you can define a UNIQUE constraint across multiple columns. You can define a UNIQUE constraint at the column or the table level. To define a UNIQUE constraint, you use the UNIQUE keyword followed by one or more columns. Introduction to SQLite UNIQUE constraintĪ UNIQUE constraint ensures all values in a column or a group of columns are distinct from one another or unique. Summary: in this tutorial, you will learn how to use the SQLite UNIQUE constraint to ensure all values in a column or a group of columns are unique.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |