This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing one of the leading SQL interview questions for database freshers.
If you are going to start your career as T-SQL or Database Developer, you must know the different ways to create a Primary Key in SQL Server.
If an interviewer asks this question to you, you can explain a below list of options. Additionally, you can also say the best approach to defining a PRIMARY KEY with user-defined constraint name.
Option 1# – Column level Primary Key:
|
1 2 3 4 5 |
CREATE TABLE tbl_ABC ( ID INT PRIMARY KEY ,Name VARCHAR(10) ) |
Option 2# – Table level Primary Key:
|
1 2 3 4 5 6 |
CREATE TABLE tbl_XYZ ( ID INT PRIMARY KEY ,Name VARCHAR(10) ,CONSTRAINT pk_XYZ_ID PRIMARY KEY(ID) ) |
Option 3# – ALTER the table to ADD Primary Key constraint:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE tbl_PQR ( ID INT NOT NULL -- NOT NULL is mandatory for Primary key ,Name VARCHAR(10) ) ALTER TABLE tbl_PQR ADD CONSTRAINT pk_PQR_ID PRIMARY KEY (ID) |
Option 4# – Primary key with Auto increment:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE tbl_AMN ( ID INT IDENTITY(1,1) -- Auto-increment ,Name VARCHAR(10) ) ALTER TABLE tbl_AMN ADD CONSTRAINT pk_AMN_ID PRIMARY KEY (ID) |
