October 14, 2011 at 9:20 am
Folks,
I have to model a concept in a SQL schema, whereby Population increase in an area can be projected in different ways. These "different ways" require completely seperate tables and approaches but need to be linked back to a scenario.
CREATE TABLE [dbo].[ScenarioProjectionProfile](
[ScenarioProjectionProfileId] [int] NOT NULL,
[ProjectionProfileTypeId] [int] NOT NULL,
[Fk_PopulationProjectionProfileId] [int] NULL,
[Fk_HousingProjectionProfiledId] [int] NULL,
CONSTRAINT [PK_ScenarioProjectionProfile] PRIMARY KEY CLUSTERED
(
In the table above [Fk_PopulationProjectionProfileId] and [Fk_HousingProjectionProfiledId] are both foreign keys and I need to prevent both of them from being populated. Only one should be set. The actual business rules precludes the keys switching at a later date but I want to constrain this at the database level. I dont want to use triggers here, is there a check constraint I can use to enforce that only one of these fields can be set?
Sorry for the confusion, but If I elaborate on the reasons for the bridging table it would take pages.
Cheers
October 14, 2011 at 9:43 am
Sure you can do that.
create table #ConstraintCheck
(
ID int not null identity,
FK1 int,
FK2 int,
MyVal varchar(25)
)
go
alter table #ConstraintCheck
Add constraint chk_ConstraintCheck CHECK ((FK1 is not null or FK2 is not null) and (FK2 is null or FK1 is null))
insert #ConstraintCheck select 1, null, 'MyVal'
insert #ConstraintCheck select null, 1, 'MyVal'
insert #ConstraintCheck select null, null, 'MyVal'
insert #ConstraintCheck select 1, 1, 'MyVal'
select * from #ConstraintCheck
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 14, 2011 at 12:51 pm
Thanks Sean,
To be honest, Im not happy about this bridging approach at all, but Im not sure what else to do. The foreign key linked tables have relationships that are disparate to each other, their suporting tables are not compatable with each other. The basically represent different types of data that will eventually through the application of algorithms ouput a contextually similar result. Are bridging tables a common approach in this type of situation?
October 14, 2011 at 12:58 pm
It is pretty hard from here to get a real understanding of what you are doing but it does seem a little odd ball. Of course what application doesn't require some sort of odd ball stuff somewhere long the way?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply