CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008unique
Psyche Genie
Psyche Genie

Reputation: 673

SQL Server Create Table With Column Unique Not Null and Not Empty(Check)

How to create a table with a column which is unique, not null and not empty(Check)?

I tried below Query

CREATE TABLE Persons 
(
P_Id int NOT NULL UNIQUE,
LastName nvarchar(255) NOT NULL,
FirstName nvarchar(255),
Address nvarchar(255),
City nvarchar(255),
CHECK (P_Id>0)
)

When i try to create a table with both UNIQUE and CHECK constraint its throwing following error. Is it possible to use two constraint in a single query?

Major Error 0x80040E14, Minor Error 25501
> CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName nvarchar(255) NOT NULL,
FirstName nvarchar(255),
Address nvarchar(255),
City nvarchar(255),
CHECK (P_Id>0)
)
There was an error parsing the query. [ Token line number = 8,Token line offset = 1,Token in error = CHECK ]. I am using SQL Server 2008.

Upvotes: 2

Views: 4700

Answers (4)

Pred
Pred

Reputation: 9042

You can controll the uniqueness of a column or column set by the UNIQUE constraint.

The data stored in the column or column set could be checked/controlled (and forced with various rules) by the CHECK constraint.

The CHECK constraint to achieve your goal is the following:

ALTER TABLE [YourTable]
  ADD CONSTRAINT CK_CheckConstraintName
    CHECK (LEN([YourColumn]) >= {MinimumColumnWidth})

You can add the constraints in the CREATE TABLE statement or if the table already exists you can add it with the ALTER TABLE .. ADD CONSTRAINT statement.

Upvotes: 0

user4066167
user4066167

Reputation: 

    CREATE TABLE tab
    (
       id  INT, 
      notnullandnotemptystr VARCHAR(10) NOT NULL UNIQUE CHECK (DATALENGTH(notnullandnotemptystr) > 0)
    )

Upvotes: 4

Bhasyakarulu Kottakota
Bhasyakarulu Kottakota

Reputation: 833

It should be some thing like this. 

 CREATE TABLE [dbo].[TABLE1](
        [COL1] [nvarchar](50) NOT NULL UNIQUE
    )

    ALTER TABLE [dbo].[TABLE1] WITH CHECK 
    ADD CONSTRAINT [CK_TABLE1] CHECK  (([COL1]<>N''))

Upvotes: 2

Mehdi Haghshenas
Mehdi Haghshenas

Reputation: 2447

for this problem you can use Constraint in sql server

ALTER TABLE TBL  WITH CHECK ADD  CONSTRAINT [CK_TBL] CHECK  
(([dbo].[TBLCheckCustomeUnique](ID)=(1)))

TBLCheckCustomeUnique is a user define function that check this conditions

Upvotes: 0

Related Questions