Sunday, January 27, 2008

Primary keys without "real" tables

Have you queried some when information_schema.table_constraints or sys.key_constraints to discover a primary keys in your database? And as a result from select * from information_schema.table_constraints you noticed that there are primary keys without TABLE_SCHEMA and TABLE_NAME in resultset?
You can be confused with this but the explanation is simple. Your primary keys defined in table-valued functions have been listed here. So if you have a function defined as

CREATE FUNCTION [dbo].[fnTest]
(
@foo int
)
returns @resultTable table (test1 int, test2 int primary key (test1))
as
begin
insert into @resultTable(test1, test2)
select 1, @foo

return
end

you will have as CONSTRAINT_NAME something like "PK__fnTest__2D27B809" and TABLE_SCHEMA and TABLE_NAME will be NULL.

No comments: