-1

I have the following:

create table dbo.Users
(
  Id int identity not null 
    constraint PK_Users_Id primary key clustered (Id),  
  Email nvarchar (120) not null
    constraint UQ_Users_Email unique (Email),
  Username nvarchar (120) not null
    constraint UQ_Users_Username unique (Username)
);

Can I set a constraint to make Email and Username equal?

Thank You, Miguel

Miguel Moura
  • 36,732
  • 85
  • 259
  • 481
  • Wouldn't it be specifying your DB you have a useless column? – Francis P Nov 04 '13 at 18:31
  • just make a view that reads the column twice. makes as much sense. – Hogan Nov 04 '13 at 18:32
  • 1
    Guys...I just don't see any correct situation where a table would have to duplicate information in such way... – Francis P Nov 04 '13 at 18:32
  • I am using this because in some projects the email is used as username ... so to avoid changing everything in my application i just make both columns equal ... i just add a constraint. does this make sense? – Miguel Moura Nov 04 '13 at 18:36

2 Answers2

4

Below is the appropriate SQL to address the question directly:

create table dbo.Users
(
  Id int identity not null 
    constraint PK_Users_Id primary key clustered (Id),  
  Email nvarchar (120) not null
    constraint UQ_Users_Email unique (Email),
  Username nvarchar (120) not null
    constraint UQ_Users_Username unique (Username)      
);


alter table dbo.Users add constraint CK_EmailEqualsUsername
check (username = email)

A, most likely better, more common approach would be to perform the comparison somewhere higher in the call stack (different layer e.g.: Presentation Layer/Domain Layer/Service Layer)

Alex
  • 34,899
  • 5
  • 77
  • 90
  • 1
    This is a perfectly valid answer - the OP wants a solution that doesn't require reengineering the app. A `CHECK` constraint is a good solution that is transparent to the application code. – Bill Karwin Nov 04 '13 at 18:39
  • 1
    I do the checking on my service layer ... but just in case I have that optional check in the database. – Miguel Moura Nov 04 '13 at 19:31
1

Try this code:

alter table dbo.Users
add constraint ck_us check(Email=Username)
Amir Keshavarz
  • 3,050
  • 1
  • 19
  • 26