Help with column constraints

[Normann]

I am working on designing a database for a project at work and am in the process of adding constraints to the various tables. I have run into a bit of trouble with one of them and hope someone here can help me.

The script for install is this:

CREATE TABLE BOOK_GENRE
	(	
	book_genre_id INT Identity PRIMARY KEY,
	book_genre_name NVARChar(70) NOT NULL
	)
GO
CREATE TABLE BOOK_TYPE
	(	
	book_type_id INT Identity PRIMARY KEY,
	book_type_name NVARChar(70) NOT NULL
	)
GO
CREATE TABLE BOOK
	(	
	book_id INT Identity PRIMARY KEY,
	book_title NVARChar(120) NOT NULL,
	book_author NVARChar(120)NOT NULL,
	book_type INT NOT NULL
		REFERENCES BOOK_TYPE(book_type_id),	
	book_genre INT NOT NULL
		REFERENCES BOOK_GENRE(book_genre_id),
	book_isbn NVARChar(13) NOT NULL,
	book_year NVARChar(4) NOT NULL,
	book_note NVARChar(120),
	book_added smalldatetime NOT NULL DEFAULT GETDATE(),
	CONSTRAINT ALLOWED_BOOK_ONLY_POSITIV_TYPE CHECK(book_type > 0), 
	CONSTRAINT ALLOWED_BOOK_ONLY_POSITIV_GENRE CHECK(book_genre > 0),
	CONSTRAINT ALLOWES_BOOK_ONLY_ISBN_NUMBERS CHECK(book_isbn ), 
	CONSTRAINT ALLOWED_BOOK_ONLY_ISBN_LENGTH CHECK(book_isbn )
	  
	)
GO

My problem lies in the last two constraints; in the first I need to check if all the entered numbers is actually a number and not a letter.
In the second I need to check if the length of the entered number is thirteen numbers long, now I know this could be made a bit easier if I used an int, but I need for it to be an nvarchar.

Hope you can help me or give me an idea how to check for it otherwise

NormanTheDane

[iburyak]

Try replacing last two with this:

CONSTRAINT ALLOWES_BOOK_ISBN CHECK(book_isbn like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Good Luck.

[Normann]

Brilliant, it works now. Thank you for your help.

NormannTheDane