- \n
- Primary Keys ensures that records in the table are unique<\/strong>. <\/span>When you create a primary key on a column, a value inserted into that column can not be re-inserted. For example, you create a primary key in the ID column and you inserted a value of 1 to this column. You can not insert same value(1) to this column again. So, if you have a unique column, you can create a primary key on that column.<\/li>\n
- A null value can not be inserted<\/strong> to Primary Key Column.<\/li>\n
- If you create a primary key, SQL Server will create a clustered index by default<\/strong>. But you can force it to create nonclustered index. You may want to read the article named “Differences Between Clustered Index and Non Clustered Index<\/a>“<\/li>\n
- We can create only one<\/strong> Primary Key in a table.<\/span><\/li>\n
- We can create Primary Key by combining multiple columns,\u00a0 This is called the Composite Key<\/strong>.<\/span><\/li>\n
- Primary Keys can be related with Foreigns Keys<\/li>\n<\/ul>\n
What is Foreign Key?<\/h2>\n
- \n
- Foreign Key is used to relate two tables to each other in relational databases.<\/li>\n
- It can be created on a column or a combination of multiple columns. It is also referred to as a reference key.<\/li>\n
- Foreign Keys have not to be unique.<\/li>\n
- We can create more than one foreing key in the table.<\/li>\n
- Sql Foreign Key values \u200b\u200bare related with Primary Key, which is located in a different table.<\/li>\n
- If you create a Foreign Key, SQL Server will not create an index by default. But in terms of performance, you usually need to create an index on a foreign key column.<\/li>\n
- In SQL databases, the primary key in one table and the foreign key in the other table are used to establish the relationship between the two tables.<\/li>\n
- Unlike Primary Key, Foreing Key can be null.<\/li>\n<\/ul>\n
Primary Key and Foreign Key Relationship<\/h2>\n
Another feature of Primary Keys is that it can be related with Foreing Keys. <\/span><\/p>\n
For example, you create a primary key in the column named ID in the City Table. The other column name is CityName. Let’s imagine that there is another table called CityFeatures. We also have a column named CityID in the CityFeature table. This column contains information about feautes of Cities. We can create a Primary Key on the ID column of City table and we can relate this primary key with the Foreing Key on the CityID column of the CityFeatures table.<\/p>\n
Thus, if you do not delete all records belonging to the relevant city in the CityFeatures table, you can not delete the record of the relevant city in the city table. This way we can protect our data integrity. Lets make an example practically.<\/p>\n
Primary Key and Foreign Key Example<\/h2>\n
Create City and CityFeatures Tables<\/h4>\n
Let’s create the City and CityFeatures tables with 2 scripts below.<\/p>\n
Create the City table with the following script.<\/p>\n
USE [Test]\nGO\nSET ANSI_NULLS ON\nGO\nSET QUOTED_IDENTIFIER ON\nGO\nSET ANSI_PADDING ON\nGO\nCREATE TABLE [dbo].[City](\n[ID] [int] IDENTITY(1,1) NOT NULL,\n[CityName] [varchar](250) NULL,\n\u00a0CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED\n(\n[ID] ASC\n)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) \nON [PRIMARY]\n) ON [PRIMARY]\nGO\nSET ANSI_PADDING OFF\nGO<\/pre>\n
Create the CityFeatures table with the below script.<\/p>\n
USE [Test]\nGO\nSET ANSI_NULLS ON\nGO\nSET QUOTED_IDENTIFIER ON\nGO\nSET ANSI_PADDING ON\nGO\n\nCREATE TABLE [dbo].[CityFeatures](\n[ID] [int] IDENTITY(1,1) NOT NULL,\n[CityID] [int] NOT NULL,\n[FamousCook] [varchar](50) NULL,\n[FamousDrink] [varchar](50) NULL\n) ON [PRIMARY]\nGO\nSET ANSI_PADDING OFF\nGO<\/pre>\n
Create a Foreing Key<\/h4>\n
With the following script, create a foreing key for the CityID column in the CityFeatures table, whose primary column is the ID column in the City table.<\/p>\n
As you can see in the Script, we are creating\u00a0 a ForeingKey Constraint in the CityID column in the\u00a0 CityFeatures table. This foreign key’s primary key is the primary key we created on the ID column of City table.<\/p>\n
ALTER TABLE [dbo].[CityFeatures]\u00a0 WITH CHECK ADD CONSTRAINT [FK_CityFeatures_City] FOREIGN KEY([CityID])\nREFERENCES [dbo].[City] ([ID])\nGO\nALTER TABLE [dbo].[CityFeatures] CHECK CONSTRAINT [FK_CityFeatures_City]\nGO<\/pre>\n
Insert Data to Tables<\/h4>\n
Add a record to the city table with the following script. As you can see, we just inserted value for CityName column. Because, we created ID column of City table as auto increment.. So when each record is inserted, the ID column will increase automatically.<\/p>\n
INSERT INTO [dbo].[City]([CityName]) VALUES ('Newyork')<\/pre>\nLet’s try to add a record to CityFeatures as follows.<\/p>\n
INSERT INTO [dbo].[CityFeatures]([CityID],[FamousCook],[FamousDrink]) VALUES (2,'x','y')<\/pre>\n
When we run the above script, we will receive an error like below.<\/p>\n
The Insert statement conflicted with the FOREIGN KEY constraint FK_CityFeatures_City. The conflict occured in database “Test”, table “dbo.City”, column ‘ID’.<\/span><\/em><\/p>\n
![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/06\/Ads\u0131z-29.png\")
<\/p>\nThe reason for the error is that there is no city with 2 IDs in the city table.<\/p>\n
When we run the script as follows, we will see that the record has been inserted.<\/p>\n
INSERT INTO [dbo].[CityFeatures]([CityID],[FamousCook],[FamousDrink]) VALUES (1,'x','y')<\/pre>\n
When we try to delete the city table with the following script, we will receive an error like below.<\/p>\n
USE [Test]\nGO\nDROP TABLE [dbo].[City]\nGO<\/pre>\n
Could not drop object ‘dbo.City’ because it is referenced by a FOREIGN KEY constraint.<\/em><\/span><\/p>\n
![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/06\/Ads\u0131z-30.png\")
<\/p>\nIn order to delete the city table, we first need to delete the foreign key in the CityFeatures table.<\/p>\n
Now, let’s try to delete the row with ID 1 in the city table by using below script.<\/p>\n
USE [Test]\nGO\nDELETE FROM [dbo].[City] where ID=1\nGO<\/pre>\n
The script will fail as below, because there is no record with CityID value 1 in the CityFeatures table.<\/p>\n
The DELETE statement conflicted with the REFERENCE constraint “FK_CityFeatures_City”.<\/span><\/em><\/p>\n
![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/06\/Ads\u0131z-31.png\")
<\/p>\nIf we first delete the values \u200b\u200bwith ID 1 in the CityFeatures as follows and then run the above script again, delete script will successfully finish.<\/p>\n
USE [Test]\nGO\nDELETE FROM [dbo].[CityFeatures] where CityID=1\nGO<\/pre>\n
As you can see in the example, we can guarantee our data integrity by using Primary Key and Foreign Key relationship.<\/p>\n
Can primary keys be null?<\/h3>\n
No, primary keys can not be null. We have mentioned in the beginning of the article.<\/p>\n
Can we have primary key on multiple columns?<\/h3>\n
Yes, we can create Primary Key by combining multiple columns,\u00a0 This is called the Composite Key.<\/p>\n
Can a Primary Key be a Foreign Key?<\/h3>\n
If you want to create one-to-one relationship you can set foreign key as primary key. That is, setting the foreign key column as primary key may be contrary to the foreign key logic. Because usually we use foreign keys for one-to-many relationship.<\/p>\n
Can a Primary Key have multiple Foreign Keys?<\/h3>\n
Yes, we can create multiple foreign keys for a primary key. But foreing keys must be on different tables. Because each foreign key reference same primary key value. Therefore, it doesnt make sense creating more than one foreign key for a same primary key. Because values of two foreign key columns will be same. Each foreing key column value that references same primary key can have different values, but it does not make sense too.<\/p>\n
Consider our example, ID is our primary key and CityID is our foreign key. Suppose that we have a column CityID2 in CityFeatures table and you want to create a foreing key column on CityID2 that references ID column as primary key. You can do it. But why?<\/p>\n
But, it makes more sense to create foreign key column on a different table that reference ID column in the City table.<\/p>\n
Can we update primary key in SQL Server?<\/h3>\n
Yes we can update primary keys, but new value of our primary key must require primary key requirements. like uniqueness, not null, etc. Also, when creating the foreign key, you must create it with ON UPDATE CASCADE. If you specify \u201cON UPDATE CASCADE\u201d when creating Foreign Key, this ensures that when there is an update in the column in the primary table, this update is reflected in the table with the foreign key. You may want to read the article “ON DELETE CASCADE and ON UPDATE CASCADE in SQL Server<\/a>” for detailed information.<\/p>\n
Important Note:<\/strong> Even if you create foreign key with ON UPDATE CASCADE, be careful that when updating primary key value. Because this cause updating all foreign key values and you may have millions of records in foreign key table.<\/p>\n
If you do not create Foreign Key with ON UPDATE CASCADE and try to update primary key column you will receive the below error.<\/p>\n
Msg 547, Level 16, State 0, Line 4 <\/em><\/span><\/p>\n
The UPDATE statement conflicted with the REFERENCE constraint “FK_CityFeatures_City”. The conflict occurred in database “Test”, table “dbo.CityFeatures”, column ‘CityID’. <\/em><\/span><\/p>\n
The statement has been terminated.<\/em><\/span><\/p>\n
![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/06\/Ads\u0131z-37.png\")
<\/p>\nCan we alter\/change primary key data type?<\/h3>\n
We can not alter primary key column’s data type. If you try to alter the table as follows, you will receive the below error.<\/p>\n
![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/06\/Ads\u0131z-38.png\")
<\/p>\nYou can change primary key data type as follows;<\/p>\n
- \n
- Drop all related foreign keys<\/li>\n
- Change primary key column’s data type<\/li>\n
- Change all related foreign keys column’s data type<\/li>\n
- Create foreign key again.<\/li>\n<\/ul>\n
Change Primary Key Data Type Example<\/h3>\n
Consider our example. We have a primary key on ID column in City table. Its data type is int. Lets change int to bigint.<\/p>\n
Drop Foreign Key<\/h4>\n
USE [Test]\nGO\n\nALTER TABLE [dbo].[CityFeatures] DROP CONSTRAINT [FK_CityFeatures_City]<\/pre>\n
Change Primary Key Column’s Data Type<\/h4>\n
USE [Test]\nGO\nALTER TABLE [dbo].[City] ALTER COLUMN [ID] bigint not null<\/pre>\n
Change Foreign Key Column’s Data Type<\/h4>\n
USE [Test]\nGO\nALTER TABLE [dbo].[CityFeatures] ALTER COLUMN [CityID] bigint not null<\/pre>\n
Create Foreign Key on same column again<\/h4>\n
USE [Test]\nGO\nALTER TABLE [dbo].[CityFeatures] WITH CHECK ADD CONSTRAINT [FK_CityFeatures_City] FOREIGN KEY([CityID])\nREFERENCES [dbo].[City] ([ID]) ON UPDATE CASCADE\nGO\nALTER TABLE [dbo].[CityFeatures] CHECK CONSTRAINT [FK_CityFeatures_City]\nGO<\/pre>\n
Can foreign key be null?<\/h3>\n
Yes, If you create the foreign key column as null, you can create a Foreign Key on that column and you can insert null value to foreign key column. If you do not create foreign key column as null, you can not insert NULL values into foreign key columns. If you try to insert null values you will receive the error as follows.<\/p>\n
INSERT INTO [dbo].[CityFeatures]([CityID],[FamousCook],[FamousDrink]) VALUES (null,'x','y')<\/pre>\n
Msg 515, Level 16, State 2, Line 2 <\/em><\/span><\/p>\n
Cannot insert the value NULL into column ‘CityID’, table ‘Test.dbo.CityFeatures’; column does not allow nulls. INSERT fails. <\/em><\/span><\/p>\n
The statement has been terminated.<\/em><\/span><\/p>\n
![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/06\/Ads\u0131z-32.png\")
<\/p>\nTo insert null values to foreign key column, you must create table as follows;<\/p>\n
USE [Test]\nGO\nSET ANSI_NULLS ON\nGO\nSET QUOTED_IDENTIFIER ON\nGO\nSET ANSI_PADDING ON\nGO\nCREATE TABLE [dbo].[CityFeatures](\n[ID] [int] IDENTITY(1,1) NOT NULL,\n[CityID] [int] NULL,\n[FamousCook] [varchar](50) NULL,\n[FamousDrink] [varchar](50) NULL\n) ON [PRIMARY]\nGO\nSET ANSI_PADDING OFF\nGO<\/pre>\n
Can a foreign key be part of a primary key?<\/h3>\n
Yes, a foreing key be part of a primary key. Lets try it in our example. First delete CityFeatures table and create it again by using the below script.<\/p>\n
USE [Test]\nGO\nSET ANSI_NULLS ON\nGO\nSET QUOTED_IDENTIFIER ON\nGO\nSET ANSI_PADDING ON\nGO\nCREATE TABLE [dbo].[CityFeatures](\n[ID] [int] IDENTITY(1,1) NOT NULL,\n[CityID] [int] NOT NULL,\n[FamousCook] [varchar](50) NULL,\n[FamousDrink] [varchar](50) NULL,\nPRIMARY KEY (ID, CityID),\nFOREIGN KEY (CityID) REFERENCES City(ID)\n) ON [PRIMARY]\nGO\nSET ANSI_PADDING OFF\nGO<\/pre>\n
Can foreign key be duplicate?<\/h3>\n
Yes, Foreign Keys can be duplicate. They do not have to be unique. You can try to insert same insert value into CityFeatures table.<\/p>\n
Declare @count int;\nSET @count=0;\nWhile (@count<10)\nBEGIN\nINSERT INTO [dbo].[CityFeatures]([CityID],[FamousCook],[FamousDrink]) VALUES (1,'x','z')\nSET @count=@count+1\nEND<\/pre>\n
![\"\"](\"https:\/\/dbtut.com\/wp-content\/uploads\/2018\/06\/Ads\u0131z-33.png\")
<\/p>\nYou may want to read the articles below.<\/p>\n
“Differences Between Primary Key and Unique Constraint<\/a>“,<\/p>\n
“ON DELETE CASCADE and ON UPDATE CASCADE in SQL Server<\/a>”<\/p>\n
“Cannot truncate table because it is being referenced by a FOREIGN KEY constraint<\/a>“,<\/p>\n
“The constraint \u2018PK_X\u2019 is being referenced by table \u2018X\u2019, foreign key constraint \u2018FK_X\u2019<\/a>“,<\/p>\n
“The INSERT statement conflicted with the FOREIGN KEY constraint<\/a>“,<\/p>\n
- A null value can not be inserted<\/strong> to Primary Key Column.<\/li>\n