SQL Day 3: Creating Tables & Enforcing Primary and Foreign Key

LO1: Creating the Tables

LO2: Enforcing Primary and Foreign Key

Prerequisite: Creating a Database

Creating Tables

You can create a table by two ways.

By Graphically: Expand the Database in Object Explorer and right click on Tables folder and click on New Table

  • Give the Column Name, Data Type and check Allow Nulls.
    • Column Name: This is going to be your column name and this column name should be unique in the table.
    • Data Type: For which type of data will be contained in the column. For example int, nvarchar, float, image etc.
    • Allow Nulls: This is a check box which tells us that the column will contain null values or not.

Primary Key allows you to uniquely identify the row in the table and it does not allow null.

Must Remember: Primary key can be applied to one single column or group of the column to uniquely identify a record/row in the table but only one primary key allows in the table.
  • To make the primary key graphically you need to right click on the column that you want to make primary then click on Set Primary Key. This will create a primary key and you can see this primary key by expanding the Columns folder inside the Tables folder.

By Running Query: To create a table by query you need to the following query. This is same as creating the database but this time column gets added to the table.

 
Create table tblGender  
(  
ID int NOT NULL Primary Key,  
Gender nvarchar(50) NOT NULL  
)


When you run above query, this will create the table name as tblGender with two columns ID and Gender. Both columns do not allow null values and ID column is Primary Key. 


Foreign Key used to enforce the data integrity. A foreign key in one table points to the primary key in another table. The foreign key prevents invalid data from being inserted into the foreign key column. The value you enter into the foreign key column has to be one of the values contained in the table that points to.

For example, In tblGender table, we have the ID column. This ID column works as a foreign key for the tblEmployee table.

Let say we have the column values like the following screen.
Graphically: For make the GenderID column of tblEmployee as foreign key we need to perform below steps. 
  • Right click on the table and select Design.

  • Select the column and click on Relationships.
  • A popup window will open and click on Add button and then click on ellipsis button.

  • Next pop window will ask about the relationship that needs to implemented. In which you need to define which is the primary key table and which is the foreign key table and also need to specify the columns. Click on OK.
  • This will create the foreign key on the tblEmployee table and you can see this by expanding the Keys columns of the tblEmployee table.


Using Query: To add foreign key by query we need to run following query.

Syntax:

alter table table_name add constraint constraint_name
Foreign Key (foreign_Key_Column_Name) references Primary_Key_Table (Primary_Key_Column)


Example:

alter table tblEmployee
add constraint tblEmployee_GenderID_FK
Foreign Key (GenderID) references tblGender (ID)


This will create the foreign key on table tblEmployee. Now if you try to insert some value in GenderID column of tblEmployee other than the value that exists in tblGender then a error message will be displayed.

The INSERT statement conflicted with the FOREIGN KEY constraint "tblEmployee_GenderID_FK". The conflict occurred in database "Sample", table "dbo.tblGender", column 'ID'.

 Note: Insert query will be cover in next session.

That’s all for today. Thank You.

Comments

Post a Comment

Popular posts from this blog

SQL Day 11: Group By in SQL Server

Image
LO1: Grouping rows using Group By LO2: Filtering Groups LO3: Difference between Where and Having Clause Group By clause is used to group a selected set of rows into a set of summary rows by the values of one or more columns or expression. It is always used in conjunction with one or more aggregate functions. Let’s say we have a table tblEmployee which contains all the salary of employees including the cities. So if we need to find the maximum salary of employee we can use the MAX(Column_Name) aggregate function to find the highest salary of employees. select MAX(salary) from tblemployee In the same way, we can find the minimum salary and total salary of all the employees using different aggregate functions. select MIN(salary) as [Minimum Salary] from tblemployee select SUM(salary) as [Total Salary] from tblemployee Let’s say we have to find out the employee salary as per the different cities, to achieve this we need to use the Group B...
Keep reading

JAVA Script Executor Code to Click on an Element

Click on an Element in Selenium WebDriver Sometimes click() method does not work because two views are loaded at the same time on a page. In this case, we can use the JavascriptExecutor to get the work done. Below is code to click on an element using Javascript Executor. 1 2 3 4 5 6 7 @FindBy(id = Checkbox. financialAssistanceAccountEmailId ) public WebElement financialAssistanceAccountEmail; //Click to element JavascriptExecutor executor = (JavascriptExecutor) driver; executor. executeScript ( "arguments[0].click()" , financialAssistanceAccountEmail);
Keep reading

SQL Day 2: Creating, Altering and Dropping a Database

Image
LO1: Creating, Altering and Dropping a Database Prerequisite:   Connecting to SQL Server using SSMS After connecting to the database through SSMS first we need to create the database on which different queries will run. Let’s start with creating a Database. A SQL Server Database can be Created, Altered or Dropped in two ways: Graphically using SQL Server Management Studio or Using SQL Query Graphically using SQL Server Management Studio: Within the Object Explorer, right-click on Database folder and select the New Database. When clicked on New Database a new dialog box will open. Here you need to provide the database name and when clicking on OK, SQL Server run the query internally and will create the new database for us. When you expand the database folder then you will see the Sample database here Creating Database Using Query: We can also create the database by executing the query in the query editor. For creating a...
Keep reading