Create a condition for MS SQL table

Question

i am designing a table for food industry application database. here is the code of a table in this database :

 create table materialPriceAndStandard (
   id int identity(200,1), 
   materialName nvarchar(100) not null default (0),
   unitPrice decimal(19,2) not null default (0),
   carbohydrate float not null default (0),
   protein float not null default (0),
   fat float not null default (0),
   humidity float not null default (0),
   minerals float not null default (0),
   totalMinerals float not null
   constraint PK_id_materialPriceAndStandard primary key (id)
 );

materialPriceAndStandard table consist a series of materials, total amount of each row (sum of minerlas include : carbohydrate .... to minerlas) should not exceed or less than 100, is it possible to force the table to throw an error if the sum of materials become more or less that 100 at the time of insert ?

Are you sure you want to use `float`s here? Because what if they add up to `99.9999967482` or `100.00000000000015`? — Damien_The_Unbeliever, Mar 12 '14 at 08:17
then what is the best data type for this situation? @Damien_The_Unbeliever — Iatrochemist, Mar 12 '14 at 08:21
I'd suggest using `decimal`, probably something like `decimal(7,4)` would be more than sufficient (up to 7 digits, with 4 of them occurring after the decimal point) — Damien_The_Unbeliever, Mar 12 '14 at 08:25

Adriaan Stander · Accepted Answer · 2014-03-12T08:11:25.243

I would recomend you take a look at using a CHECK Constraints

CHECK constraints enforce domain integrity by limiting the values that are accepted by one or more columns. You can create a CHECK constraint with any logical (Boolean) expression that returns TRUE or FALSE based on the logical operators. For example, the range of values for a salary column can be limited by creating a CHECK constraint that allows for only data that ranges from $15,000 through $100,000. This prevents salaries from being entered beyond the regular salary range. The logical expression would be the following: salary >= 15000 AND salary <= 100000.

So as an example

CREATE TABLE FOO(
  Col1 FLOAT,
  Col2 FLOAT,
  CONSTRAINT BAR CHECK (Col1 + Col2 = 100)
)
GO

INSERT INTO FOO VALUES (1,99)
GO

--this will fail
INSERT INTO FOO VALUES (1,100)
GO

DROP TABLE FOO
GO

`GO` is the batch terminator used in my SSMS. [GO statement](http://technet.microsoft.com/en-us/library/ms188037.aspx) — Adriaan Stander, Mar 12 '14 at 08:21

score 1 · Answer 2 · edited May 23 '17 at 11:49

1

You would need to create a Table level CHECK Constraint, please check the below link: SO

edited May 23 '17 at 11:49

Community

1
1

answered Mar 12 '14 at 08:10

Vasan

375
1
12

Create a condition for MS SQL table

2 Answers2