Creating Unique Indexes on Partition table..

In partition tables, SQL Server forces to include the Partition column as part of unique index/unique constraint or primary key. Is there a way to over-ride this option.

We have a table, which is partitioned on a Calculated Column (generated using custom logic). Also we need to enforce uniqueness based on few other columns. But SQL Server does not allow indexes/contraints on these columns without including the partition column.

Thanks for your help.

Well, you can create a non-aligned unique index on a partitioned table, but doing that will in essence not allow meta-data-only partition switching. Depending on your needs, that may be ok or not. You could also use a DML trigger to enforce uniqueness instead of a unique index. Or, if your environment allows for the use of them, you could always create an indexed view on the table and create a unique index on that view that will in essence enforce the uniqueness on the corresponding table as well.

For more information, see the following topic in SQL 2005 BOL:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5af648b1-8454-4c17-a47e-f9656572440b.htm

HTH,

Creating referential integrity constraints

Two ways to do this... Creating the constraints when creating the data model OR using SQL to use the 'reference' constraint. Does 2005 provide any other automated method of creating the Primary Key - to Foreign key constraints without writing the SQL to do this?

Thx

What do you mean by automated method? Using DDL is not automated enough? You could use SMO to create the constraints but you have to write VB.NET or C# code. You can also use the GUI but I wouldn't recommend that because it can mess up your tables depending on what you are doing. For example, it can drop and recreate tables depending on the changes you made in the diagram designer. And this may or may not be the right thing to do. Often using SQL DDL is the most direct and optimized way to make schema changes.

Another option is to use a modelling tool like Visio or ErWin or Power Designer. Those have capabilities to generate scripts to create the schema and/or make changes. Note that the features supported by the tools may differ from the database engine. For example, using included columns or indexing computed columns will be a problem. Similarly, the tools might allow defining cascading foreign key relationships even if there are cycles for example but if you try to apply those changes in SQL Server it will fail because the database engine lacks supports for those. On the other hand, if you are trying the same against Oracle it will succeed. So if you have complex schema relationships then you will have to use a hybrid approach. Or if you support different database engines you will have to tailor the scripts to the specific engine. Visio for example allows you to define additional scripts in the model that can be executed againt the database engine.

|||

Thank you for your answer, I didn't know that the GUI would do the things that you described. That was what I was referring to. I guess for me, the automated method would be that when you create a primary key, the create the foreign key in the 'create table' GUI, the foreign key constraint would be automatically created if the column names are the same. Does this make sense? Maybe coffee is the answer.

As you mentioned above, there are products that do provide this automation, I just wondered if the 2005 engine had included this feature.

Thx

|||

katgreen777 wrote:

the create the foreign key in the 'create table' GUI, the foreign key constraint would be automatically created if the column names are the same. Does this make sense?

It makes sense to some extent. But you can't just go by column names. You could have "Description" column in multiple tables which have no relation to each other except for the fact that they represent some sort of description of an entity. This rule might however work for key attributes.

I don't know if SSMS diagram designer or VS designer infers these things from the model. You have to ask in the Tools or Visual Studio forum. Data modelling tools however does this for you depending on how you create the model. For example, if you drag and drop a key column from one table to another it will establish a relationship that will translate to referential constraint on the database. Of course, none of this comes free. You typically pay a lot for designer/modelling tools.

|||

One note on this... isn't it a rule of thumb not to include computed columns in a table?

thx

|||I don't know of any such rule. There are cases where using computed column is useful. But it is a proprietary feature anyway so for data modelling purpose probably you shouldn't worry about it. If you hit some design issues in SQL Server due to limitations you can use computed columns & indexes on computed columns.|||

The books I've been reading say that you shouldn't include computed columns in a table because of the extra space they take up, but instead, create a view where the computations take place. That was what I was referring to.

Kat

|||Wow! Which books are those? You need to throw them in the garbage. Computed column as the name implies is computed at run-time. There are ways to persist computed column values in SQL Server 2005 which will take up space or if you index computed columns. Otherwise, it is no different than defining a view with the same expression. Even with view the notion that computations take place within a view is incorrect. The view definition is parsed into the statement (SELECT or DML), compiled, optimized and executed.|||

Yes, you aren't the first person to say that about my books, any good ones you would recommend?

Kat

Creating Primary key with ODBC

Hi all,

I've spent hours trying to find the error in the following SQL 2000
command:

ALTER TABLE [ClientList] ADD CONSTRAINT [PK_ClientList] PRIMARY KEY
CLUSTERED ([PhoneNo]) On [PRIMARY]

Every time I try to "Execute" this from my (VB5) ODBC connection I get:

Runtime error 3289;
Syntax error in CONSTRAINT clause

For the life of me I can see nothing wrong. I used Enterprise manager
to create this statement, and I can create the primary key fine from
there. The PhoneNo field does not allow NULLs.

Everything is service-packed up to date. I have tried using
[databasename].[dbo].[ClientList] and suchlike.

I've tried to find a relevant manual, but my SQL Server developers
guide suggests this should be ok and I can't see anything wrong in the
books online.

Can anybody please help?

TIARS200Phil (philsowden@.dataservicesltd.co.uk) writes:
> I've spent hours trying to find the error in the following SQL 2000
> command:
> ALTER TABLE [ClientList] ADD CONSTRAINT [PK_ClientList] PRIMARY KEY
> CLUSTERED ([PhoneNo]) On [PRIMARY]
> Every time I try to "Execute" this from my (VB5) ODBC connection I get:
> Runtime error 3289;
> Syntax error in CONSTRAINT clause
> For the life of me I can see nothing wrong. I used Enterprise manager
> to create this statement, and I can create the primary key fine from
> there. The PhoneNo field does not allow NULLs.

That appears to be an error from the client layer. There certainly is
no syntax error in that statement as far as SQL Server is concerned, as
I can see. (Run in Query Analyzer to verify.)

Could you post the actual VB code you are using? Please also include
the part where you connect.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the prompt reply Erland.

The database is opened as follows:
..
..
..
lsConnectionString = "ODBC;Description=SQL Server 2k;DRIVER=SQL
Server;SERVER=SERVER;UID=Administrator;" & _

"APP=??;WSID=PII333;DATABASE=Sparc;Network=DBNM PNTW;QueryLog_On=Yes;
Trusted_Connection=Yes"

Set gdbSparc = DBEngine.Workspaces(0).OpenDatabase("", dbDriverNoPrompt,
False, lsConnectionString)
..
..
..

(Sorry for line confusion, hope this makes sense!).

The function that would build the indexes contains the following code.

lsSQL = "ALTER TABLE " & lsTable & " " & _
"ADD CONSTRAINT PK_" & lsTable & " PRIMARY KEY CLUSTERED " & _
"([PhoneNo]) On [PRIMARY];"

gdbSparc.Execute lsSQL

This translates to the code I posted previously when the "lsTable"
parameter is provided (e.g. [ClientTable]).

I pasted the code by printing the value of <lsSQL> in the debug window
and copying it from there.

HTH

Phil

*** Sent via Developersdex http://www.developersdex.com ***|||RS200Phil (RS200Phil@.dataservicesltdnospam.co.uk) writes:
> The database is opened as follows:
> .
> .
> .
> lsConnectionString = "ODBC;Description=SQL Server 2k;DRIVER=SQL
> Server;SERVER=SERVER;UID=Administrator;" & _
> "APP=??;WSID=PII333;DATABASE=Sparc;Network=DBNM PNTW;QueryLog_On=Yes;
> Trusted_Connection=Yes"
> Set gdbSparc = DBEngine.Workspaces(0).OpenDatabase("", dbDriverNoPrompt,
> False, lsConnectionString)
> .
> .
> (Sorry for line confusion, hope this makes sense!).
> The function that would build the indexes contains the following code.
> lsSQL = "ALTER TABLE " & lsTable & " " & _
> "ADD CONSTRAINT PK_" & lsTable & " PRIMARY KEY CLUSTERED " & _
> "([PhoneNo]) On [PRIMARY];"
> gdbSparc.Execute lsSQL

Hm, doesn't look like modern technology to me. :-)

Have you checked which version of the ODBC SQL Server driver you have?
Which operating system is this (incl Service Pack)? Do you know which
version of the MDAC you have?

