In a recent Twitter David Taylor (
Blog|
Twitter) asked for
#sqlhelp on constraining data in a varchar column that was holding 2 different types of data. One type of data is textual and like '4 PK WITH NO SN'. The other type of data is 11-digit numbers. The constraint he wanted to add was he wanted to ensure the numbers were unique. Duplicates if the values were textual are ok. Following is a possible quick and dirty solution that I developed in SQL Server 2008 Dev Edition. I don't know if it will work for David and I am sure there are other possibly better options but the following appears to work ok.
Mark Vaillancourt (
Blog|
Twitter) wrote a similar post several months ago
here.
Please share any thoughts or feedback you may have. Thanks!
IF (OBJECT_ID('dbo.vwMyTableNumericMyColumn', 'V') > 0)
DROP VIEW dbo.vwMyTableNumericMyColumn;
IF (OBJECT_ID('dbo.MyTable', 'U') > 0)
DROP TABLE dbo.MyTable;
CREATE TABLE dbo.MyTable
(
ID int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
MyColumn varchar(50) NOT NULL
);
GO
-- sample data
INSERT INTO dbo.MyTable (MyColumn) VALUES ('11111111111');
INSERT INTO dbo.MyTable (MyColumn) VALUES ('11111111112');
INSERT INTO dbo.MyTable (MyColumn) VALUES ('11111111113');
INSERT INTO dbo.MyTable (MyColumn) VALUES ('11111111114');
INSERT INTO dbo.MyTable (MyColumn) VALUES ('11111111115');
INSERT INTO dbo.MyTable (MyColumn) VALUES ('4 Pk With No SN');
INSERT INTO dbo.MyTable (MyColumn) VALUES ('4 Pk With No SN');
INSERT INTO dbo.MyTable (MyColumn) VALUES ('4 Pk With No SN');
INSERT INTO dbo.MyTable (MyColumn) VALUES ('4 Pk With No SN');
INSERT INTO dbo.MyTable (MyColumn) VALUES ('4 Pk With No SN');
GO
-- create schemabound view
CREATE VIEW dbo.vwMyTableNumericMyColumn
WITH SCHEMABINDING
AS
SELECT ID, MyColumn
FROM dbo.MyTable
WHERE (ISNUMERIC(MyColumn) = 1);
GO
-- create unique clustered index on schemabound view
CREATE UNIQUE CLUSTERED INDEX vwMyTableNumericMyColumn ON dbo.vwMyTableNumericMyColumn
(
MyColumn ASC
);
GO
-- confirm data
SELECT * FROM dbo.MyTable;
SELECT * FROM dbo.vwMyTableNumericMyColumn;
-- will cause error
INSERT INTO dbo.MyTable (MyColumn) VALUES ('11111111111');
-- will cause error
UPDATE dbo.MyTable SET MyColumn = '11111111111' WHERE MyColumn = '11111111112'
-- will not cause error
INSERT INTO dbo.MyTable (MyColumn) VALUES ('4 Pk With No SN');
-- will not cause error
UPDATE dbo.MyTable SET MyColumn = '22222222222' WHERE MyColumn = '11111111112'