CODEWITHSUNDEEP
sql-server
Iatrochemist
Iatrochemist

Reputation: 276

Create a condition for MS SQL table

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 ?

Upvotes: 0

Views: 161

Answers (2)

Adriaan Stander
Adriaan Stander

Reputation: 166396

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

Upvotes: 1

Vasan
Vasan

Reputation: 375

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

Upvotes: 1

Related Questions