Post reply

unique constraint

  • August 9, 2012 at 10:23 am

    #279444

    I have a new table. The unique value is PersonID+lateDate, it can be used as primary key.

    But we would like to add a surrogate key as primary key to the table, and add above as a unique constraint.

    My questions are two:

    1. I know how to add a unique constraint using SQL, but from GUI how can I do it. It seems I can only add a unique index but not unique constraint.

    2. I would like to setup the surrogate key as primary key, but should it be clustered or non-clustered.

    Should the unique index PersonID+lateDate be clusted or non-clustered?

    Thanks

  • August 9, 2012 at 10:37 am

    #1523957

    sqlfriends (8/9/2012)

    Should the unique index PersonID+lateDate be clusted or non-clustered?

    Thanks

    Can't really answer this question properly unless we know more about the data and the application.

    Advantage of having the clustered index on the surrogate is potentially better organization of the actual data pages and minimal page splits.

    Advantage of having the clustered index on the PersonID might be vastly better performance for queries or transactions against this table if the SARGS involve this key. Random inserts may require periodic index rebuilds but the performance benefit may make it worthwhile.

    Also, you may be able to get the same results with a covered index if most the queries are selects.

    So, as you can see it really depends upon what sort of data will be in the table, how many rows and what sort of queries will be used and what sort of transactions. All of that needs to be taken into consideration before you can draw a conclusion as to which type of index would be best.

    The probability of survival is inversely proportional to the angle of arrival.

  • August 9, 2012 at 10:42 am

    #1523962

    Thanks, how about the question no 1?

  • August 9, 2012 at 10:59 am

    #1523972

    Maybe I am missing something, as this seems too obvious, but ... right-click the 'Constraints' node and select 'New Constraint'.

  • August 9, 2012 at 11:03 am

    #1523973

    I don't see any word about unique constraint from there

  • August 9, 2012 at 11:03 am

    #1523974

    look at alter table in BOL. The command will basically be:

    alter table add constraint <name_of_contraint> UNIQUE(column [, column,...]) [clustered]

    But you should double check my syntax with BOL

    The probability of survival is inversely proportional to the angle of arrival.

  • August 9, 2012 at 11:04 am

    #1523976

    I know how to add the constraint using SQL, but would like to know how to do it in SSMS GUI too.

  • August 9, 2012 at 11:05 am

    #1523977

    sqlfriends (8/9/2012)

    I know how to add the constraint using SQL, but would like to know how to do it in SSMS GUI too.

    actually there are some things you just can't do from the gui believe it or not. (not saying this is one of them)

    The probability of survival is inversely proportional to the angle of arrival.

  • August 9, 2012 at 11:19 am

    #1523982

    it's there.

    it's an option when you go to create or edit an index

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 9, 2012 at 11:24 am

    #1523986

    Thanks, so this is to create a unique index, but not a unique constraint.

    It seems not GUI way to create unique constraint.

  • August 9, 2012 at 11:29 am

    #1523991

    sqlfriends (8/9/2012)

    Thanks, so this is to create a unique index, but not a unique constraint.

    It seems not GUI way to create unique constraint.

    i think it's more of just a naming convention in the gui issue;

    what is the difference between a unique constraint and a unique index? i thought they were the same thing;

    i kinda see the point though, if i define a column as NOT NULL, that is a not null constraint, but i don't see it in the lists of constraints...

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 9, 2012 at 11:33 am

    #1523997

    Lowell (8/9/2012)

    what is the difference between a unique constraint and a unique index? i thought they were the same thing;

    The underlying functionality is the same, but the DDL is different. For example, scripting indexes is an option when scripting table objects.

    It might make a difference if you had port to another database engine, but for SQL server both mechanism are implemented as an index.

    The probability of survival is inversely proportional to the angle of arrival.

  • August 9, 2012 at 11:35 am

    #1523999

    I think if you create a unique constraint using sql, it creates both in gui constraint , also in index list.

    But in GUI, you can only create a unique index.

  • August 9, 2012 at 11:40 am

    #1524002

    there is a drop down menu in the screen shot...right now it says "Index"

    if you chose the other option in the list "Unique Key" it creates it as a constriant, and not just a unique index.

    i learned something on this one...but i'm not a GUI guy, mostly a scrpter.

    my crappy table example:

    --as a unique index:

    CREATE TABLE [dbo].[_WorkSpace](

    [IDISACTNBR] [varchar](4) NOT NULL

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    USE [SandBox]

    /****** Object: Index [IX__WorkSpace] Script Date: 08/09/2012 13:35:23 ******/

    CREATE UNIQUE NONCLUSTERED INDEX [IX__WorkSpace] ON [dbo].[_WorkSpace]

    (

    [IDISACTNBR] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    as a unique key:

    CREATE TABLE [dbo].[_WorkSpace](

    [IDISACTNBR] [varchar](4) NOT NULL,

    CONSTRAINT [IX__WorkSpace] UNIQUE NONCLUSTERED

    (

    [IDISACTNBR] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 9, 2012 at 12:28 pm

    #1524032

    This link expands on what Lowell is trying to explain. You add a Unique Key from the Table Design Gui.

    http://www.blackwasp.co.uk/SQLUnique.aspx

    Hope that helps.

    Erin

Viewing 15 posts - 1 through 15 (of 33 total)

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