Author: Latheesh NK

SQL

FOREIGN KEY Constraints in SQL Server

Foreign Key Constraint is a type of constraint in SQL Server to enforce a relation between two tables with a column or a set of columns to ensure a data integrity.

Let us quickly look at how do we create a foreign key in SQL Server

Create Table ParentTable (ParentCol int Primary Key NOT NULL)
Create Table ChildTable (ChildCol int Primary Key NOT NULL, ParentCol int NULL references ParentTable(ParentCol))

The above will create foreign key from ChildTable to ParentTable on column ParentCol. If we carefully look at the definition of ParentCol in ChildTable, we can see its a NULL-able column. If there is a requirement that we should not have NULL value, then we can make the column as NOT NULL.

Cascade Actions:

As we see in the above screenshot, delete_action/update_action are called as cascade actions. This can be used to define the actions on delete/update operations on Primary table. Let us examine with an example as below.

First off, let us try to insert some data in both tables and try to delete from Parent which has reference record in ChildTable.

Insert into ParentTable Select 100
Insert into ParentTable Select 200
Insert into ParentTable Select 300
Insert into ChildTable Select 1, NULL
Insert into ChildTable Select 2, NULL
Insert into ChildTable Select 3,300

Select * From ParentTable
Select * From ChildTable

--Now try to delete a record from ParentTable which has a corresponding record in ChildTable
Delete From ParentTable where ParentCol =300

The DELETE operation fails as there is a record in ChildTable which prevents the DELETE operation on ParentTable.

Now, let us have a CASCADE action on DELETE to define delete on ChildTable as well.

ALTER TABLE ChildTable DROP constraint[FK__ChildTabl__Paren__58D1301D]

ALTER TABLE [dbo].ChildTable  WITH CHECK ADD  CONSTRAINT [FK__ChildTabl__Paren__58D1301D] FOREIGN KEY(ParentCol)
REFERENCES [dbo].ParentTable (ParentCol)
ON DELETE CASCADE
GO

/*Now try to delete a record from ParentTable which has a corresponding record in ChildTable*/
Delete From ParentTable where ParentCol =300


Select * From ParentTable
Select * From ChildTable

Now, we can see the DELETE operation is successful and the Delete action on the table has been changed to Cascade as below.

Points to ponder:

  1. You need to have the column in the Parent Table part of Primary Key
  2. There would not be any indexes created on the Child table for the referenced key by default. Sometimes, indexing of the foreign key column helps a lots on transactional databases/tables.
  3. Foreign key constraints are advised to create when two tables are tightly coupled and if we use these tables in a join condition to check its existence on Parent tables, because the key constraints takes care of it by default.
  4. If we have a CASCADE update, then SQL Server will not allow to create Instead of Trigger on the table.

If you enjoyed this blog post, feel free to share it with your friends!

SQL

DEFAULT constraint in SQL Server

This is the *simplest* constraint of all other constraints to explain, DEFAULT constraint will set a default value for a column if a value is not specified for the column.

How do we declare a default constraint?

  • While declaring a table
CREATE TABLE People (
    ID int NOT NULL,
    FirstName varchar(255),
    City varchar(255) DEFAULT 'Chennai'
);
--The below insert operation is not specified any value for City, but SQL Server uses default value 'Chennai'
Insert into People(ID,FirstName) Values(1,'Latheesh NK')

--The below insert specified a value for City
Insert into People(ID,FirstName,City) Values(2,'NK Latheesh','Cochin')

Select * from People

Drop Table People
  • Altering an existing table

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
    a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable and you want the Default Value used for Existing records.
If your Column is NOT NULL, then it will automatically use the Default Value for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol‘s value, then it will Default to 0.
If you insert a Record and Specify SomeCol‘s value as NULL (and your column allows nulls), then the Default-Constraint will not be used and NULL will be inserted as the Value.

If you want to add multiple columns you can do it this way for example:

ALTER TABLE YourTable
    ADD Column1 INT NOT NULL DEFAULT 0,
        Column2 INT NOT NULL DEFAULT 1,
        Column3 VARCHAR(50) DEFAULT 'Hello'

Can we drop a DEFAULT constraint?

Yes, we can very well DROP a default constraint.

Can we disable/enable DEFAULT constraint?

No, we cannot disable/enable default constraint, we can only drop and recreate default constraint in SQL Server.

If you enjoyed this blog post, feel free to share it with your friends!

Change Data Capture, SQL

Disable cdc for all tables in SQL Server

Today, I had to remove cdc from all tables on a database to fix a particular issue. Here is the script to disable the cdc at table level.

Script

Select 'EXEC sys.sp_cdc_disable_table
 @source_schema = ''' + OBJECT_SCHEMA_NAME (source_object_id) +''',
 @source_name = ''' + object_name(source_object_id)+ ''',
 @capture_instance = '''+ capture_instance +''';',* From cdc.change_tables

Please note, the above script is not actually disabling the cdc on tables, but generates the statements which can be used to disable the CDC on tables. This is just to make sure we review before we apply the disable commands.

I would also suggest to execute the below to disable the Change Data Capture on the database as well.

use dbname
EXEC sys.sp_cdc_disable_db

If you enjoyed this blog post, feel free to share it with your friends!

SQL

CHECK constraint in SQL Server

CHECK constraint is to specify a predicate to a column or multiple columns that allows the data values that are acceptable. If the value is not satisfying the condition, the record violates the constraint and the operation will be ignored.

Let us quickly look at few of its usage with some examples as below.

create table testcheck
(
	ID int NOT NULL,
	Name varchar(50),
	Gender varchar(50) check (Gender in ('Male','Female'))
)

Insert into testcheck values(1,'Latheesh','Male')--Success row
Insert into testcheck values(1,'SQL Server','') --Failed row

Is it possible to drop a constraint?

Yes, we can very well drop a check constraint using alter table statement as below.

--Drop the constraint
ALTER TABLE testcheck DROP CONSTRAINT CK__testcheck__Gende__29572725;
--retry with no value for gender
Insert into testcheck values(1,'SQL Server','')

Does the constraint check the existing data?

Yes, when we create a constraint, it check the existing value by default.

--Add the constraint back
ALTER TABLE testcheck ADD CONSTRAINT CK__testcheck__Gende__29572725 CHECK (Gender in ('Male','Female') );

Is it possible to enable and disable a constraint?

Yes, very well.

Select * From testcheck
--Disable the constraint
ALTER TABLE testcheck NOCHECK CONSTRAINT CK__testcheck__Gende__29572725;
--Insert a value that does not satisfy
Insert into testcheck values(1,'SQL Server','')
Select * From testcheck

Now, let us enable the constraint back and see the interesting behavior.

--Enable the constraint back
ALTER TABLE testcheck CHECK CONSTRAINT CK__testcheck__Gende__29572725; --This would not check the existing data
Select * From testcheck
Insert into testcheck values(1,'dummy','')
Select * From testcheck

We can see that the CHECK CONSTRAINT operation is enabling the constraint and it violates the next insert statement as expected. However, if you notice the existing data, there is a violated data, which is not evaluated. Quite interesting, right? So, we need a different way to enable the constraint that also verifies the existing data. Here we go with the usage of WITH CHECK.

-- Enable the constraint properly
ALTER TABLE testcheck WITH CHECK CHECK CONSTRAINT CK__testcheck__Gende__29572725 --This would also check the existing data

The above shows that WITH CHECK actually evaluates the existing data as well. Now, let us clean up the data and re-enable successfully.

--Existing data clean up
Delete from testcheck where gender not in ('male','Female')
-- Enable the constraint properly
ALTER TABLE testcheck WITH CHECK CHECK CONSTRAINT CK__testcheck__Gende__29572725 --This time, this would succeed

When you disable a constraint using WITH NOCHECK, SQL Server is making a change to constraint to no longer trusts that constraint. It flags it as “not trusted” in metadata. Eventually, the only way to set it to trusted is by specifying the constraint WITH CHECK while enabling the constraint.

If you enjoyed this blog post, feel free to share it with your friends!

DDL, SQL

UNIQUE constraints in SQL Server

UNIQUE constraints are enforcing no duplicates can exists for a column or combination of columns for a table. We can define constraints in 3 ways:

  1. while creating a table
  2. using ALTER TABLE statement
  3. using SQL Server Management Studio

Let us quickly see with a simple example as below.

Drop table if exists TestTable
Create Table TestTable ( ID int UNIQUE, FirstName varchar(50) )

sp_help 'TestTable'

sp_help system procedure can be used to understand the table properties. If we observe the result of sp_help, we can see two important information.

  1. constraint name – unique constraint created.
  2. index name – though we have not created any index separately, there is one created as the same name of constraint.

So, when we create a UNIQUE constraint, SQL Server automatically creates a nonclustered index on the table. And this index is responsible for enforcing the uniqueness on the column(s).

Can we drop the index created?

No, we will never be able to drop those indexes until we drop the constraints.

Can we insert NULL values to a UNIQUE column?

Yes, we can have ONLY one NULL value to a UNIQUE column. If we try to insert again, it will fail for the same reason of duplicate violation.

Can we define UNIQUE for more than one column?

Yes, we can define on combination of columns. Interestingly, by defining on composite, we can have NULL values for each individual columns once and one for combination as well.

Drop table if exists TestTable
Create Table TestTable ( ID int , FirstName varchar(50) , UNIQUE(ID,FirstName))

--Data inserts
Print 'First insert'
Insert into TestTable Values(1,NULL)
Print 'Second insert'
Insert into TestTable Values(NULL,NULL)
Print 'Third insert'
Insert into TestTable Values(NULL,'SQL')
Print 'Fourth insert'
Insert into TestTable Values(100,'some name')

If you enjoyed this blog post, feel free to share it with your friends!