How Constraints works in Sql Server

Cursor- Sql Server
Cursor- Sql Server
0
(0)

Constraints | Primary Key | Unique | Foreign

Q1: What are Constraints?
Used to specify the rules concerning data in the table.
It can be applied on single or multiple columns of a table

NOT NULL – Restricts NULL value from being inserted into a column.
CHECK – Verifies that all values in a field satisfy a condition or with in limit. Check constraint is to enforce integrity.
DEFAULT – Automatically assigns a default value if no value has been specified for the field.
UNIQUE – Ensures unique values to be inserted into the field.
INDEX – Indexes a field providing faster retrieval of records.
PRIMARY KEY – Uniquely identifies each record in a table.

FOREIGN KEY – Ensures referential integrity for a record in another table.
 
 
Q2: What is a Primary Key Constraints?
Uniquely identifies each row in a table. It must contain UNIQUE values and has an implicit NOT NULL constraint.
A table in SQL is strictly restricted to have one and only one primary key, which is comprised of single or multiple fields (columns).
/* Create table with a single field as primary key */
CREATE TABLE CUSTOMERS (     
    ID INT NOT NULL
    Name VARCHAR(255)
    PRIMARY KEY (ID)
);

/* Create table with multiple fields as primary key */

CREATE TABLE CUSTOMERS (     
    ID INT NOT NULL
    LastName VARCHAR(255)
    FirstName VARCHAR(255NOT NULL,
    CONSTRAINT PK_Customer PRIMARY KEY (ID, LastName)
);

/* Set a column as primary key */

ALTER TABLE Customers ADD PRIMARY KEY (ID);

/* Set multiple columns as primary key */

ALTER TABLE Customers ADD CONSTRAINT PK_Customer PRIMARY KEY (ID, LastName);
 
 
Q3: What is a UNIQUE constraint?
Ensures that all values in a column are different. This provides uniqueness for the column(s) and helps identify each row uniquely.
There can be multiple unique constraints defined per table.
/* Create table with a single field as unique */
CREATE TABLE CUSTOMERS (     
    ID INT NOT NULL UNIQUE
    Name VARCHAR(255)    
);
/* Create table with multiple fields as unique */
CREATE TABLE CUSTOMERS (     
    ID INT NOT NULL
    LastName VARCHAR(255)
    FirstName VARCHAR(255NOT NULL,
    CONSTRAINT PK_Customer  UNIQUE (ID, LastName)
);

 

/* Set a column as unique  */
ALTER TABLE Customers ADD UNIQUE (ID);

 

/* Set multiple columns as unique  */
ALTER TABLE Customers ADD CONSTRAINT PK_Customer  UNIQUE (ID, LastName);
 
 
Q4: What is a Foreign Key constraint?
Comprises of single or collection of fields in a table that compulsory refer to the PRIMARY KEY in another table. Foreign key constraint
ensures referential integrity in the relation between two tables. The table with the foreign key constraint is labelled as the child table,
and the table containing the candidate key is labelled as the referenced or parent table.
CREATE TABLE CUSTOMERS (     
    ID INT NOT NULL 
    Name VARCHAR(255)  
    DepartmentId INT
    PRIMARY KEY (ID)
    FOREIGN KEY (DepartmentIdREFERENCES Department(DepartmentId)  
);

 

ALTER TABLE CUSTOMERS ADD FOREIGN KEY (DepartmentId)
REFERENCES Department (Department);

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

1 Trackback / Pingback

  1. Sql Server Interview Questions - etechstuffs

Leave a Reply

Your email address will not be published.


*


10 + seven =