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

Question

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.

Create table information [Create Table](http://msdn.microsoft.com/en-ca/library/ms174979.aspx) — Tak, Sep 22 '14 at 14:57
It is indeed possible to have all three constraints on the same line (NOT NULL, UNIQUE and CHECK(yourconstraint...) ), but the CHECK must be on the same line with the variable you're constraining, and the comma should be after the check expression. — , Sep 22 '14 at 16:21

score 3 · Accepted Answer · answered Sep 22 '14 at 14:57

3

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

answered Sep 22 '14 at 14:57

If you are using a version older than SQL Server 2008, try using LEN() instead of DATALENGTH() – Sep 22 '14 at 15:06

score 2 · Answer 2 · answered Sep 22 '14 at 14:56

2

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''))

answered Sep 22 '14 at 14:56

Bhasyakarulu Kottakota

823
1
6
10

score 0 · Answer 3 · answered Sep 22 '14 at 14:56

0

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

answered Sep 22 '14 at 14:56

Mehdi Haghshenas

2,433
1
16
35

score 0 · Answer 4 · answered Sep 22 '14 at 15:01

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.

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

4 Answers4