Category Archives: Notes

All Articles

Script to Create Foreign Key on the Compound Primary Key

Leave a reply

Compound Primary key is a primary key which is created on more than one column. Now the questions is how to create the foreign key for the compound primary key where it references more than one column.

Check the below example.

create table employee
(
	empID int not null,
	SSN int not null,
	name varchar(20)
)


ALTER TABLE [employee]
ADD CONSTRAINT pk_employee PRIMARY KEY (empID, SSN)


create table EmpDetail
(
		empID int,
		SSN int,
		address varchar(20),
		city varchar(20),
		pin varchar(20)
)

ALTER TABLE dbo.empDetail
   ADD CONSTRAINT FK_Employee
   FOREIGN KEY(empID, SSN)
   REFERENCES dbo.employee(empID, SSN)


SELECT
    tc.TABLE_NAME,
    tc.CONSTRAINT_NAME, 
    ccu.COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu 
      ON ccu.TABLE_NAME = tc.TABLE_NAME AND ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE
    tc.TABLE_NAME IN ('employee','employeeDetail')

Primary Key, Unique Key Constraints – Clustered Index and Non Clustered Index

Leave a reply

You can use the below script to create the Primary Key on the already existing tables. Primary key enforces a uniqueness in the column and created the clustered index as default.

Primary key will not allow NULL values.

-- Adding the NON NULL constraint
ALTER TABLE [TableName]	 
ALTER COLUMN PK_ColumnName int NOT NULL

--Script to add the primary key on the existing table
ALTER TABLE [TableName]
ADD CONSTRAINT pk_ConstraintName PRIMARY KEY (PK_ColumnName)

If you want to define or create the non-clustered index on the existing table, you can use the below script. If the data in the column is unique, you can create the Unique Constraint as well.

Unique Key enforces uniqueness of the column on which they are defined. Unique Key creates a non-clustered index on the column. Unique Key allows only one NULL Value.

--script to create non-clustered Index
create index IX_ColumName on TableName(ColumnName)
--script to create Unique constraint on the existing table
ALTER TABLE TableName ADD CONSTRAINT ConstraintName UNIQUE(ColumnName)

Again Awarded as MVP for 2012-2013

4 Replies

Dear Friends,

I want to share a good news with you all, I am again 3rd time awarded as MVP in SQL Server. Thanks for reading my site, I will keep posting the best article and always there to solve your queries.

Thanks,
Jugal Shah

You will see the 2012 in below award shortly -:)

Using PowerShell to Register All SQL Instances Into Central Management Server

1 Reply

Problem

Managing multiple SQL Servers has its challenges. One of the big challenges is having to collect data from each of the servers you manage and figure out which servers need attention. SQL Server has introduced a new feature called Central Management repository which can be used to manage multiple instances. One of the challenges to using CMS is that you have to register all the SQL Servers manually into CMS.  The below article will guide you on how to register hundreds of servers in a second for SQL Server CMS.

Solution

http://www.mssqltips.com/sqlservertip/2658/using-powershell-to-register-all-sql-instances-into-central-management-server/