Identity Column, Primary Key and Foreign Key

-


Hello, and welcome to the blog. In this blog we are going to talk about "Identity columns, Primary Key and Foreign Key".

In the previous blog we have seen some constraints and I told you there are more coming and here they are now. These constraints hold way too much powers as compare to what we have learnt in previous blog. Believe me if you these have arrows in your quiver, it is very easy for you to conquer the practices of designing a fully functional database. So let's not create more hype about this and start exploring theses things.

In this blog we are going to talk about
  1. Identity Column
  2. Primary Key
  3. Foreign Key
Let's start the discussion with

1) Identity Column:

  1. When you want to create a column whose value should get incremented every time you insert a new record then IDENTITY COLUMN is the thing you should be looking for.
  2. IDENTITY column is very helpful when we want to create a unique column. Because of identity column you don't have to worry about having unique values for a column.
  3. Now a days almost every time a person wants to create a PRIMARY KEY which is our next topic he uses Identity column as base for it and then apply Primary Key to that column.
  4. The keyword "Identity" that we are using can be different for different DBMS software. For example: For this feature AUTO-INCREMENT keyword is used in MySQL and IDENTITY keyword is used in Microsoft SQL Server.
  5. One more thing I would like to add is that you don't need to specify value for identity column while inserting a record. We will see an example to understand this better.
  6. Now let's know the syntax for creating a a identity column:
    Syntax :
    IDENTITY(seed , increment_by)
    "seed" is the starting value that is the very first row which is inserted will hold that value and "increment_by" is the value by which the value of column is incremented every-time a new record is inserted
  7. Example:
    CREATE TABLE [dbo].[Employee2](
    [Id] [int] IDENTITY(1,1),
    [FirstName] [varchar](50) NOT NULL,
    [LastName] [varchar](50) NULL,
    [Department] [varchar](50) NULL,
    [Salary] [decimal](18, 2) ,
    [Designation] [varchar](50) 
    )

  8. Now "Id" column of Employee table is IDENTITY column and its value will be auto incremented
  9. The last thing you must know is that you can't alter a existing column to identity column you must specify Identity column at the time of creating table.
  10. Now let's see how IDENTITY column affects insert statement.
    1. insert into Employee2 values (1,'John','Doe','Sales',12000,'Trainee')

    2. We got error saying "An explicit value for the identity column in table 'Employee2' can only be specified when a column list is used and IDENTITY_INSERT is ON." As I said you can't specify value for Identity column and we have specified value for Identity column so we got this error.
    3. Let' see same query without specifying value for identity column
    4. insert into Employee2 values ('John','Doe','Sales',12000,'Trainee')

    5. Now the Row is inserted successfully.

    6. Let's add one more record to check if the value in Identity column gets incremented or not.
    7. insert into Employee2 values ('Marie','Krook','HR',15000,'Trainee')

