April 28, 2010 at 9:07 am
So I have a table that has many tables plugging data into it. We call it a configuration table. Instead of making a table per FK I have one table that has unenforced FKs going into it.
I have it set up so I can enforce by triggers or a constraint by a view.
Is there anything better than what I have set up in 05 or 08?
right now I do not have them constrained since the program "should" do it. I may eventually get the time and apply the trigger or constraint view. Is either one more dependable or better to practice? I would think the trigger would be faster since I would not have a union query. Instead I would have if blocks per owner type.
April 28, 2010 at 11:39 am
I'm sorry, I can't understand entirely what you're describing.
Why can't you use referential integrity?
BTW, you mention a view that enforces integrity. Views are simply select statements. They can't enforce integrity between tables in any way.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 28, 2010 at 11:51 am
I'm with Grant on this, why can't you use referential integrity?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 28, 2010 at 12:06 pm
It needs to be something like this:
CREATE TABLE dbo.TableC
(
TableCid uniqueidentifier NOT NULL,
BookName nvarchar(50) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.TableC ADD CONSTRAINT
PK_TableC PRIMARY KEY CLUSTERED
(
TableCid
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE TABLE dbo.TableB
(
TableBId uniqueidentifier NOT NULL,
RecipeName nvarchar(50) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.TableB ADD CONSTRAINT
PK_TableB PRIMARY KEY CLUSTERED
(
TableBId
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE TABLE dbo.TableA
(
Ownerid uniqueidentifier NOT NULL,
ownerType tinyint NOT NULL,
XXConfigureA xml NOT NULL,
XXConfigureB varchar(50) NOT NULL,
XXConfigureC decimal(18, 0) NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.TableA WITH NOCHECK ADD CONSTRAINT
FK_TableA_TableC FOREIGN KEY
(
Ownerid
) REFERENCES dbo.TableC
(
TableCid
) ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT FOR REPLICATION
GO
ALTER TABLE dbo.TableA
NOCHECK CONSTRAINT FK_TableA_TableC
GO
ALTER TABLE dbo.TableA WITH NOCHECK ADD CONSTRAINT
FK_TableA_TableB FOREIGN KEY
(
Ownerid
) REFERENCES dbo.TableB
(
TableBId
) ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT FOR REPLICATION
GO
ALTER TABLE dbo.TableA
NOCHECK CONSTRAINT FK_TableA_TableB
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply