Naming the Constraint in sql server

Dynamically naming the constraint:

Constraint is nothing but a condition placed on the column or object. Let's see a small example to create a Primary Key constraint.

CREATE TABLE SANTHOSH_TABLE (NAME VARCHAR(100),ID INT PRIMARY KEY,DOB DATETIME)

---- INSERT RECORDS INTO THE TABLE ----------
INSERT INTO SANTHOSH_TABLE VALUES('VENKAT',1,'1/1/2010')
INSERT INTO SANTHOSH_TABLE VALUES('LINGAM',2,'1/1/2009')
INSERT INTO SANTHOSH_TABLE VALUES('ILAM',3,'1/2/2010')
INSERT INTO SANTHOSH_TABLE VALUES('SANTHOSH',4,'1/3/2010')
INSERT INTO SANTHOSH_TABLE VALUES('SIVARAM',5,'1/4/2010')

SELECT * FROM SANTHOSH_TABLE

The Primary key constraint name is
PK__SANTHOSH__3214EC2762AFA012 which looks tougher to remember the name. This name is automatically generated by SQL Server. Can we have a specific name to the Constriant?

Below query will drop the constraint located in to your table.

--------- DROP CONSTRAINT ON THE TABLE
ALTER TABLE SANTHOSH_TABLE DROP CONSTRAINT PK__SANTHOSH__3214EC2762AFA012

Now, lets see how to name a constraint.

---- DROPPING THE TABLE------------
DROP TABLE SANTHOSH_TABLE

---- NAMING THE CONSTRAINTS EXPLICITLY------------
CREATE TABLE SANTHOSH_TABLE (NAME VARCHAR(100),ID INT ,DOB DATETIME,
CONSTRAINT PK_ID_SANTHOSH PRIMARY KEY (ID))

Now, you constraint name is PK_ID_SANTHOSH.

Happy Learning!!!

Cheers,

Venkatesan Prabu .J