How to restrict Blank values in Primary Key column

Question

I have a database of sql server 2008 which I am using to display in a gridview. I have written some code to add new rows in asp.net and C# as Code behind.

When ever a row is added through the Programming I have put some checking which will not allow the required values to be Null.

But, here comes my problem when ever any one of the user adds a new row manually by opening the Database, then a blank value is allowing in the Primary key column which is not 'Null value'.

So, here I have to restrict even not to allow blank values in primary key column, how can I solve this problem.

When you say "blank value" do you mean `NULL` or an empty string or both? What is the type of the primary key column? — Daniel Renshaw, Apr 25 '11 at 11:33
Its a Varchar in the database, and its not allowing the null values as it is Primary key, but the empty string is allowing. — Sai Kalyan Kumar Akshinthala, Apr 25 '11 at 11:38
Why are you allowing your users to directly access the database? This is a very poor practice. — HLGEM, Apr 25 '11 at 14:13
I'm not fond fo varchar primary keys but if you have one, it will only allow 1 to be the empty string as primary keys are by nature unique. — HLGEM, Apr 25 '11 at 14:14

score 2 · Accepted Answer · answered Apr 25 '11 at 11:45

2

You need a check constraint

ALTER TABLE [TableName]
    ADD CONSTRAINT [CK_PrimaryKeyNotEmpty]
        CHECK
        (
            LEN([ColumnName]) > 0
        )

answered Apr 25 '11 at 11:45

Daniel Renshaw

33,729
8
75
94

Very very thanks, @Daniel Renshaw, its exactly working as what i think to work. – Sai Kalyan Kumar Akshinthala Apr 25 '11 at 11:55

score 0 · Answer 2 · edited May 23 '17 at 12:29

0

I'm having trouble figuring out what you actual question is.

1) If you need to make a column not accept null values:

Add a constraint to the column in the db:

ALTER TABLE [Table] ALTER COLUMN [Column] INTEGER NOT NULL

See: Altering a column: null to not null

2) If you need to restrict your PK column to only certain values add a constraint:

ALTER TABLE Table ADD CONSTRAINT CK_Table_Column_Range CHECK ( Column >= 0 )

edited May 23 '17 at 12:29

Community

1
1

answered Apr 25 '11 at 11:26

garnertb

9,454
36
38

Here, my problem is the Primary Key column is not accepting Null Values, but when ever we enter the cursor in the Column, and not giving any thing in that, but filling the remaining columns its accepting the row to be added even though nothing is given in the Primary key column. – Sai Kalyan Kumar Akshinthala Apr 25 '11 at 11:35

How to restrict Blank values in Primary Key column

2 Answers2