SQL Server – Insert Self Referencing Entry

database-designsql servert-sql

I have a Person table that has a created_by column that references the primary id of the table itself. So, it could be an employee that adds another employee to the database. It works fine.

But People can also add themselves (signup). So the value in the created_by column should be the auto-incremented value of the id column. But that value is obviously not available until after the insert.

So I could either (a) make the reference not to check the values, (b) add a default value in the beginning or (c) make the column nullable. All options seem bad to me.

The MySQL's dialect has this:

SET FOREIGN_KEY_CHECKS = 0;
INSERT INTO EMPLOYEE VALUES('12345','67890'),('67890','12345');
SET FOREIGN_KEY_CHECKS = 1;

…but I could not find something similar for SQL Server's T-SQL.

Best Answer

It looks like you are using an Identity for your Primary Key. If you need flexibility with your primary key I would recommend using a sequence. it would look something like this.

CREATE SEQUENCE SQ_temp AS INT INCREMENT BY 1 START WITH 1

CREATE TABLE Users 
( ID INT PRIMARY KEY DEFAULT NEXT VALUE FOR SQ_temp
, UserName VARCHAR(30)
, createdBy INT REFERENCES Users (ID)
)


INSERT INTO dbo.Users
(
    ID
    , UserName
    , createdBy
)
VALUES
(NEXT VALUE FOR SQ_temp,'User1', NEXT VALUE FOR SQ_temp)
, (NEXT VALUE FOR SQ_temp,'User2', NEXT VALUE FOR SQ_temp)
, (NEXT VALUE FOR SQ_temp,'User3', NEXT VALUE FOR SQ_temp)


SELECT *
FROM dbo.Users AS u

Related Solutions

Database Design – Structuring Excel-like Data

Here is one approach:

CREATE TABLE columns (
    id       INTEGER, 
    ordinal  INTEGER);

CREATE TABLE rows (
    id       INTEGER, 
    ordinal  INTEGER);

CREATE TABLE cells (
    rowid     INTEGER, 
    columnid  INTEGER, 
    value     TEXT);

This way, you will still need to +1/-1 the ordinals behind the new/deleted position, but fortunately you can do it all with a single statement, UPDATE rows SET ordinal = ordinal + 1 WHERE ordinal > 42. Although it updates many rows, the update statement should execute in less than a second.

Benefits:

Updating a rows table with N rows is lighter than updating a cells table with N*M rows.
Explicitly storing the ordinal means fast random access to an ordered subset of the data.

SQL Server – Clustered Index Scan for Self-Referencing FK Cascade Delete

It needs to validate that the row you are trying to delete is not a parent of an existing row.

You don't have an index on ParentTestId.

So it must do the scan.

CREATE NONCLUSTERED INDEX ix ON  [dbo].[Test](ParentTestId)

Then you see a seek.

BTW: The 20% estimated cost of the scan is likely to be an underestimate in this case.

The FK validation is under a left semi join and SQL Server costs it as though only a partial scan will be needed and it will find a matching row and the delete will fail.

Presumably the rows you are actually deleting will succeed more often than not and so a full scan will be required in order to validate that there are no conflicting rows.

Using trace flag 4138 to turn off row goals

DELETE FROM dbo.Test
WHERE  TestId = 200 
OPTION (querytraceon 4138 )

The re-costed plan shows the CI scan at 100% rather than 20% (as it now assumes a full scan will be needed)

This difference in estimated cost is sufficient for the missing index suggestion to show up.

The costs shown in this plan are still not very representative however. You might notice that they add up to 219%.

Also the overall plan cost of the queries with and without the trace flag are both identical at 0.0168268. The full CI scan ought, in fact, to be costed at 0.152373 (0.0485075 + 0.103866)

enter image description here

but it seems to be capped at no more than the original plan cost (and the overall plan cost doesn't get adjusted upwards either hence incorrect percentages)

Best Answer

Related Solutions

Database Design – Structuring Excel-like Data

SQL Server – Clustered Index Scan for Self-Referencing FK Cascade Delete

Related Question