Table and row Problems - 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)

Table and row Problems

Author

Topic

Hinduson
Yak Posting Veteran

69 Posts

Posted - 2013-09-22 : 16:36:37

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

172 Posts

Posted - 2013-09-22 : 16:46:48

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
)
);

Hinduson
Yak Posting Veteran

69 Posts

Posted - 2013-09-22 : 16:52:36

Wow Sigmas. You are a genius. Let me try it out

Best Regards.

Hinduson
Yak Posting Veteran

69 Posts

Posted - 2013-09-22 : 16:55:59

Hello Sigmas,Its Giving me this Error message

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

Best Regards.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-22 : 18:04:49

[code]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)
);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Hinduson
Yak Posting Veteran

69 Posts

Posted - 2013-09-23 : 03:39:10

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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-23 : 13:09:03

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources