October 6, 2015 at 11:20 pm
Comments posted to this topic are about the item Composite foreign key
Igor Micev,My blog: www.igormicev.com
October 6, 2015 at 11:21 pm
This was removed by the editor as SPAM
October 6, 2015 at 11:21 pm
Basic question, but yet very good & useful, thanx Igor.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
October 7, 2015 at 1:16 am
good basic question. Thanks for sharing.
October 7, 2015 at 3:22 am
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)
October 7, 2015 at 4:19 am
Back to basics, nice timely reminder, thank you.
...
October 7, 2015 at 2:30 pm
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
October 7, 2015 at 2:56 pm
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,
October 7, 2015 at 4:02 pm
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
October 8, 2015 at 1:31 am
Nice one, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 8, 2015 at 8:10 am
Thanks for the question. Good to know about the data type compare.
October 11, 2015 at 4:53 am
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
November 4, 2015 at 4:41 am
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 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply