A composite foreign key is a foreign key consisting of multiple columns.
This article provides an example of creating a composite foreign key using Transact-SQL in SQL Server.
Category: SQL
How to Create a Foreign Key in SQL Server (T-SQL Examples)
In this article I demonstrate how to create a foreign key in SQL Server using Transact-SQL. I demonstrate how to create a foreign key at the time of creating the table (as opposed to updating an existing table).
A foreign key is a column that references another table’s primary key column. This creates a relationship between the tables.
11 Ways to Return Foreign Keys in a SQL Server Database using T-SQL
This article presents eleven different approaches to retrieving foreign key information in SQL Server. Some of these will be more useful than others, depending on the situation.
Some of these methods can be modified to return further information. For example, you could perform joins on various system views to return more data.
11 Ways to Retrieve a Primary Key in SQL Server (T-SQL Examples)
There are often many ways to do the same (or similar) thing in SQL Server. Retrieving all primary keys from a database (or even just the primary key for a given table) is a case in point.
This article presents eleven different approaches to retrieving primary key information in SQL Server.
Some of these will be more useful than others, depending on your exact situation.
How to Create a Primary Key in SQL Server (T-SQL Examples)
This article demonstrates how to create a primary key in SQL Server when creating a table using Transact-SQL.
A primary key is one or more columns that have been configured as the unique identifier for a given table. Primary keys can be used to enforce data integrity in the table.
A table can only have one primary key, and primary keys can only be added to columns that are defined as NOT NULL.
This article demonstrates how to create a primary key in a new table (i.e. when creating the table). If you need to create a primary key in an existing table, see How to Add a Primary Key to an Existing Table in SQL Server.
How to Add a Primary Key to an Existing Table in SQL Server (T-SQL Examples)
This article demonstrates how to add a primary key to an existing table in SQL Server using Transact-SQL.
A primary key is a column that has been configured as the unique identifier for a given table.
You would normally create a primary key constraint when you create the table, but you can also add a primary key to an existing table.
Note that a table can only have one primary key. So you can’t add a primary key if the table already has one.
Also primary keys can only be added to columns that are defined as NOT NULL.
How to Create a User-Defined Data Type Alias in SQL Server using T-SQL
In addition to the many data types available in SQL Server, you also have the option of creating your own data type. Some of these are referred to as “user-defined data types”, while others are referred to as “alias data types”.
A user-defined data type is implemented through a class of an assembly in the Microsoft.NET Framework common language runtime (CLR).
An alias data type is based on a SQL Server native system type. In other words, you use an existing data type for the basis of your alias data type.
Having said that, I’ve seen Microsoft use the term “user-defined data type alias” when referring to an alias data type. I’ve also seen it referred to as simply a “data type alias”.
Either way, this article demonstrates how to a create a user-defined data type alias using Transact-SQL.
datetime vs datetime2 in SQL Server: What’s the Difference?
This article explores the main differences between the datetime and datetime2 data types in SQL Server.
If you’re not sure which one to use, use datetime2 (see its advantages below).
Converting Between Date & Time Data Types in SQL Server (T-SQL Examples)
When you convert between date and time data types in SQL Server, you need to be mindful of how the new data type will handle the value you’re trying to assign to it.
In some cases you might lose part of the value, in other cases you might gain a bunch of zeros (increasing storage size in the process). You may also end up with a value that’s been rounded up.
The following articles contain examples of conversions between the different date and time data types, with a particular focus on the issues I just mentioned.
Convert ‘datetimeoffset’ to ‘time’ in SQL Server (T-SQL Examples)
If you have a datetimeoffset value, but you don’t need the date and time zone offset part, converting it to time will save you a lot of storage space (while removing unnecessary details from the value). This article contains examples of converting a datetimeoffset value to a time value in SQL Server.