Sophie's Data Orchard

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...

 Establish a default value might be useful, it's fun to learn something new.  Let's assume that I'm going to add new records to the table which belongs to one single category, and I want it generate automatically each time when I key in the new record. I'm going to use the Products table this time. Open the design window and select Category from the Column Name, then move down to the Column Properties. Type 'eBooks' to 'Default Value or Binding', SQL server will automatically turn it into '(N'ebooks')'. The  N character  means that this will use the Unicode character set and  the data type for Category is nvarchar(50) which supports the Unicode.  Now save this change and let's test if it works. Go to Object Explorer, select table name, right click and press 'Edit Top 200 Rows'. Add a new record, fill the info to each column of table product, skip the Category column for now. Then press enter, and Voila!  SQL server fills out t...

We know that  social security number, employee ID or passport number are all unique numbers.  SQL Server has the capability to let  the database engine automatically assign new unique identifies when new records are added. Let's do it. First, select the table 'Customers' from my SQL server 2022's database 'Red30Tech', then right click the table name and select Design. Add a new column named 'CustomerID', set Data Type as 'int', scroll down to the Column Properties, update (Is Identity) from Identity Specification as 'Yes', set Identity Seed as '1000' which means the very first row loaded into the table starts from 1000. Set Identity Increment as '1' which means it will increase 1 from 1000 for the next row. Now, drag the little triangle icon on the left of CustomerID to the top, click the Primary key icon, this column will be assigned as column with unique value for each row.  Save the design window and hover over to the t...