Help on constraint

Question

Help on constraint

bayalapudi_k

Grasshopper

Points: 14
More actions
May 26, 2011 at 3:23 pm

#148109

Hi Guys,
I am new to the forum and first post of my sql. I am in my initial learning stage and need all you guys help.
I need a solution for this please...
Created a table and entered values for the columns with nulls and duplicates
create table CUSTOMERS
(
cust_id int,
customer varchar(20)
)
entered values as in the table....
100; ABC
100;SHD
NULL;CDB
My requirement is adding a Primary Key to the column cust_id.
of my learning I came to know the column have to be given a not null constraint before PK is given....
But when I write the code as
ALTER TABLE CUSTOMERS
ALTER COLUMN cust_id int not null
GO
It is throwing an error, .....
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'cust_id', table 'UNIVERSITY1.dbo.CUSTOMERS'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
Please anyone help me with the issue.....Thanks

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004591 More actions · Answer 1

bayalapudi_k (5/26/2011)
Hi Guys,
I am new to the forum and first post of my sql. I am in my initial learning stage and need all you guys help.
I need a solution for this please...
Created a table and entered values for the columns with nulls and duplicates
create table CUSTOMERS
(
cust_id int,
customer varchar(20)
)
entered values as in the table....
100; ABC
100;SHD
NULL;CDB
My requirement is adding a Primary Key to the column cust_id.
of my learning I came to know the column have to be given a not null constraint before PK is given....
But when I write the code as
ALTER TABLE CUSTOMERS
ALTER COLUMN cust_id int not null
GO
It is throwing an error, .....
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'cust_id', table 'UNIVERSITY1.dbo.CUSTOMERS'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
Please anyone help me with the issue.....Thanks

Everything up to this point, including the error is pretty obvious. You're trying to make a unique key on non-unique data. So, the question would be, which 100 row do you want to get rid of?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bayalapudi_k Grasshopper Points: 14 More actions · Answer 2

bayalapudi_k

Grasshopper

Points: 14

May 26, 2011 at 5:39 pm

#1330732

I want this as a non-unique data.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

The id has to be unique... the name of customer might repeat, but even then you need a way to differentiate the duplicates.

Help on constraint

Cookies on SQLServerCentral