none
How to create candidate and super key? RRS feed

  • Question

  • I did lot of google but did not get script for candidate and super key. I do not have syntax to create candidate and super key in sql.

    Please help!

    Monday, June 1, 2015 1:30 PM

Answers

  • You don't directly define candidate or super keys in SQL Server.  Those concepts are more from a logical database model, but in SQL Server Transact-Sql you are defining the physical model.  As you know, a super key is any combinations of columns where you are guaranteed that no two rows have the same value in all of the columns.  A candidate key is a super key with the property that if you remove any column from that super key, it is no longer a super key (that is, in some sense it is a "minimal" super key).

    Both candidate keys and super keys are defined in SQL Server as either primary key constraints, unique key constraints, or unique indexes.  They are functionally the same except that you can only have one primary key constraint and all the columns in primary key constraints must be marked NOT NULL, that is, none of the columns is allowed to contain a NULL value.

    To see sample syntax for creating a primary key, go to https://msdn.microsoft.com/en-US/library/ms189039.aspx and scroll down to the Using Transact-SQL section.  You can find sample syntax for unique keys at https://msdn.microsoft.com/en-US/library/ms190024.aspx and sample syntax for creating unique indexes at https://msdn.microsoft.com/en-US/library/ms187019.aspx.

    For almost every table, you want a primary key or unique key for every candidate key the table has.  You don't generally define primary keys or unique keys or unique indexes for super keys that are not candidate keys.  Sometimes that might be done for performance reasons, but it is quite rare to do this.

    Tom

    Tuesday, June 2, 2015 2:01 AM

All replies