Composite foreign key

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Comments posted to this topic are about the item Composite foreign key

    Igor Micev,
    My blog: www.igormicev.com

  • This was removed by the editor as SPAM

  • Hany Helmy

    SSChampion

    Points: 13436

    Basic question, but yet very good & useful, thanx Igor.

  • twin.devil

    SSC-Insane

    Points: 22208

    good basic question. Thanks for sharing.

  • samot-dwarf

    SSC Eights!

    Points: 984

    Regarding the truncation prevention: you can create a single table (empty) table with a handful columns (one for each data type used as PK) and create FK's from this table to multiple other tables

    ALTER TABLE dbo.TruncProt WITH CHECK ADD CONSTRAINT [FK_TruncProt_tbl1]

    FOREIGN KEY(id_bigint) REFERENCES dbo.tbl1 (id);

    ALTER TABLE dbo.TruncProt WITH CHECK ADD CONSTRAINT [FK_TruncProt_tbl2]

    FOREIGN KEY(id_bigint) REFERENCES dbo.tbl2 (id);

    ALTER TABLE dbo.TruncProt WITH CHECK ADD CONSTRAINT [FK_TruncProt_tbl3]

    FOREIGN KEY(id_int) REFERENCES dbo.tbl3 (id);

    ALTER TABLE dbo.TruncProt WITH CHECK ADD CONSTRAINT [FK_TruncProt_tbl4]

    FOREIGN KEY(id_unid) REFERENCES dbo.tbl4 (unid);

    Maybe you want to set up some check constraints to prevent that someone inserts a row in the TruncProt table (e.g. id_bigint > 1 and id_bigint < 2)

  • HappyGeek

    SSCoach

    Points: 18676

    Back to basics, nice timely reminder, thank you.

    ...

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    happygeek (10/7/2015)


    Back to basics, nice timely reminder, thank you.

    Yes, it's an easy question. It's a way how to prevent TRUNCATE on a table(s), especially in datawarehouses.

    Igor Micev,
    My blog: www.igormicev.com

  • Hugo Kornelis

    SSC Guru

    Points: 64675

    The question is okay (though I was hoping everyone would know that - not every table needs an IDENTITY primary key, you know?)

    I don't understand the discussion on preventing truncate table. Using a foreign key for this is a horrendous abuse. If you want to prevent truncate table, then reconsider who you are giving ALTER permissions on the tables.

    (Also, a foreign key with an empty dummy table as proposed in this thread disallows truncate, but not delete without where clause, so you are not really achieving much,


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Hugo Kornelis (10/7/2015)


    The question is okay (though I was hoping everyone would know that - not every table needs an IDENTITY primary key, you know?)

    I don't understand the discussion on preventing truncate table. Using a foreign key for this is a horrendous abuse. If you want to prevent truncate table, then reconsider who you are giving ALTER permissions on the tables.

    (Also, a foreign key with an empty dummy table as proposed in this thread disallows truncate, but not delete without where clause, so you are not really achieving much,

    Agree, the situation is that that i still cannot set alter permissions, but i'm planning it in future. Thanks.

    Igor Micev,
    My blog: www.igormicev.com

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Nice one, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the question. Good to know about the data type compare.

  • TomThomson

    SSC Guru

    Points: 104772

    Nice question.

    But preventing truncate by a mechanism that doesn't prevent delete (as suggested in the explanation) seems a bit crazy - permissions should be used to do this, and if you doesn't have the privilege required to deny permision you probably ought not to be mucking around like that to prevent everyone from truncating.

    Tom

  • patricklambin

    SSCrazy Eights

    Points: 9964

    Thanks for this nice question that I was able to answer thanks to a session I followed in 2014. I remembered that the speaker was not pleased about a question about the use to prevent delete/truncate and replied that it was not a good way to solve this aim...

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply