Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2005
    Posts
    115

    Unanswered: unallowed on 2 columns

    I am trying to create a table

    CREATE TABLE [dbo].[Members] (
    [id_Members] [int] IDENTITY (1, 1) NOT NULL ,
    [Login] [varchar] (12) NOT NULL ,
    [Password] [varchar] (12) NOT NULL ,
    [Status] [bit] NOT NULL ,
    ) ON [PRIMARY]


    I need id_Members AS Identity
    but I want to unallow Login+Pasword 2 times

    I mean if i have :
    Login = aaa
    Password = bbb


    I cannot find a second time:
    Login = aaa
    Password = bbb


    but of course :
    Login = aaa
    Password = ccc

    or
    Login = ccc
    Password = bbb

    are allowed


    what is missing in my code : CREATE TABLE [dbo].[Members] ?

    thank you

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
     CREATE  UNIQUE  INDEX [IX_Members] ON [dbo].[Members]([Login], [Password]) ON [PRIMARY]
    Creates a unique index (not simply a constraint) since you are probably going to be including these fields in where criteria quite a bit. You probably want to index your identity column too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2005
    Posts
    115
    it seems perfect :-)

    thank you

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Maybe it's just me, but I'd lose the IDENTITY Column, and make the Login the Primary key.

    Why would you allow 1 login multiple passwords?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    To maintain a password history, probably. Hence the Status field.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Hence the status field?

    Ever get your password locked out or revoked?

    Hence the status field....oye....

    If you want history that's fine, have another table with history, make the PK the ID and a datetime column....and use a trigger to fire for updates and deletes

    hence the status column.....dude start early today....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I haven't had lunch yet, so I am not operating on all cylinders. But field/column...now you are just being picky. Sheesh...you get one IT manager fired and it all goes to your head.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jun 2005
    Posts
    115
    that was a simple exemple I need an unique integer as key for something else

    on another forum I get

    Run following after creating table, clustered is optional:

    ALTER TABLE [dbo].[Members] ADD
    CONSTRAINT [PK_Members] PRIMARY KEY CLUSTERED
    (
    [Login],
    [Password]
    ) ON [PRIMARY]


    which one is better ?

    thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •