Check constraint - SQL problem

Hi,

I am new to database development and am writing a database as part of a
university course

I have created a table as below called CableWire - the table is created ok.

CREATE TABLE CableWire
(CableWireID CHAR(7),
BSstandard  CHAR(16),
Colour VARCHAR(16),
Material  VARCHAR(16),
MetresInStock INTEGER,
PRIMARY KEY (CableWireID));

However when I try to alter the table by adding a CHECK constraint:

ALTER TABLE CableWire
ADD CHECK (MetresInStock >= 0);

I get a pop-up box: "Line: 21
SQLSTATE = 37000
[Microsoft][ODBC dBase Driver] Syntax error in field definition, Con
tinue?"

(line 21 equated to the 2nd of those 2 lines). The syntax seems perfectly
acceptable to me.  Any help appreciated.

Regards,

Mary

Hello, Mary

The syntax is perfectly acceptable in Microsoft SQL Server 2000, but
are you using SQL Server or dBase ? The error message indicates that
the ODBC dBase driver is involved. If you want this to run on dBase,
perhaps you should try your question on another newsgroup.

Razvan

Some minor commetns about the design and some questions.

1) Why is every non-key column NULL-able?

2) I don't know the wirte business, so who defines the cablewire_id
codes?  I know the ISO stuff for machine screws, etc.

3) Likewise, what is the BS Standard.  My first guess was British
Standards, since you spelled color wrong :)

4) Don' t you use Pantone or Land color numbers?  Can you give me an
example of CHAR(16) color name?   I assume that it is a name, not a
code, but since you did not follow ISO-11179 rules, I don't know.

CREATE TABLE CableWire
(cablewire_id CHAR(7) NOT NULL PRIMARY KEY,
bs_standard  CHAR(16  NOT NULL),
colour_name VARCHAR(16)  NOT NULL,
material_type VARCHAR(16)  NOT NULL,
stock_level INTEGER DEFAULT 0  NOT NULL
CHECK (stock_level >= 0));

Otherwise, your syntax was fine.

Hi Mary,
I have just created table and added check constraint using alter
statement without any error on SQL SERVER 2000. Could you send more
detail about environment where you encountered this error.
Ash
http://www.astragalaxy.com