Check Constraint Question - 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 2005 Forums

Transact-SQL (2005)

Check Constraint Question

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-19 : 17:31:16

Hi

I have created a table which contains date periods. It has 3 fields all with DateTime data types. These fields being ID, BeginDate, and EndDate. The ID field will hold a date indicating what month the reord is for, ie 01/03/2007 being March or 05/04/2007 being April. The BeginDate will contain the beginnig date for the financial month and EndDate for the financial month.

I am trying to apply a check constraint on the ID field so that combaination of a month and year can only be entered once, ie. if 01/03/2007 already exists in the table then 03/03/2007 cannot.

Is this possible?

Thanking you in advance!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-19 : 17:35:09

This is not possible with a check constraint. A check constraint is used for the row only and not the entire table.

You could use a trigger instead or just manually code the checks in your stored procedure or inline sql.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-19 : 18:13:59

Hi

Thanks for that!

Do you know hat the syntax would be?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-20 : 00:16:43

quote:
Originally posted by rcr69er

Hi

Thanks for that!

Do you know hat the syntax would be?

Thanks

CREATE TRIGGER YourTrigger ON YourTable
INSTEAD OF INSERT 
AS
BEGIN
IF NOT EXISTS (SELECT * FROM YourTable t
               INNER JOIN INSERTED i
               ON DATENAME(mm,i.ID) + DATENAME(yy,i.ID) = DATENAME(mm,t.ID) + DATENAME(yy,t.ID))
BEGIN
INSERT INTO YourTable
SELECT * FROM INSERTED
END
END
GO

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-20 : 03:32:54

Hi

Thanks for that, however it stills allows me to enter dates with the same month and year combination.

For example ID field contains a value of 01/01/2008, so I tried entering 02/01/2008 and it still excepts it instead of rejecting it.

Any ideas on what to do?

Thanks

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-05-20 : 04:59:22

I think you are after a unique constraint. An example to get you going:

create table test
(
x datetime not null,
y as cast(datepart(yyyy,x) as varchar) + cast(datepart(mm,x) as varchar) unique
);

insert into test values( getdate());
insert into test values( getdate());

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources