Alternate key - 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

General SQL Server Forums

New to SQL Server Programming

Alternate key

Author

Topic

tasha0228
Starting Member

5 Posts

Posted - 2011-06-18 : 12:15:40

Hi

I want to make a unique key between 2 dates.
This is what I have so far

CREATE TABLE Uitvoering
(
Voorstellingsnummer NUMERIC(4) NOT NULL ,
Uitvoeringsnummer NUMERIC(2) NOT NULL ,
BeginIime DATETIME NOT NULL ,
EndTime DATETIME NOT NULL ,
Zaal CHAR(11) NULL ,

CONSTRAINT PK_Uitvoering PRIMARY KEY (Voorstellingsnummer, Uitvoeringsnummer),
CONSTRAINT CHK_datum CHECK(EindDatumTijd>begindatumtijd),
CONSTRAINT AK_uitvoering unique (between BeginTime and Endtime)
)

The problem is I want to make the whole period between the begintime and the endtime unique, I tried using between, but I dont think its going to work, did i do smt wrong? or if any1 has another idea of how I could achieve this.

if I olny make the begintime and endtime unique, like if smt would begin at 10:00 than another performance could begin at 10:05. so I want to avoid that, and make the whole period between for example 10:00 and 12:00.

thanx in advance

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-18 : 12:29:38

You can't make a constraint against a time period in SQL Server (although some other RDBMS's can). You could however create a user-defined function that checks for overlapping date ranges and returns a value if it finds one, and that function can be used to enforce a check constraint. Keep in mind the performance overhead of such a function if you will be inserting or updating large numbers of rows.

tasha0228
Starting Member

5 Posts

Posted - 2011-06-18 : 12:34:59

How will I be able to create the userdefined function to check it?

quote:
Originally posted by robvolk

You can't make a constraint against a time period in SQL Server (although some other RDBMS's can). You could however create a user-defined function that checks for overlapping date ranges and returns a value if it finds one, and that function can be used to enforce a check constraint. Keep in mind the performance overhead of such a function if you will be inserting or updating large numbers of rows.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-18 : 14:30:17

Something like this:

CREATE TABLE Uitvoering
(
Voorstellingsnummer NUMERIC(4) NOT NULL ,
Uitvoeringsnummer NUMERIC(2) NOT NULL ,
BeginDatumTijd DATETIME NOT NULL ,
EindDatumTijd DATETIME NOT NULL ,
Zaal CHAR(11) NULL ,

CONSTRAINT PK_Uitvoering PRIMARY KEY (Voorstellingsnummer, Uitvoeringsnummer),
CONSTRAINT CHK_datum CHECK(EindDatumTijd>BeginDatumTijd),
CONSTRAINT AK_uitvoering unique (BeginDatumTijd,EindDatumTijd)
)
go
create function dbo.CheckSpan(@begindate datetime, @enddate datetime) returns bit AS
BEGIN
declare @found bit
set @found=case when exists(select * from Uitvoering
where (BeginDatumTijd<>@begindate or EindDatumTijd<>@enddate)
AND (@begindate between BeginDatumTijd and EindDatumTijd or
@enddate between BeginDatumTijd and EindDatumTijd)) then 1 else 0 end
return @found
END
go
alter table Uitvoering add constraint CHK_CheckSpan CHECK(dbo.CheckSpan(BeginDatumTijd,EindDatumTijd)=0)

insert Uitvoering values(1,1, '1900-01-01','1900-02-01',null)  -- succeeds
insert Uitvoering values(2,2, '1900-01-11','1900-01-21',null)  -- fails because of range violation
insert Uitvoering values(2,2, '1900-02-02','1900-03-01',null)  -- succeeds
insert Uitvoering values(3,3, '1900-02-11','1900-04-21',null)  -- fails because of range violation

tasha0228
Starting Member

5 Posts

Posted - 2011-06-19 : 08:26:21

omg.. it works :D, thank you so much :)
i really appreciate you're help

quote:

Originally posted by robvolk

Something like this:

CREATE TABLE Uitvoering
(
Voorstellingsnummer NUMERIC(4) NOT NULL ,
Uitvoeringsnummer NUMERIC(2) NOT NULL ,
BeginDatumTijd DATETIME NOT NULL ,
EindDatumTijd DATETIME NOT NULL ,
Zaal CHAR(11) NULL ,

CONSTRAINT PK_Uitvoering PRIMARY KEY (Voorstellingsnummer, Uitvoeringsnummer),
CONSTRAINT CHK_datum CHECK(EindDatumTijd>BeginDatumTijd),
CONSTRAINT AK_uitvoering unique (BeginDatumTijd,EindDatumTijd)
)
go
create function dbo.CheckSpan(@begindate datetime, @enddate datetime) returns bit AS
BEGIN
declare @found bit
set @found=case when exists(select * from Uitvoering
where (BeginDatumTijd<>@begindate or EindDatumTijd<>@enddate)
AND (@begindate between BeginDatumTijd and EindDatumTijd or
@enddate between BeginDatumTijd and EindDatumTijd)) then 1 else 0 end
return @found
END
go
alter table Uitvoering add constraint CHK_CheckSpan CHECK(dbo.CheckSpan(BeginDatumTijd,EindDatumTijd)=0)

insert Uitvoering values(1,1, '1900-01-01','1900-02-01',null)  -- succeeds
insert Uitvoering values(2,2, '1900-01-11','1900-01-21',null)  -- fails because of range violation
insert Uitvoering values(2,2, '1900-02-02','1900-03-01',null)  -- succeeds
insert Uitvoering values(3,3, '1900-02-11','1900-04-21',null)  -- fails because of range violation

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources