Wednesday 29 May 2013

what type of index create when we create a foreign key on a table ?

No index will be create when we create a foreign key on a table.

Check the below code :

CREATE TABLE sal 
  ( 
     name CHAR(5) NOT NULL, 
     sal  INT 
  ) 

INSERT INTO sal 
VALUES     ('a', 
            100), 
            ('b', 
             200), 
            ('c', 
             300), 
            ('d', 
             400), 
            ('e', 
             100), 
            ('f', 
             200), 
            ('g', 
             400), 
            ('h', 
             300), 
            ('i', 
             500) 

SELECT * 
FROM   sal 

-- only HEAP index will be present 
SELECT Object_name(object_id), 
       * 
FROM   sys.indexes 
WHERE  object_id IN ( Object_id('sal') ) 

CREATE TABLE sal2 
  ( 
     name CHAR(5), 
     sal  INT 
  ) 

-- only HEAP index will be present 
SELECT Object_name(object_id), 
       * 
FROM   sys.indexes 
WHERE  object_id IN ( Object_id('sal2') ) 

ALTER TABLE sal 
  ADD CONSTRAINT pk_sal PRIMARY KEY(name) 

-- Now CLUSTERED index will be created default with primary key. 
SELECT Object_name(object_id), 
       * 
FROM   sys.indexes 
WHERE  object_id IN ( Object_id('sal') ) 

ALTER TABLE sal2 
  ADD CONSTRAINT ref_sal_name FOREIGN KEY(name) REFERENCES sal(name) 

-- After creating the foreign key constraint also, we have only HEAP index on sal2 table. 
SELECT Object_name(object_id), 
       * 
FROM   sys.indexes 
WHERE  object_id IN ( Object_id('sal2') ) 

SELECT Object_name(object_id), 
       * 
FROM   sys.indexes 
WHERE  object_id IN ( Object_id('sal'), Object_id('sal2') ) 

No comments:

Post a Comment