2) Primary Key

  1. It is very important to have a individuality as a person and it is same for database records as well.
  2. A database table must have a column or a combination of column that contains a value by which you can identify each and every record in the table.
  3. When a column value is used to uniquely identify each and every record then that column is a "PRIMARY KEY" Column.
  4. Data in Primary Key column is always unique and not null. Null values are not accepted in a primary key column.
  5. As data in primary key must be unique "IDENTITY" column is used as a base for "Primary Key" column. And this happens almost every time.
  6. When you define a column as a "Primary key" for that table, then database engine enforces data uniqueness by automatically creating unique index for Primary key column because of which access of data becomes faster when you use primary key column in your query.
  7. Note: Primary key cannot be applied to nullable column
  8. Syntax for creating Primary key
    CREATE TABLE [dbo].[Employee2](
    [Id] [int] IDENTITY(1,1) PRIMARY KEY,
    [FirstName] [varchar](50) NOT NULL,
    [LastName] [varchar](50) NULL,
    [Department] [varchar](50) NULL,
    [Salary] [decimal](18, 2) ,
    [Designation] [varchar](50) 
    )
    OR
    CREATE TABLE [dbo].[Employee2](
    [Id] [int] IDENTITY(1,1),
    [FirstName] [varchar](50) NOT NULL,
    [LastName] [varchar](50) NULL,
    [Department] [varchar](50) NULL,
    [Salary] [decimal](18, 2) ,
    [Designation] [varchar](50) 
    [constraint PK_Employee
    Primary Key (Id)
    )

  9. You can add Primary Key constraint to an existing column. For that we have syntax like:
    ALTER TABLE Employee
    ADD PRIMARY KEY(Id)
    or
    ALTER TABLE Employee
    ADD CONSTRAINT PK_Employee
    ADD PRIMARY KEY(Id)

  10. Now let's finally see syntax for deleting primary key:
    ALTER TABLE Employee
    DROP CONSTRAINT Pk_Employee

here comes the final thing

3) Foreign Key

  1. FOREIGN KEY is the most important concept when we will talk about joins. normalization in future, foreign key plays a very important role.
  2. "Foreign key" is a column in your table which represents information or record of other table.
  3. "Foreign key" mainly refers to Primary Key in another table. It's basically a link between two or more tables.
  4. When you add foreign key constraint to a column, it also make sure that you are not inserting inappropriate data, which is not present in the referring table.
  5. I have created a "Employee" table in which "DepartmentId" is a foreign key then I can't add "DepartmentId" which is not present in "Department" table. For example I can't add DepartmentID=11 in "Employee" table as I don't have any record in my "Department" table with Id as 11.

  6. Now let's see syntax for adding foreign key while creating table:
    CREATE TABLE [dbo].[Employee2](
    [Id] [int] IDENTITY(1,1) PRIMARY KEY,
    [FirstName] [varchar](50) NOT NULL,
    [LastName] [varchar](50) NULL,
    [DepartmentId] [int] FOREIGN KEY references DEPARTMENT(Id),
    [Salary] [decimal](18, 2) ,
    [Designation] [varchar](50) 
    )
    OR
    CREATE TABLE [dbo].[Employee2](
    [Id] [int] IDENTITY(1,1),
    [FirstName] [varchar](50) NOT NULL,
    [LastName] [varchar](50) NULL,
    [DepartmentId] [int],
    [Salary] [decimal](18, 2) ,
    [Designation] [varchar](50),
    constraint FK_Department FOREIGN KEY(DepartmentId)
    REFERENCES DEPARTMENT(Id) 
    )

  7. Syntax for adding foreign key after creating table
    ALTER TABLE Employee
    ADD constraint FK_Department
    FOREIGN KEY(DepartmentId)
    REFERENCES Department(Id)

  8. now, finally let's learn to delete a primary key
    ALTER TABLE Employee
    DROP constraint FK_Department 

Congratulation you have understood some of the most important topic of SQL. See you  in some another blog till then "Keep coding, keep learning because trust me you can make it big".

"Have a great day"

Comments

Post a Comment

Popular posts from this blog

SQL Joins | Inner Join | Left Join | Right Join | Full Join | Self Join

-
Image
Hello, and welcome to the blog. In this blog we are going to talk about " Joins ". In the  previous blog  we have seen most important topic of Primary Key, Foreign Key and now its time to join some of the tables. So, In this blog we are going to talk about What is join and its purpose Type Of Joins Inner Join Left Join Right Join Full Join Self Join Let's  start with 1) What is join and its purpose There is a clause in SQL called Join. And its purpose is to combine rows from two or more tables which are connected with each other by a common column. When I say connected with each other by a common column I am talking about Foreign Key which we have covered in previous blog. Although if you haven't enforced foreign key for this blog but we do have a common column then also you can perform jo...
Read more

Update, Delete and Truncate Commands

-
Image
Hello, and welcome to the blog. In this blog we are going to talk about "Updating and deleting data". We learn about "Inserting and Retrieving Data" in  this blog . Now its time to learn updating and deleting our mistakes. Yes with the help of the UPDATE and DELETE  statements  you can actually update and delete wrong entries in your database respectively. So in this blog we are going too talk about  UPDATE statement DELETE  statement TRUNCATE command Let's start with 1) UPDATE statement : UPDATE statement is used to update a specific record or a bunch of records which exists in your database table. If you made a mistake while inserting data in your database then UPDATE statement is the loophole through which you can correct it. Let's see a example: If you can see in our table I have made a mistake that SALARY  of "Sales Per...
Read more

More clauses in SQL

-
Image
Hello, and welcome to the blog. In this blog we are going to talk about "More clauses in SQL". We learn about "WHERE" clause in this blog . Now its time to learn more clause which are going to help us to be more specific about retrieving data from Database and eventually its gonna make us more and more efficient in handling data in Database. So, In this blog we are going to talk about GROUP BY clause HAVING clause ORDER BY clause Now, let's talk about 1) GROUP BY clause This clause is often used with Aggregate Functions (which we have learnt in previous blog ) so that we can group our result with a particular column. I know it sound little confusing but we will solve this confusion with the help of example. Let's consider a scenario where I told you to count number of people working in each department with our existing Employee table. So then you might write a query like this: select count(*) from Employee where Department='IT' Then you will keep rep...
Read more