SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Table and row Problems
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hinduson
Yak Posting Veteran

Ghana
57 Posts

Posted - 09/22/2013 :  16:36:37  Show Profile  Reply with Quote
Please am being asked to answer the following questions and I need Help Running it in Sql Server 2005..

Table:Items.Itemdetails
*ItemID must be Auto generated
*ItemName should not be left Blank
*ItemDescription should not be left Blank
*QuantityInHand Should be greater than 0. The record should not be inserted or Modified Manually if Quantityinhand is 0.
*UnitPrice should be greater than 0
*ReorderQuantity should be greater than 0
*ReorderLevel should be greater that 0

Please Can someone help me with the syntax to execute this querries.

Best Regards.

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 09/22/2013 :  16:46:48  Show Profile  Reply with Quote
create table items.itemdetails
(
itemID int not null identity(1,1) primary key,
itemName varchar(50) not null,
itemDescription varchar(8000) not null,
quantityInHand int not null,
unitPrice money not null,
reorderQuantity int not null,
recorderLevel int not null,
check (len(ltrim(ItemName))=len(ItemName)
and len(ltrim(ItemDescription))=len(ItemDescription)
and QuantityInHand > 0
and UnitPrice > 0
and ReorderQuantity > 0
and ReorderLevel > 0
)
);
Go to Top of Page

Hinduson
Yak Posting Veteran

Ghana
57 Posts

Posted - 09/22/2013 :  16:52:36  Show Profile  Reply with Quote
Wow Sigmas. You are a genius. Let me try it out

Best Regards.
Go to Top of Page

Hinduson
Yak Posting Veteran

Ghana
57 Posts

Posted - 09/22/2013 :  16:55:59  Show Profile  Reply with Quote
Hello Sigmas,Its Giving me this Error message

Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ')'.


Best Regards.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/22/2013 :  18:04:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
CREATE TABLE	Items.ItemDetails
		(
			ItemID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
			ItemName NVARCHAR(50) NOT NULL,
			CONSTRAINT CK_ItemName CHECK (ItemName > ''),
			ItemDescription NVARCHAR(MAX) NOT NULL,
			CONSTRAINT CK_ItemDescription CHECK (ItemDescription > ''),
			QuantityInHand INT NOT NULL,
			CONSTRAINT CK_QuantityInHand CHECK (QuantityInHand > 0),
			UnitPrice MONEY NOT NULL,
			CONSTRAINT CK_UnitPrice CHECK (UnitPrice > 0),
			ReorderQuantity INT NOT NULL,
			CONSTRAINT CK_ReorderQuantity CHECK (ReorderQuantity > 0),
			ReorderLevel INT NOT NULL,
			CONSTRAINT CK_ReorderLevel CHECK (ReorderLevel > 0)
		);



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Hinduson
Yak Posting Veteran

Ghana
57 Posts

Posted - 09/23/2013 :  03:39:10  Show Profile  Reply with Quote
Thanks Swepso, it executed. But i will like to know if i have to insert values into itemname and itemDescription since it said it should not be left blank?

Best Regards.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/23/2013 :  13:09:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Hence the constraint the the name and the description must be greater than empty string.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000