Check Constraint against 3 columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Check Constraint against 3 columns

Author

Topic

avalanche333
Starting Member

6 Posts

Posted - 2010-01-19 : 11:36:56

I have 3 columns and I only want 1 value ented in any of the three columns. Is this possible?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-19 : 12:09:45

seems like they should be one column in that case but here's the general way to do it (actual code will depend on your data types)...

create table t1 (a int, b int, c int, d int,
	check (isnull(b, 0) + isnull(c, 0) + isnull(d, 0) < 2)
)
GO
insert t1 values(1, null, null, 1) -- succeeds
insert t1 values(2, 1, null, null) -- succeeds
insert t1 values(2, 1, 1, null) -- fails

avalanche333
Starting Member

6 Posts

Posted - 2010-01-19 : 13:53:09

Thanks for your help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-19 : 14:00:52

A design like that is a recipe for "disaster". You can instead store the data into a new table with two columns. The first column would indicate the type of data, and the second column would be the actual value.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

avalanche333
Starting Member

6 Posts

Posted - 2010-01-19 : 14:32:46

quote:
Originally posted by russell

seems like they should be one column in that case but here's the general way to do it (actual code will depend on your data types)...
create table t1 (a int, b int, c int, d int,
check (isnull(b, 0) + isnull(c, 0) + isnull(d, 0) < 2)
)
GO
insert t1 values(1, null, null, 1) -- succeeds
insert t1 values(2, 1, null, null) -- succeeds
insert t1 values(2, 1, 1, null) -- fails

Actually what I'm trying to do is a little more complex then this.

The 3 keys are FK values, not a simple 1 or 0 flag.

So if I use what you suggested it will not work.

For instance I could insert

insert t1 values(1, 10, null, null) -- fails

I just want one of the 3 columns to have a value in it.

avalanche333
Starting Member

6 Posts

Posted - 2010-01-19 : 14:40:13

quote:
Originally posted by tkizer

A design like that is a recipe for "disaster". You can instead store the data into a new table with two columns. The first column would indicate the type of data, and the second column would be the actual value.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

What I am trying to do is have a Dependency type table.

For example:

tblOne
intOneID

tblTwo
intTwoID

tblThree
intThreeID

tblDependency
intID
intSourceOneID FK tblOne
intSourceTwoID FK tblTwo
intSourceThreeID FK tblThree
intDependencyOneID FK tblOne
intDependencyTwoID FK tblTwo
intDependencyThreeID FK tblThree

What I want to do is setup dependency. Any 1 source column can be dependent on any 1 dependencyID

Do data row can look like:
(100, 1, NULL, NULL, 2, NULL, NULL)
(101, NULL, NULL, 4, 2, NULL, NULL)
(102, NULL, 3, NULL, NULL, 3, NULL)

And so on....

Is there a better way to do this?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-19 : 15:18:18

show us the actual table DDL

avalanche333
Starting Member

6 Posts

Posted - 2010-01-19 : 15:36:04

Here it is, I've taken several columns out to keep things a little smaller, but the relevent ID's are there.

So with the first 3 tables I want to setup a dependency table that will allow any 1 value from the 3 tables (tblLUGroup, tblCtlTask, tblLUTaskGroup) to be dependent on any 1 other value from the same tables.

I try to accomplish this with tblLUTaskGroupDependency.
But that is either bad design and should be done a different way (which I can't figure out) or I need a way to check that only 1 of the three source columns are entered and only 1 of the 3 depends on columns are entered.

I hope this is clear?


CREATE TABLE [dbo].[tblLUGroup](
	[intLUGroupID] [int] IDENTITY(1,1) NOT NULL,
	[strGroupName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_tblLUGroup] PRIMARY KEY CLUSTERED 
(
	[intLUGroupID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[tblCtlTask](
	[intCtlTaskID] [int] IDENTITY(1,1) NOT NULL,
	[strTaskDescription] [varchar](255) NULL,
	[strTaskAction] [varchar](255) NOT NULL,
 CONSTRAINT [PK_tblCtlTask] PRIMARY KEY CLUSTERED 
(
	[intCtlTaskID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[tblLUTaskGroup](
	[intLUTaskGroupID] [int] IDENTITY(1,1) NOT NULL,
	[intCtlTaskID] [int] NOT NULL,
	[intLUGroupID] [int] NOT NULL,
	[intSequence] [int] NOT NULL,
 CONSTRAINT [PK_intLUTaskGroup] PRIMARY KEY CLUSTERED 
(
	[intLUTaskGroupID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblLUTaskGroup]  WITH CHECK ADD  CONSTRAINT [FK_tblLUTaskGroup_tblCtlTask] FOREIGN KEY([intCtlTaskID])
REFERENCES [dbo].[tblCtlTask] ([intCtlTaskID])
GO

ALTER TABLE [dbo].[tblLUTaskGroup] CHECK CONSTRAINT [FK_tblLUTaskGroup_tblCtlTask]
GO

ALTER TABLE [dbo].[tblLUTaskGroup]  WITH CHECK ADD  CONSTRAINT [FK_tblLUTaskGroup_tblLUGroup] FOREIGN KEY([intLUGroupID])
REFERENCES [dbo].[tblLUGroup] ([intLUGroupID])
GO

ALTER TABLE [dbo].[tblLUTaskGroup] CHECK CONSTRAINT [FK_tblLUTaskGroup_tblLUGroup]
GO

CREATE TABLE [dbo].[tblLUTaskGroupDependency](
	[intLUTaskGroupDependencyID] [int] IDENTITY(1,1) NOT NULL,
	[intCtlTaskID] [int] NULL,
	[intLUGroupID] [int] NULL,
	[intLUTaskGroupID] [int] NULL,
	[intDependsOnCtlTaskID] [int] NULL,
	[intDependsOnGroupID] [int] NULL,
	[intDependsOnTaskGroupID] [int] NULL,
	[blnRequiresSuccessfulRun] [bit] NOT NULL,
 CONSTRAINT [PK_tblLUTaskGroupDependency] PRIMARY KEY CLUSTERED 
(
	[intLUTaskGroupDependencyID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


ALTER TABLE [dbo].[tblLUTaskGroupDependency]  WITH CHECK ADD  CONSTRAINT [FK_tblLUTaskGroupDependency_tblCtlTask] FOREIGN KEY([intCtlTaskID])
REFERENCES [dbo].[tblCtlTask] ([intCtlTaskID])
GO

ALTER TABLE [dbo].[tblLUTaskGroupDependency] CHECK CONSTRAINT [FK_tblLUTaskGroupDependency_tblCtlTask]
GO

ALTER TABLE [dbo].[tblLUTaskGroupDependency]  WITH CHECK ADD  CONSTRAINT [FK_tblLUTaskGroupDependency_tblCtlTask1] FOREIGN KEY([intDependsOnCtlTaskID])
REFERENCES [dbo].[tblCtlTask] ([intCtlTaskID])
GO

ALTER TABLE [dbo].[tblLUTaskGroupDependency] CHECK CONSTRAINT [FK_tblLUTaskGroupDependency_tblCtlTask1]
GO

ALTER TABLE [dbo].[tblLUTaskGroupDependency]  WITH CHECK ADD  CONSTRAINT [FK_tblLUTaskGroupDependency_tblLUGroup] FOREIGN KEY([intLUGroupID])
REFERENCES [dbo].[tblLUGroup] ([intLUGroupID])
GO

ALTER TABLE [dbo].[tblLUTaskGroupDependency] CHECK CONSTRAINT [FK_tblLUTaskGroupDependency_tblLUGroup]
GO

ALTER TABLE [dbo].[tblLUTaskGroupDependency]  WITH CHECK ADD  CONSTRAINT [FK_tblLUTaskGroupDependency_tblLUGroup1] FOREIGN KEY([intDependsOnGroupID])
REFERENCES [dbo].[tblLUGroup] ([intLUGroupID])
GO

ALTER TABLE [dbo].[tblLUTaskGroupDependency] CHECK CONSTRAINT [FK_tblLUTaskGroupDependency_tblLUGroup1]
GO

ALTER TABLE [dbo].[tblLUTaskGroupDependency]  WITH CHECK ADD  CONSTRAINT [FK_tblLUTaskGroupDependency_tblLUTaskGroup] FOREIGN KEY([intLUTaskGroupID])
REFERENCES [dbo].[tblLUTaskGroup] ([intLUTaskGroupID])
GO

ALTER TABLE [dbo].[tblLUTaskGroupDependency] CHECK CONSTRAINT [FK_tblLUTaskGroupDependency_tblLUTaskGroup]
GO

ALTER TABLE [dbo].[tblLUTaskGroupDependency]  WITH CHECK ADD  CONSTRAINT [FK_tblLUTaskGroupDependency_tblLUTaskGroup1] FOREIGN KEY([intDependsOnTaskGroupID])
REFERENCES [dbo].[tblLUTaskGroup] ([intLUTaskGroupID])
GO

ALTER TABLE [dbo].[tblLUTaskGroupDependency] CHECK CONSTRAINT [FK_tblLUTaskGroupDependency_tblLUTaskGroup1]
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-19 : 16:25:34

Extend the original CHECK suggestion

create table	t1
		(
			a int,
			b int,
			c int,
			d int,
			check	(
					case when a is null then 0 else 1 end
					+ case when b is null then 0 else 1 end
					+ case when c is null then 0 else 1 end
					+ case when d is null then 0 else 1 end <= 1
				)
		)

N 56°04'39.26"
E 12°55'05.63"

avalanche333
Starting Member

6 Posts

Posted - 2010-01-20 : 07:22:10

Ok I'll give this a try.
In terms of design, is this the right approach?
There is something about the dependency table that does not sit right with me although it does accomplish what I need.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources