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