All I can really recommend is to try to get a newer version of the ODBC
driver, as this appeears to be culprit. (You could try to remove the
"On [PRIMARY]" part.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||RS200Phil wrote:
> gdbSparc.Execute lsSQL

Try:
gdbSparc.Execute lsSQL, dbSQLPassthrough

Otherwise Jet sticks its nose in.|||Thanks for that, Trevor.

The passthrough was originally in place, but it failed with error 3416.
I thought I was getting more info when I went through Jet.

However, you've hit the nail on the thumb! I found the following in the
Jet manual:

"Note The Microsoft Jet database engine doesn't support the use of
CONSTRAINT, or any of the data definition language (DDL) statements,
with non-Microsoft Jet databases. Use the DAO Create methods instead."

So it's back to the drawing board, then!

BTW - I am converting about 100 quite large tables from a legacy Access
97 back end to SQL 2k. DTS doesn't do a brilliant job of the
conversion, but it did create the initial vb5 code for me. I just had
to bodge it for the index creation (no good there, then!) and to change
some of the field types (rather than do it manually for about 1000
fields!).

Thanks for your help, I'm optimistic that I can resolve it now.

Cheers

Phil

*** Sent via Developersdex http://www.developersdex.com ***|||RS200Phil wrote:
> Thanks for that, Trevor.
> The passthrough was originally in place, but it failed with error 3416.
> I thought I was getting more info when I went through Jet.

You mean 3146? You can loop the errors collection of the dbengine
object, e.g.

dim e as Error
...
For each e in DbEngine.Errors
debug.print e.number, e.description
Next

> BTW - I am converting about 100 quite large tables from a legacy Access
> 97 back end to SQL 2k. DTS doesn't do a brilliant job of the
> conversion, but it did create the initial vb5 code for me. I just had
> to bodge it for the index creation (no good there, then!) and to change
> some of the field types (rather than do it manually for about 1000
> fields!).

Have you tried the upsizing wizard? http://support.microsoft.com/kb/q176614/|||Thanks so much for the ideas. Of course - I should've thought of the
dbengine error collection.

Also, I like the idea of the upsizing wizard. I've used it before, some
time ago, but opted for the SQL Server DTS tool. I'll give this a go,
too.

I appreciate all your help, Trevor. I'm sure I'll be ok now.

Cheers

Phil

*** Sent via Developersdex http://www.developersdex.com ***

Creating Foreign Key constraint

I have two tables; each has multi-column primary keys. I need to create a foreign key relationship between the two tables.

Sales:

DKey, int, PK

OKey,int, PK

RKey,int, PK

...

Rep:

Bkey, int, PK, identity

Rkey, int, PK

...

When I try to like Rkey from both tables as a foreign key relationship, I get an error that the columns in one table "do not match an existing primary key or UNIQUE constraint. (I've tried it both ways and get the same error.)

How can I link these two tables?

Thanks.

Make an unique index on Rkey from Rep or Sales depends on the table you can delegate "Primary Key" (use Database Diagram for usability)

|||

Since you did not include rationale for the key selection, this is going to be just a guess.

The purpose of a Primary Key is to uniquely identify a row of data. It looks like each Rep is uniquely identified with the IDENTITY field BKey. I'm not sure why you have RKey also involved in the Primary Key for Rep.

You could recreate the Primary key for Rep using only the Bkey, and then add the Bkey field to Sales to relate Sales to Rep.

OR, if RKey is unique, use it as a Primary Key.

However, if for some silly reason you HAVE to have a combination of Bkey and Rkey as the Primary key in Rep, you will have to add both columns to Sales in order to create the relationship.

As a side note, the use of Bkey, Rkey, etc. for column names is very 'odd', and not only adds confusion, but will be difficult to maintain the pattern for all tables. A 'best practice' naming convention for an IDENTITY field (such as Bkey) is to use the table name and the suffix [ID]. So a preferred name for Bkey would be RepID. The name instantly communicates where the field comes from and what it is, expecially useful when it is added to another table.

|||

I was trying to simplify the question....I probably should have included more details. I'm sorry, I never know how much detail to go into.

What I'm actually trying to do is create a many to many link between:

Sales:

DateKey, int, PK

OrganizationKey, int, PK

CustomerKey, int, PK

RepBridgeKey, int, PK

....

RepBridge:

RepBridgeKey, int, PK, identity

RepKey, int, PK

Rep:

RepKey, int, PK, identity

.....

I can link RepBridge and Rep via RepKey but not RepBridge with Sales via RepBridgeKey.

From your answer, it looks like I would have to include RepKey in my Sales table and do the FK link on both fields?

Thanks.

|||

No, I think you can only make an unique index on RepBridgeKey from RepBridge (is possible because is identity)

You can make the following links vis-a-vis your vision of table schema:

Rep <RepKey-->RepBridge

RepBridge <RepBridgeKey>Sales

|||

John,

In RepBridge, I'm still not sure why you need RepKey as part of the Primary Key. It seems like BridgeRepKey is a unique identifier and would adequately serve as the PK (and the FK in Sales).

RepBridgeKey 'should' be all you need.

However, if, as said earlier, there is a business reason to complicate the Primary key in BridgeRep by adding RepKey -then you will have to add RepKey to Sales -at which point, the RepBridge table seems unnecessary. You would have to duplicate the rows in Sales for each Rep if there are multiple Reps involved with a single Sale.

Your sales table probably needs a better defined primary key. Typically a unique sales identifier is created -something like an INVOICE number, SalesOrder number, etc. In your Sales table above, only one sale would be allowed per day per Rep. That may be reasonable, but is it realistic? I would prefer a [SalesID int IDENTITY Primary Key] field

Typically, a 'bridging' table will have an IDENTITY field as PK, and the PK's from BOTH others tables as FK.

RepBridge

RepBridgeKey, int IDENTITY PK

RepKey FK to Rep

SalesKey Fk to Sales

|||

I see what you're saying....the bridge table does not need the RepKey as part of its primary key. I was thinking that each new sales rep would have to trigger a new bridge record but this can be done with only one PK identity field.

There is a reason behind the key fields in the sales table, but perhaps I shall also revisit that side issue.

Thanks again.