November 5, 2015 at 9:27 am
Is it possible to create a constraint that specifies that if one column is null, the other column cannot be null and vice versa ?
November 5, 2015 at 9:29 am
Yes it is.
It'll be a CHECK constraint.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 5, 2015 at 10:32 am
Thanks, wasn't sure if the following syntax was supported or not....
CONSTRAINT C_KEYS CHECK (CTRL_ID IS NOT NULL OR CTRL_KEY IS NOT NULL),
November 6, 2015 at 4:26 pm
Now I have a new problem.
I want my table to have two "keys", one integer, one string so that a value can be accessed by either.
I have solved the problem of insuring one of these columns is not null,
but how do I insure that the key entered is unique ?
In other words, how can I specify a constraint that insures either column has unique values, yet nulls are allowed ?
November 6, 2015 at 5:03 pm
For that you either need a unique index on a computed column (A Coalesce of the two would work in this case), or a filtered index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 9, 2015 at 6:38 am
Thanks Gail -
I think that computed column with unique index is the best approach.
From what I've gathered, filtered indexes can only be created with filters on a single column.
November 9, 2015 at 6:45 am
You would create 2 contraints, one on each column to ensure that the not null values have a 1 to 1 relationship to each other.
2 unique constraints would be best, but since you need nullible values, as Gail has already suggested, unique index with filter would probably be the only way to go.
Don't make the mistake of creating a PK on (column1,column2), check that error by insert(1,1), then insert(1,2)
Example below.
create table Mytable (Column1 int, Column2 varchar(20) null,primary key clustered (Column1))
go
Create unique nonclustered index IX_Column2 on Mytable(Column2) where column2 is not null
November 9, 2015 at 7:13 am
Gail - I ended-up using a constraint instead of a unique index.
CREATE TABLE [dbo].[CONTROL_FACT](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CTRL_ID] [int] NULL, -- 1
[CTRL_KEY] [varchar](50) NULL, -- 'TARGET_LAST_UPDATE'
[CTRL_VALUE] [varchar](1000) NULL, -- '2015-11-03', 'abc@xxxxyyyy.com'
[CTRL_VERS] varchar(1) NULL, -- P=Prod Q=QA D=Dev, etc
[CTRL_DATATYPE] varchar(30) NULL, -- DECIMAL(9,6), int, smalldatetime, etc. Default = varchar
[ID_KEY_UNIQUE] AS COALESCE([CTRL_ID],[CTRL_KEY]), -- computed as we want a unique id set and unique key set
CONSTRAINT C_KEYS CHECK (CTRL_ID IS NOT NULL OR CTRL_KEY IS NOT NULL), -- one or the other key must be supplied
CONSTRAINT U_CTRLID_KEY UNIQUE(ID_KEY_UNIQUE),
CONSTRAINT [PK_CTRL] PRIMARY KEY CLUSTERED
(
ID
),
) ON [PRIMARY]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply