Introducing FOREIGN KEY constraint 'Constraint Name' on table 'Table Name' may cause cycles or multiple cascade paths

Whenever we define FOREIGN KEY with CASCADE options for UPDATE/DELETE in more than one column on a table the following error occurred.

Scenario:
1. I have one Parent table named "Table1"

IF OBJECT_ID('Table1','U') IS NULL
Create Table Table1
(
Id INT IDENTITY(1,1) PRIMARY KEY,
Column1 VARCHAR(10)
)
GO


2. I have one child table named "Table2"

IF OBJECT_ID('Table2','U') IS NULL
Create Table Table2
(
Id INT,
Column2 VARCHAR(10),
Id_2 INT
)
GO


3. Defining FOREIGN KEY with CASCADE option and reproducing the Error:
I just want to define FOREIGN KEY on Id and Id_2 columns with CASCADE option.

The following script will work fine

ALTER TABLE dbo.TABLE2
ADD CONSTRAINT Fk_Id FOREIGN KEY(Id)
REFERENCES dbo.Table1(Id) ON UPDATE CASCADE
GO


Command(s) completed successfully.

The following script fails with Error, This table already used the Key column "Table1(Id)" with CASCADE Option..
ALTER TABLE dbo.TABLE2
ADD CONSTRAINT Fk_Id_2 FOREIGN KEY(Id_2)
REFERENCES dbo.Table1(Id) ON UPDATE CASCADE
GO


Msg 1785, Level 16, State 0, Line 1
Introducing FOREIGN KEY constraint 'Fk_Id_2' on table 'Table2' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.


Reason:
- The Key column "Table1(Id)" have been referred on "Table2(Id) and Table2(Id_2)" columns, So it throwing an Error.
- In such scenario, we can go for Trigger

Remote table-valued function calls are not allowed.

Normally, when we access any objects(Table, View....) from Remote server using Four part naming convension through Linked Server as given below...

Select Column1, Column2 From Server2.Database1.Dbo.Table1(NOLOCK)

The following Error occurred...

Remote table-valued function calls are not allowed.

Solution:
Select Column1, Column2 From Server2.Database1.Dbo.Table1 WITH(NOLOCK) instead of NOLOCK

This error will not occur, When we access the Objects from different database or same database within the same instance or server. But, when we go to different Instance or server, NOLOCK should be used along with WITH keyword(i.e: WITH(NOLOCK)) instead of NOLOCK

Number of referencing columns in foreign key differs from number of referenced columns, table 'TableName'.

When creating/adding referential integrity constraints on a Table, The following error occurred in various scenarios as given below..

Number of referencing columns in foreign key differs from number of referenced columns, table 'TableName'.

Meaning:
Column name not specified for the foreign key constraints.

When creating table:
IF OBJECT_ID('Tb_Table2','U') IS NULL
CREATE TABLE Tb_Table2
(
Id INT,
Column2 VARCHAR(10),
CONSTRAINT FK_ID FOREIGN KEY REFERENCES Tb_Table1(Id)
)
GO


or

Adding foreign key constraint on existing table:
ALTER TABLE Tb_Table2 ADD CONSTRAINT FK_ID FOREIGN KEY REFERENCES Tb_Table1(Id)
GO


Column name not specified for Foreign key constraints in both scripts

The script should be as follows...

IF OBJECT_ID('Tb_Table2','U') IS NULL
CREATE TABLE Tb_Table2
(
Id INT,
Column2 VARCHAR(10),
CONSTRAINT FK_ID FOREIGN KEY(Id) REFERENCES Tb_Table1(Id)
)
GO


or

ALTER TABLE Tb_Table2 ADD CONSTRAINT FK_ID FOREIGN KEY(Id) REFERENCES Tb_Table1(Id)
GO


Here, Id is the Parent table Key column.

Table level constraint does not specify column list, table 'TableName'.

When creating Primary Key or Foreign Key like constraint, The following error occurred..

Table level constraint does not specify column list, table 'TableName'.

Meaning:
Column Name or Column List not specified for the constraints.

When creating Table:
IF OBJECT_ID('Tb_Table1','U') IS NULL
CREATE TABLE Tb_Table1
(
Id INT IDENTITY(1,1),
Column1 VARCHAR(10),
CONSTRAINT PK_Id PRIMARY KEY
)


or

When adding constraint on existing table:
ALTER TABLE Tb_Table1 ADD CONSTRAINT PK_Id PRIMARY KEY
GO


The error occurred, Here No column or Column list specified..

The script should be as follows...

IF OBJECT_ID('Tb_Table1','U') IS NULL
CREATE TABLE Tb_Table1
(
Id INT IDENTITY(1,1),
Column1 VARCHAR(10),
CONSTRAINT PK_Id PRIMARY KEY(Id)
)


or

ALTER TABLE Tb_Table1 ADD CONSTRAINT PK_Id PRIMARY KEY (Id)
GO

How to Restore MASTER database...

Why/when should restore the Master database:
- Incase of any failure login into an Instance due to forgot the login credentials
- Windows authentication credentials removed mistakenly and Sysadmin('sa') password also changed...
- we can fix this issue by restoring the Master database.

When should backup the Master database ?
1. If any changes done at Master database like whenever new login created/modified

2. New database created or Mapped the user with database.

What are the things needs to be considered before restore the Master database ?
1. If there is any changes to Master database after the database backup created.

2. Any login created/modified after the Master database backup created.

3. Any associated/mapped user with user databases after the Master database backup created.

4. Any user database created after the Master database backup created.

5. Any user database re-attached as it creates system tables to maintain the availability.

6. Any objects, logins, or databases have been deleted after Master database was backed up, those objects, logins, and databases should be deleted from Master database also.

7. Any user database no longer available that are referenced in a Master database backup, It report errors when restoring the Master database. Those databases should be dropped after the Master backup is restored.

8. The SQL Server instance stopped automatically once the Master database restore activity completed.

How to restore the Master database backup ?
Restoring Master database is not direct way like user defined database as it needs some additional workarround here..

1. Identify the Sqlservr.exe location.

2. Stop the SQL Server Instance.

3. Start the SQL Server in single user mode from command prompt...

If the SQL Server is Default instance:

If the SQL Server is Named instance:

Press Enter to proceed...

Once the system databases started as shown below... "Please don't close this screen"

4. Open the "SQL Server Management studio" from Start --> Program Files --> ...

5. Login into the Instance with Windows Authentication, The Instance doesn't have windows authentication credential, But we can login through Single user mode...

6. Expand the System Databases --> Right click on Master Database --> Tasks --> Restore --> Database... as shown below

7. Enter as Master in To Database area, Locate the Master database backup file with REPLACE Mode.

8. It may receive any error stating that "Single session is already running..." (or) "A transport-level error has occurred when receiving results from the server", Just click OK and Cancel to proceed further...

9. Now the command prompt can be closed once the restore process completed...

10. Start the SQL Server Instance service.

11. Open the "SQL Server Management studio" from Start --> Program Files --> ...

Why SPARSE column ?


Sparse Column:
Sparse column is like a normal column that has optimized storage for NULL.

Sparse column reduces the space requirement for NULL.

If the column value is NULL then, the values require NO STORAGE.

Using Normal Column:
IF OBJECT_ID('Tb_NormalColumn','U') IS NOT NULL
DROP TABLE Tb_NormalColumn
GO
CREATE TABLE Tb_NormalColumn
(
ID INT IDENTITY(1,1),
Column1 VARCHAR(100) NULL,
Column2 VARCHAR(100) NULL
)
GO

INSERT Tb_NormalColumn(Column2) VALUES('www.sqlserverbuddy.blogspot.com')
GO 1000


Here, The column1 uses NULL and It consumes some spaces in allocation.

sp_spaceused Tb_NormalColumn
GO



Using SPARSE Column:
IF OBJECT_ID('Tb_SparseColumn','U') IS NOT NULL
DROP TABLE Tb_SparseColumn
GO
CREATE TABLE Tb_SparseColumn
(
ID INT IDENTITY(1,1),
Column1 VARCHAR(100) SPARSE NULL,
Column2 VARCHAR(100) NULL
)
GO

INSERT Tb_SparseColumn(Column2) VALUES('www.sqlserverbuddy.blogspot.com')
GO 1000


Here, The column1 uses NULL and It consumes some spaces in allocation.

sp_spaceused Tb_SparseColumn
GO



To identity the SPARSE column:
SELECT name,is_sparse FROM sys.columns WHERE [object_id] = object_id('Tb_SparseColumn')
GO


Limitation of using SPARSE column:
1. SPARSE column should be Nullable.
2. It can not have RowGuidCol or Identity property and Filestream attribute.
3. It can not be Text, nText, Image, Timestamp, Geometry, Geography & User defined data type.
4. It can not have Default value, Computed columns.
5. It can not be a part of Clustered Index or Primary Key.
6. Normally, a row can have maximum of 8060 bytes. But, when using SPARSE column, row size will be 8018 bytes only.
7. When changing a normal column to a SPARSE column, the SPARSE column will consume more space for nonnull values than the normal columns.

Note: This feature NOT available prior to SQL Server 2008.

Conclusion:
Using SPARSE column, we can avoide/reduce memory allocation for NULL values

Rows are not deleted from the tables named in the FROM clause...

The question is "Rows are not deleted from the tables named in the FROM clause of the DELETE statement" ?

The answer is Yes and No

NO ?
Normally, when we use DELETE statement on Independent table, The records will be deleted from the tables named in the FROM clause.

DELETE FROM TB_Items WHERE CategoryID = 1

But, we can also use the script without FROM clause here...

YES ?
when we use DELETE statement on dependent table, The records has to be deleted from the table1 which dependent on table2.

Basically, when we perform a DELETE operation on a table, The records will be removed from the table named in "FROM CLAUSE as given below".


But, What the image shows below is "Different"


When we perform DELETE operation on a table which is based on some other table then, The record will be removed from the table named nearby the DELETE statement, Not from the "FROM CLAUSE".

Alternate to DBCC CLEANTABLE

Normally, we will execute this whenever we do some significant changes to variable-length columns in a table or indexed view.

But, we can also do some alternate to this activity.

Yes

we can Rebuild the indexes on tables and viewes.

Backup and restore operations are not allowed on database tempdb

1.Tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database.

2.Temporary tables and stored procedures are dropped automatically on disconnect.

So, Backup and restore operations are not allowed on tempdb

Failed to update database "DATABASE" because the database is read-only.

The reasone may be any one of the following

1. The database is Read-Only (or)
2. The database is a Snapshot

How to identity whether the database is Read-Only, Snapshot and Source database of the snapshot ?

a) Read-Only:
SELECT DATABASEPROPERTYEX('DatabaseName','Updateability') 'Read Only'
Read Only
READ_ONLY


SELECT Is_Read_Only 'Read Only' FROM SYS.databases WHERE name = 'DatabaseName'
Read Only
1

b) Snapshot source:
SELECT CASE WHEN DB_NAME(source_database_id) IS NULL THEN 'Not a Snapshot' ELSE 'Snapshot Created from ' + '"' + DB_NAME(source_database_id) + '"' END 'Source DB Name' FROM SYS.databases
WHERE name = 'DatabaseName'


If the Database is not a Snapshot
Source DB Name
Not a Snapshot


If the Database is a Snapshot
Source DB Name
Snapshot Created from "SourceDatabaseName"

Note: Snapshot can not be created for Log file:
Log files, offline files, restoring files, and defunct files for database snapshots should not be specified. "Database_log" is not an eligible file for a database snapshot.

The log or differential backup cannot be restored because no files are ready to rollforward.

All restore operations start with the restore of a FULL backup. It isn’t possible to restore only a Differential or a Log backup.

Both need a reference of the LSN(Log Sequence Number) to proceed.

When we try to restore Differential or Log backup directly without any Full backup, The following Err occurred... Because the differential or log backup will not have LSN to continue.

"The log or differential backup cannot be restored because no files are ready to rollforward."

To identify/verify whether the backup file (LSN) sequence is valid or not

SELECT Name,first_lsn,last_lsn,checkpoint_lsn,database_backup_lsn FROM msdb..backupset WHERE database_name = 'PandianDB' ORDER BY backup_start_date, media_set_id







1. Differential backup Database_Backup_LSN should match with Full Backup Checkpoint_LSN

2. Log backup Database_Backup_LSN should match with Differential Backup Checkpoint_LSN

3. Some times Differential backup and Log backup Checkpoint_LSN are the same. The meaning is "There is no any write operation performed after the Differential backup taken"

How to force Checkpoint process to occur every 2 minutes


Normally, The recovery interval is default to 0. It means SQL Server dynamically manages how offen a checkpoint occure.

We can force the SQL Server to occur the recovery interval every 2 minutes

sp_configure 'Show Advanced Options',1
GO

sp_configure 'Recovery Interval',2
Reconfigure with Override
GO

sp_configure 'Show Advanced Options',0
GO