How to add Duplicate & Null records on Unique Col

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to add Duplicate & Null records on Unique Col

Author

Topic

vijay1234
Starting Member

48 Posts

Posted - 2014-06-12 : 08:32:13

Hi All,

I have a table with set of columns with Unique constraint on one of the columns with Flag column.

'Name' is the column name where unique constraint is created. Deleted is one more Flag column with default 'o'

So i would like to know the solution where the Column name 'Name' will allow multiple NULL values and duplicate Records ( Names).

Kindly advice

Regards,
Vijay

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-06-12 : 08:37:43

https://www.google.de/?gws_rd=ssl#q=sql%20server%20remove%20unique%20constraint

Too old to Rock'n'Roll too young to die.

vijay1234
Starting Member

48 Posts

Posted - 2014-06-12 : 08:50:46

Hi Fred,

I Still want to have Unique Constraint on the column 'Name', but should allow NULL values & Duplicate entries.

As the Datatype is declared varchar(100) NULL.

I have altered to allow NULL values by creating one more computed column. It's working fine. But i need one more thing to allow duplicate records.

Please find my code below

USE chsdirectory

Create Table Emp
([Employee id] int not NULL constraint Emp_pk primary key clustered,
[First Name] varchar(100) NULL,
[Last Name] varchar(100) NULL,
[Nick Name] varchar(100) NULL,
[Social Security Number] int NULL)
go

Alter table Emp
Add constraint ssn_unique UNIQUE ([Social Security Number])

Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(1,'Robert','Bates','Bob',111213422)

Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(2,'Robert','Bates','Bob',121213422)

Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(3,'Robert','William','Rob',131213422)

Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(4,'Sonia','Keira','Sony',131413426)

Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(5,'Mellisa','Brown','Mel',NULL)

Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(6,'Sibey','Chikhs','Ciby',NULL)

Alter table Emp Add MySSN as case when [Social Security Number]
is NULL then [Employee id] else [Social Security Number] end
go

Alter table Emp Add constraint ssn_unique1 UNIQUE ([MySSN] )
go

Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(7,'Harsha','Sree','Bang',NULL)

Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(8,'Sunil','Kiran','Vij',NULL)

Insert into Emp ([Employee id],[First Name],[Last Name],
[Nick Name],[Social Security Number])
values(9,'SonKirania','Keira','Sony',131413426)

alter table Emp
drop constraint ssn_unique

alter table Emp
ADD Deleted bit NOT NULL default (0)

select * from Emp

Regards,
Vijay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources