SQL Server 2022 : Check constraints and unique constraints

We could set up the rules or constraints when we create a new table, like we don't want the input too short or any unwanted characters, so 'check constraints' could help us on it.

I'm using the Customers table again and go to 'Design' window. Click the 'Manage Check Constraints' icon or select any item in the Column Name and right click, then select 'Check Constraints' from the menu.





Then I have this window :

I edit the constraints in this window: use len() function to make the content of LastName column must be greater than or equal to 2 bytes. Then rename the '(Name)' of Identity, also add 'Description' and press 'Add' to save the editing.

Go back to the Customers table, select 'Edit Top 200 Rows'.

Go to the last row of the Customers table, then enter the new content to it.
Input 'Roger' as FirstName, 'W' as LastName. Then skip the Address, City and State. Press enter, it returns the pop-up window with the error message that the new row 4 was not committed because it doesn't satisfy the constraint 'CK_Customers_LastNameLenghth' .  So the constraint's setup is successful. The press Ok and Esc, the violated content will be erased by the system.

Go to Object Explorer, check the dbo.Customers table, CK_Customers_LastNameLength is under the Constrains folder.
Also, for dbo.Products, we can see DF_Products_Category and DF_Products_TimeStamps as Constraints.



Now, what if we want the name of product is unique, how to set the constraints?
Right click the dbo.Products, select 'Design'. But you can't find 'unique constraints' option from top of the bar or from the right click menu. SQL server uses index to achieve this goal.

Click 'Add' button to add a new index.

SQL server creates the default name as 'IX_Products'. Then click the three dots on the right of the ProductID(ACS).


We arrives at the 'Index Columns' window. 


Change the Column Name into 'Name', and leave the Sort Order as 'Ascending', then click ok. 



Go back to the Indexes/Keys window, we can see the Columns has been updated as 'Name(ASC)'.

Now we set 'Yes' to Is Unique which will constrain the input of the name. No duplicated value is allowed. And update the [Name] of Identity as 'UX_Products_Name' and close.


Go back to the Object Explorer, check the Indexes folder of dbo.Products, it shows as ' UX_Products_Name(Unique, Non-Clustered)'.


Let's test if the setting works well. Right click dbo.products, and select 'Edit Top 200 Rows'. And go down to the bottom of the table. 


Input 'Blueprints' as Category, 'BP112' as ProductID, 'Cat Robot Blueprint' as Name. Also fill up the rest columns and press enter. But the system returns the error message said that 'Cat Robot Blueprint' is duplicated. 


Press OK and update the name column as 'Large Cat Robot Blueprint'. Press enter, the new row is updated properly with the time stamp. 



Well, that is how SQL server create the unique constraints through index. 

Notes: I use the sample database from Microsoft SQL Server 2022 Essential Training With Adam Wilbert. 

Comments

Post a Comment

Popular posts from this blog

Schema and Security Control in SQL server

Image
dbo is always seen as a SQL server table's schema. dbo stands for database owner. The dbo schema is the default schema of every database. When user created with  CREATE USER   T-SQL command have dbo as their default schema. The dbo schema is owned by the dbo user account.  SQL server also provides other pre-defined schemas that have the same names as the built-in db users and roles: db_accessadmin, db_backupoperator, db_datareader, db_datawriter, db_ddladmin, db_denydatareader, db_denydatawriter, db_owner, db_securityadmin . These exist for backward compatibility. The recommendation is to not use them for user objects.  User can drop the schemas that have the same names as the fixed database roles - unless they're already in use, in which case the drop-command will simply return an error and block the drop of the used schema. For example: IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_accessadmin') DROP SCHEMA [db_accessadmin] GO If user drops the...
Read more

The statistics of competition results in Excel

Image
Recently, my son participated in a local academic competition alongside other kids of same grade from various schools. During the group contest, held in the same classroom, his team which has 4 kids from same class felt confident as they competed against other school teams. However, to their surprise, their school didn’t secure a spot in the top eight. The team they considered less capable outperformed them. As a data enthusiast mom, I’m eager to delve into the reasons behind this unexpected outcome. I'm going to divide the observants into 4 groups:  Group1: participants from school A, Group2: participants from school B,  Group3: participants from school C, Group4: All the participants. First, I'm going to study the Descriptive Statistics of each group's scores.  Descriptive statistics refer to the analysis, summary, and presentation of findings related to a data set derived from a sample or the entire population. These statistics help us understand and organize char...
Read more

Washington State's Gasoline Price Hit the historical High - PowerBI Dashboard

Image
Washington State's Gasoline Price Hit the Historical High Recently, I heard the news that the gas price of Washington hit a historical high in June 2023 , even unseated California, though the Gas prices are still lower than the inflation-plagued summer months last year. The average price in King County exceeded $5 a gallon on Jun 21st per The Seattle Times . No wonder when I fill up my gas at Costco, the price is almost 5 dollars per gallon. Normally, I'm not price sensitive, but this time, it surprised me. So I grabbed the dataset from U.S. Energy Information Administration and work out a Power BI dashboard.  The first chart which is the Line and clustered Column Chart to show the gas price tendency of Washington State together with other 4 states as California, Texas, New York and Florida. The gas price I'm using is All Grades All Formulations Retail Gasoline Prices(Dollars per Gallon). It looks like California always has the highest gas price in the past 5 ...
Read more