CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Neo
Neo

Reputation: 16239

How do I ensure about non negative column?

I need to create a table Branch with columns branch_name and branch_city character type, assets as integer type. branch_name is the primary key and I have to ensure that assets are non negative.

I tried like

CREATE TABLE Branch
(
    branch_name navarchar(100) primary key,
    branch_city nvarchar(100),
    assests int NOT NULL
)

Upvotes: 23

Views: 33950

Answers (3)

Krunal Mevada
Krunal Mevada

Reputation: 1655

Try this one

CREATE TABLE Branch (
    branch_name VARCHAR(100) PRIMARY KEY
    ,branch_city NVARCHAR(100)
    ,assests INT NOT NULL
    ,CONSTRAINT ck_assets_positive CHECK (assests >= 0)
    )

Upvotes: 0

Krunal Mevada
Krunal Mevada

Reputation: 1655

Alter your table creating a constraint to check the column

ALTER TABLE Branch ADD CONSTRAINT chkassets CHECK (assets > 0);

Upvotes: 9

marc_s
marc_s

Reputation: 755128

You need to define a datatype for your primary key, and you need to add a CHECK constraint to ensure assets is non-negative:

CREATE TABLE dbo.Branch
(
    branch_name NVARCHAR(100) primary key,
    branch_city nvarchar(100),
    assets int NOT NULL CHECK (assets >= 0)
)

Upvotes: 31

Related Questions