Need help to add Check Constraint into table

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)

Need help to add Check Constraint into table

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-01-27 : 06:50:40

My 1st table as follow,
CREATE TABLE DERPosi_201001
(
[TID] int identity(1,1) not null,
[SeatN] [numeric](15, 0) NOT NULL,
[Posi] [varchar](30) NOT NULL,
[recGrp] [datetime] not null
)

1. How to Add Check Constraint to DERPosi_201001?
2. This constraint will check recGrp.
3. recGrp only allow recGrp within Jan 2010 (01/2010)

My 2nd table as follow,
CREATE TABLE DERPosi_201002
(
[TID] int identity(1,1) not null,
[SeatN] [numeric](15, 0) NOT NULL,
[Posi] [varchar](30) NOT NULL,
[recGrp] [datetime] not null
)

1. How to Add Check Constraint to DERPosi_201002?
2. This constraint will check recGrp.
3. recGrp only allow recGrp within Feb 2010 (02/2010)

Please help me

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-27 : 08:44:09

[code]CREATE TABLE DERPosi_201001
(
[TID] int identity(1,1) not null,
[SeatN] [numeric](15, 0) NOT NULL,
[Posi] [varchar](30) NOT NULL,
[recGrp] [datetime] not null,
Constraint ck_recGrp_201001 CHECK (
recGrp BETWEEN '2010001' and '20100131'
)
)[/code]

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-27 : 08:45:53

Example from the BOL:

CREATE TABLE CheckTbl (col1 int, col2 int);
GO
CREATE FUNCTION CheckFnctn()
RETURNS int
AS
BEGIN
DECLARE @retval int
SELECT @retval = COUNT(*) FROM CheckTbl
RETURN @retval
END;
GO
ALTER TABLE CheckTbl
ADD CONSTRAINT chkRowCount CHECK (dbo.CheckFnctn() >= 1 );
GO

Should give you the groundworks.

http://msdn.microsoft.com/en-us/library/ms188258.aspx

EDIT: Better answer above

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-01-27 : 09:50:47

quote:

Originally posted by russell

CREATE TABLE DERPosi_201001
(
	[TID] int identity(1,1) not null,
	[SeatN] [numeric](15, 0) NOT NULL,
	[Posi] [varchar](30) NOT NULL,
	[recGrp] [datetime] not null,
	Constraint ck_recGrp_201001 CHECK (
		recGrp BETWEEN '2010001' and '20100131'
	)
)

Above statement no return error after table created. But, got an error when to insert recGrp values. my recGrp equal to mm/dd/yyyy

I changed your statement as follow,
CREATE TABLE DERPosi_201001
(
[TID] int identity(1,1) not null,
[SeatN] [numeric](15, 0) NOT NULL,
[Posi] [varchar](30) NOT NULL,
[recGrp] [datetime] not null,
Constraint ck_recGrp_201001 CHECK (
recGrp BETWEEN '1/1/2010' and '1/31/2010'
)
)

However, your statement is my inspiration

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-27 : 11:17:11

as long as recGrp is datetime you dont have to worried about format of date values. it will always be stored in format yyyy-mm-dd hh:mm:ss

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources