check constraint? - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

check constraint?

Author

Topic

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2007-11-19 : 11:43:17

Not sure how to do this with a check constraint as I've only done simple checks before.
The table has 2 columns InvoiceNo, Path

Not allowed:
99999 PathStringA
99999 PathStringA
Allowed:
99999 PathStringA
99999 PathStringB
Allowed:
99999 PathStringA
99998 PathStringA

Any ideas on how to do this?
Thanks

georgev
Posting Yak Master

122 Posts

Posted - 2007-11-19 : 11:54:33

Declare a UNIQUE constraint on the columns...

George
<3Engaged!

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2007-11-19 : 12:29:59

No, because both columns will not be unique.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-19 : 17:15:11

According to your example, YES!

CREATE TABLE #NoDups 
(
	InvoiceNum INT NOT NULL,
	[Path] VARCHAR(50) NOT NULL
)
GO

CREATE UNIQUE NONCLUSTERED INDEX UK_NoDup ON #NoDups (InvoiceNum, [Path])
GO

INSERT #NoDups
SELECT 99999, 'PathStringA'
UNION ALL SELECT 99999, 'PathStringB'
UNION ALL SELECT 99998, 'PathStringA'

SELECT *
FROM #NoDups

-- Fails
INSERT #NoDups
SELECT 99999, 'PathStringA'

cognos79
Posting Yak Master

241 Posts

Posted - 2007-11-19 : 17:16:15

if you are using stored proc to insert data into this table then you can make sure no duplicates are inserted using query.

georgev
Posting Yak Master

122 Posts

Posted - 2007-11-20 : 07:01:48

quote:
Originally posted by qwertyjjj

No, because both columns will not be unique.

The combination of both columns will be unique though..?
Your example certainly suggests this!

George
<3Engaged!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources