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 Relational Databases(Primary/Foreign Key?)

Hey everyone,
I have just started getting into to SQL and am completely brand new to the whole concepts of relational databases. Someone on this forum pointed to the MSDN videos on LEARNVISUALSTUDIO.NET which have been very helpful. Unfortunately while learning about relational databases and looking at the program that I want to design and make using them, I have run into a pretty big wall, concerning the primary key and foreign key.
For my program, I am trying to save an object, and lets say the base class is SLIDE. Now SLIDE will store basically most of the information that I will ever want to store, such as timeCreated and mainText and slideID(primarykey). But there are other classes that derive from slide that will store just a bit more information than that because of what they are. Lets say there is a class derived from SLIDE called PERSON that stores its parentNode, which is to say something that this slide specifically belongs to and has a reference to. Now the tricky part is that in this program, every single slide can have a reference to another slide, that you would see displayed and that you could just click on and view if you wanted to.
Now relating what I just told about the classes in my program to a relation database standpoint is what confuses me. If I make a table SLIDE, it would hold incomplete data about the PERSON object, because that object has more data than SLIDE has. The answer to this was to make another table called PERSON, which would have more columns. But now we arrive at the big problem: The primary key called maybe SLIDEID would be different in this new PERSON table than in the other table called SLIDE (or any other derived class). Therefore the link between these tables is messed up. In my object orientated mind I am thinking of static class variables that would still stay constant for derived classes, so that when I make a PERSON slide it will increment off of the primary key of the SLIDE table. In other words, is there some sort of super TABLE that you can derive from, like an abstract class, where the primary keys of other tables can build off of because they will be used as the same type of reference to eachother.
If none of this made sense to the reader, I am greatly sorry. I do not really know what else I can say to convey to you the problem I have. Maybe its because I am so used to object orientated languages that this is making it so difficult to explain. If however you do understand what I am talking about, please think about it and help me find a solution to this problem. I am not an experienced programmer, but I do very much enjoy it and I am very excited about starting to make this program, and I have learned that before I start coding it is very important to have a very firm design in mind first.
Thank your for reading,
Jeremy

fwiw, I'll try to give you a nudge in the right direction.. =;o)

First off, you're completely right in that having a firm design (along with an understanding of the same) in mind before starting, is a very good thing.

Having said that, it's not certain that it'll be an easy or fast journey to get there.

You're currently thinking objectoriented, and trying to fit that into a relational design. Here's where the difficulties start.

Object orientation and Relational theory are quite different, to say the least...

Probably the best place to start, is to begin reading in Handbook of Relational Database Design

( http://www.amazon.com/Handbook-Relational-Database-Candace-Fleming/dp/0201114348 )

This is pretty much (still) the 'bible' on the subject.

It's not productspecific, so there's no 'specials' in there, but you'll find everything you want to know about how-to and why

and the reasonings and rules of what makes up a 'good' database design, and imo it's all pretty 'easy-reading' too, along with

examples and explanations about why different things may or may not be a good idea, and what sort of problems or benefits

different design choices may give you.

Once you get a grip about how the relational world works, you can then see how to apply that to how you want your program to work.

Additionally, if you just google on 'relational database design', you'll get a bunch of links to different online tutorials

(at least it looks that way, haven't looked at all)

Good luck, and welcome to 'our' world =;o)

/Kenneth

|||Dear Kenneth,
Thank you for the reply, I'll see if I can get the book so I can get a much better idea on what relational database design should all be about.
I have been thinking about my problem a little more lately and I think that I have come up with an idea. I can use my program in C# to keep track of the identities keys(maybe an int but leaning forward to a string, where like the first three letter distinguish what type of object it is, ie. PER-4539) through the use of a class static variable and then when I create a new entry into the table I input that key directly, instead of letting the database handle it. So basically I use C# to handle this problem instead of SQL. Is that a good idea, or should I still think of something else?
Jeremy
|||

Well, as a db guy, I shiver at the thought.

There are some 'stuff' out there that does keymanagement along those lines, though.. But only because you can doesn't always mean you should.. =;o)

Something about keys...

A key is the 'identificator' within a domain. A domain in this case, is typically a table. A table is also many times referred to as an 'entity'.

There are a few different names for different flavors of keys, such as Primary key, Alternate key, Foreign key, Super key.

The purpose of the Primary key is to single out the one unique row.

The PK may consist of a single column or more (composite key).

It's called 'Primary' just because it's the one chosen 'primarily' among the available alternate keys within the table.

In general (pertaining your question) it's not a good idea to have an 'intelligent' PK.

That is, the key shouldn't be any construction with some innate meaning or code. It will bring you more trouble than joy in the long run.

The Alternate Key could serve as a PK, but isn't declared as a PK, thus being 'alternate' by name.

The Super Key is all the columns in the table. It's always there automagically, and could be used as a PK right away,

but that wouldn't be very practical in most cases. (and there's also limitations on how indexes can be created)

A Foreign Key is just what the name implies - it's a key from 'somewhere else'.

'Else' meaning another table, or entity.

(sry for the lack of a creative example) =;o)

For example, you have an entity (table) 'Parent' that has some attributes. (columns)

Among the columns you decide which one is to be the PK.

The Parent table has a relation to another table 'Child'.

The relation is zero to many. That is, a parent may have zero, one or more children.

Child is then another entity (table) with some attributes (columns) of it's own.

Child has it's own PK, but how to know which child belongs to which parent?

In the Child table, you also place the PK from Parent, which then becomes a FK in Child.

As you may notice, we're heading into unknown waters pretty quick, and most of this stuff are related to other stuff that is related to other stuff.. etc etc

As soon as we start thinking about tables, columns, keys and relations, the question also arises about which columns should go into which table?

What is the difference if the PK is a single or composite key?

Here's where 'normalization' starts to show, which is another thing to consider.

I like this site http://www.datamodel.org/reference.php for it's simplicity.

It shows a great overview of the rules for normalization and also about cardinality - both are a 'must know' for you.

If you go there and browse around a bit, I think that you'll get some more pieces into place.

You'll also find, that in this world there are very few (if any) absolutes or 'right answers'.

Practically everything is depending on scope and/or context - what is the good choice here, might be not good over there etc.

All in all - 'it depends'

/Kenneth

|||Once again Kenneth, thank you so very much. I will definintly be trying to get that book and will look through that website.
Jeremy

creating primary keys

Is there any way to speed up Primary key creation on a
table
I have a 60o million row table and its taking forever
I know that data in table ic clean in data in PK columns i
am ccreating is unique
alter table Profile_table add primary key (as_of_date,
PAN11_ACCOUNT_NUMBER, PRODUCT_CODE)In addition to Brian's replay try to avoid using EM in such cases.
Go to QA and there do a creation.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:e6q6sGbcDHA.1368@.TK2MSFTNGP11.phx.gbl...
> by default... a Primary Key is a clustered index. So the question is reall
> the same as 'how can I make a clustered index creation go faster'.
> You might find more info if you're searching on index creation...
> * what sort of hardware are you running on? SQL will take good advantage
of
> SMP, lots of memory, and fast disk for creating a large index like this...
> * there's no way to avoid the unique check if that's one thing you're
> wondering about. Doesn't matter if you KNOW that the data is unique... SQL
> will still check it...
> * take a look at the BOL entry for Create Index to make sure you
understand
> some of the basics around what happens if you have other indexes on the
> table already...
> also, I noticed that your key has a column in it that implies it's a
> datetime. Is that true? You should know that a PK has to be unique and
that
> the datetime datatype is only accurate to +-3MS. Therefore... SQL Server
> treats the datetime of '2003-01-01 00:00:00.000' as being identical to
> '2002-12-31 23:59:59.999'. This can create problems if you have a date as
> part of a PK...
>
> declare @.date1 datetime
> ,@.date2 datetime
> set @.date1 = '2003-01-01 00:00:00.000'
> set @.date2 = '2002-12-31 23:59:59.999'
> if @.date1 = @.date2 print 'the dates match'
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "sanjay" <sanjayg@.hotmail.com> wrote in message
> news:059e01c371aa$1054d580$a301280a@.phx.gbl...
> > Is there any way to speed up Primary key creation on a
> > table
> > I have a 60o million row table and its taking forever
> >
> > I know that data in table ic clean in data in PK columns i
> > am ccreating is unique
> >
> > alter table Profile_table add primary key (as_of_date,
> > PAN11_ACCOUNT_NUMBER, PRODUCT_CODE)
> >
> >
>

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 primary key on a view

i'm connecting a view to microsoft mappoint using a .UDL. when i link the
data i need to select the primary key so i choose the customer number, but
there are a few shops that have to shops but one customer number so neither
go in to the map because they are duplicates. so i was wondering if i could
create a primary key in the view that is sort of like the autonumber in
access. so each shop would be given a number when the view is called and tha
t
could be my primary key. the number wouldn't even have to match up with the
same number every time i would just need it to get every shop into mappoint.
if anyone has any suggestions i would really appreciate it. thank you.
jb
Message posted via http://www.webservertalk.comjbennett via webservertalk.com wrote:
> i'm connecting a view to microsoft mappoint using a .UDL. when i link the
> data i need to select the primary key so i choose the customer number, but
> there are a few shops that have to shops but one customer number so neithe
r
> go in to the map because they are duplicates. so i was wondering if i coul
d
> create a primary key in the view that is sort of like the autonumber in
> access. so each shop would be given a number when the view is called and t
hat
> could be my primary key. the number wouldn't even have to match up with th
e
> same number every time i would just need it to get every shop into mappoin
t.
> if anyone has any suggestions i would really appreciate it. thank you.
> jb
> --
> Message posted via http://www.webservertalk.com
Adding an "auto-number" (the similar feature is called IDENTITY in SQL
Server) doesn't seem to make much sense for the situation you
described. For future reference, it's a reliable rule of thumb that
doing anything "like Access" in SQL Server is often going to be a big
mistake ;-)
Instead, create a table called Shops. Shops obviously isn't the same as
Customers.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>
>Instead, create a table called Shops. Shops obviously isn't the same as
>Customers.
Thank you for your reply, i'll keep in mind not to try stuff in SQL that i
can do in access:) we already have a customer table. the problem is that we
ship ups and some of our shops have po boxes for address so we need a ship t
o
address which is stored in another table. so i created a view that pulls in
the customer info with the shipping address. after i did that i came across
a
problem that some shops had 2 stores under one customer number creating a
duplicate primary key, because in mappoint i had to use custno as the primar
y
key. we have shops added wly and inactivated wly so i would have to
update a table all the time, that is why i used a view. if there is a way to
create a table that automatically updates that would be great. thanks again
for all your help.
jb
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1

Creating new File groups on an existing db

Out production database is around 10G and it is all in one file group
(Primary).
I would like to create new file groups and split the database into multiple
file groups for better manageablity.
I have identified the tables that can be moved into new file groups.
What is the best approach to spilt an existing production database into
multiple file groups?
Thanks,
S.KumarSuresh
If your table whose are going to be place on separate filegroup have
clustered indexes then you can easily re-create the clustered index with
specify file group .For details please refer to BOL.
"Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
news:#A3i3KWbDHA.2548@.TK2MSFTNGP09.phx.gbl...
> Out production database is around 10G and it is all in one file group
> (Primary).
> I would like to create new file groups and split the database into
multiple
> file groups for better manageablity.
> I have identified the tables that can be moved into new file groups.
> What is the best approach to spilt an existing production database into
> multiple file groups?
> Thanks,
> S.Kumar
>|||You should start changing the filegroups for the specified tables, if there
aren´t many tables you could work with EM, edit table, property of the
tables/ indexes.
Jens Süßmeyer.|||Multiple files in a single filegroup allows SQL to do parallel IO on a
single query...
Using multiple filegroups allow you to.
1. Backup/Restore subsets of tables with different recovery needs.
2. Balance IO if you beleive you can do better than striping
3. Limit the space allocation which is used by a subset of tables...
The 1st is the best reason for filegroups...It does not improve
manageability, but causes you to have to watch for disk space utilization on
EACH filegroup...
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corp (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
news:OxBFFmWbDHA.3360@.tk2msftngp13.phx.gbl...
> My aim is to split the database into multiple files.
> I could have multiple files referring to the same File Group (Primary) or
> multiple files going to multiple File groups.
> Which way is better and what are the pros and cons?
> Thanks,
> S.Kumar
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eki5qUWbDHA.1580@.tk2msftngp13.phx.gbl...
> > Suresh
> > If your table whose are going to be place on separate filegroup have
> > clustered indexes then you can easily re-create the clustered index with
> > specify file group .For details please refer to BOL.
> >
> >
> >
> > "Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
> > news:#A3i3KWbDHA.2548@.TK2MSFTNGP09.phx.gbl...
> > > Out production database is around 10G and it is all in one file group
> > > (Primary).
> > > I would like to create new file groups and split the database into
> > multiple
> > > file groups for better manageablity.
> > >
> > > I have identified the tables that can be moved into new file groups.
> > > What is the best approach to spilt an existing production database
into
> > > multiple file groups?
> > >
> > > Thanks,
> > > S.Kumar
> > >
> > >
> >
> >
>|||Wayne,
Did you use to work for Unisys ?
I use to know one Wayne Snyder when I worked for them in Charlotte 10 yrs
ago.
If you are the one, please send me a note to my personal email address.
Thanks,
Suresh Kumar
"Wayne Snyder" <wsnyder@.ikon.com> wrote in message
news:OyKlrSXbDHA.2672@.tk2msftngp13.phx.gbl...
> Multiple files in a single filegroup allows SQL to do parallel IO on a
> single query...
> Using multiple filegroups allow you to.
> 1. Backup/Restore subsets of tables with different recovery needs.
> 2. Balance IO if you beleive you can do better than striping
> 3. Limit the space allocation which is used by a subset of tables...
> The 1st is the best reason for filegroups...It does not improve
> manageability, but causes you to have to watch for disk space utilization
on
> EACH filegroup...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Computer Education Services Corp (CESC), Charlotte, NC
> (Please respond only to the newsgroups.)
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
> news:OxBFFmWbDHA.3360@.tk2msftngp13.phx.gbl...
> > My aim is to split the database into multiple files.
> > I could have multiple files referring to the same File Group (Primary)
or
> > multiple files going to multiple File groups.
> >
> > Which way is better and what are the pros and cons?
> > Thanks,
> > S.Kumar
> >
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:eki5qUWbDHA.1580@.tk2msftngp13.phx.gbl...
> > > Suresh
> > > If your table whose are going to be place on separate filegroup have
> > > clustered indexes then you can easily re-create the clustered index
with
> > > specify file group .For details please refer to BOL.
> > >
> > >
> > >
> > > "Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
> > > news:#A3i3KWbDHA.2548@.TK2MSFTNGP09.phx.gbl...
> > > > Out production database is around 10G and it is all in one file
group
> > > > (Primary).
> > > > I would like to create new file groups and split the database into
> > > multiple
> > > > file groups for better manageablity.
> > > >
> > > > I have identified the tables that can be moved into new file groups.
> > > > What is the best approach to spilt an existing production database
> into
> > > > multiple file groups?
> > > >
> > > > Thanks,
> > > > S.Kumar
> > > >
> > > >
> > >
> > >
> >
> >
>

Creating multiple primary keys

Hi!
I have this scenario:
One big table which will be populated with playlists; each playlist is
defined by:
1) tv channel
2) playlist date
Each playlist has many rows; each rows is defined by:
1) tv channel (as said before)
2) playlist date (as said before)
3) playlist onair hour
I must keep in this table these data for example for 2 months with 10
channels and 1000 rows for each playlist.
In my import procedure I have to do some calculations, so I prefer to load
data first in a temporary table, do my calculations and then make an insert
block of all 1000 rows in 1 step with a stored procedure from the temporary
table to the target table when all calculations and editing is ended: insert
into maintable execute('select * from temporarytable'). Then delete the
content of temporary table and import another playlist.
What kind of table structure would you use?
I think the best choice would be a multiple primary key for each row with
the fields 1,2 and 3.
Do you see some better solution? Is a multiple primary key very expensive in
terms of database performance?
I think using an autoincrement primary ID would not help because the insert
block would not work with it.
Any ideas?
Thanks!
Jem777> I think using an autoincrement primary ID would not help because the
> insert
> block would not work with it.
Not sure what you mean, because if you cannot insert data into the table, it
would be valueless for everyone. Either way, you need to have a valid
natural key of some sort defined or you will get logically duplicated data
(the rows may be different, other than the meaningless key)

> Do you see some better solution? Is a multiple primary key very expensive
> in
> terms of database performance?
Your first concern should be the validity of the data. If you only have one
table, and it is not related to any other tables, then the size of the key
will make very little (if any) difference

> I think the best choice would be a multiple primary key for each row with
> the fields 1,2 and 3.

> 1) tv channel (as said before)
> 2) playlist date (as said before)
> 3) playlist onair hour
This is probably a good key, because there is no way you will have two
programs on the same TV channel at the same time. I will go out on a limb
and say that this is not a great table structure, and is going to be wildly
denormalized, but I don't know what you are doing with it, so who knows if
it matters :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"jem777" <camillo@.rockit.it> wrote in message
news:eVBKSj5OFHA.1440@.TK2MSFTNGP10.phx.gbl...
> Hi!
> I have this scenario:
> One big table which will be populated with playlists; each playlist is
> defined by:
> 1) tv channel
> 2) playlist date
> Each playlist has many rows; each rows is defined by:
> 1) tv channel (as said before)
> 2) playlist date (as said before)
> 3) playlist onair hour
> I must keep in this table these data for example for 2 months with 10
> channels and 1000 rows for each playlist.
> In my import procedure I have to do some calculations, so I prefer to load
> data first in a temporary table, do my calculations and then make an
> insert
> block of all 1000 rows in 1 step with a stored procedure from the
> temporary
> table to the target table when all calculations and editing is ended:
> insert
> into maintable execute('select * from temporarytable'). Then delete the
> content of temporary table and import another playlist.
> What kind of table structure would you use?
> I think the best choice would be a multiple primary key for each row with
> the fields 1,2 and 3.
> Do you see some better solution? Is a multiple primary key very expensive
> in
> terms of database performance?
> I think using an autoincrement primary ID would not help because the
> insert
> block would not work with it.
> Any ideas?
> Thanks!
> Jem777
>|||You made me think about it and maybe I found a solution!
MAINTABLE:
3 id_palinsesto int 4 0 (primary key)
0 id_tmp int 4 0
0 data datetime 8 0
0 rete int 4 0
0 ora int 4 -1
TMPTABLE:
3 id_tmp int 4 0 (primary key)
0 data_palinsesto datetime 8 1
0 rete int 4 1
0 ora int 4 1
The copy query:
insert into MAINTABLE(id_tmp,data,rete,ora) execute('select
id_tmp,data,rete,ora from TMPTABLE)
The 2 primary keys are not linked each other!!!!
I did not know it was possible!!!
I dont have to use multiple primary keys!!!
COOL!
Thanks,
jem777
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uQY20s5OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Not sure what you mean, because if you cannot insert data into the table,
it
> would be valueless for everyone. Either way, you need to have a valid
> natural key of some sort defined or you will get logically duplicated data
> (the rows may be different, other than the meaningless key)
>
expensive
> Your first concern should be the validity of the data. If you only have
one
> table, and it is not related to any other tables, then the size of the key
> will make very little (if any) difference
>
with
>
> This is probably a good key, because there is no way you will have two
> programs on the same TV channel at the same time. I will go out on a limb
> and say that this is not a great table structure, and is going to be
wildly
> denormalized, but I don't know what you are doing with it, so who knows if
> it matters :)
>
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "jem777" <camillo@.rockit.it> wrote in message
> news:eVBKSj5OFHA.1440@.TK2MSFTNGP10.phx.gbl...
load
with
expensive
>|||>> I think the best choice would be a multiple primary key for each row
with
the fields [sic] 1,2 and 3. <<
In SQL Server, the temporal data type has both the date and time.
Generally speaking, you want to show durations of time, not points. You
also never told us what is playing -- TV shows? Here is my guess.
CREATE TABLE PlayLists
(tv_channel CHAR (5) NOT NULL,
time_slot_start DATETIME NOT NULL,
time_slot_end DATETIME NOT NULL,
CHECK (time_slot_start < time_slot_end),
show_name CHAR(15) NOT NULL,
PRIMARY KEY (tv_channel, time_slot_start ));
You can now use BETWEEN predicates with the time slot durations for
your queries.
performance? <<
No. Size, grandularity and the order of the columns in the declaration
are more important.
insert block would not work with it. <<
You do know that an autoincrement extension is not relational,
proprietary and dangerous to data integrity, don't you? By definition
it can never be a key. You already have a natural thanks to the
physics of the situation, so quit trying to mimic a magnetic tape file.|||>>> I think using an autoincrement primary ID would not help
> insert block would not work with it. <<
> You do know that an autoincrement extension is not
> relational,
> proprietary and dangerous to data integrity, don't you?
> By definition
> it can never be a key. You already have a natural thanks
> to the
> physics of the situation, so quit trying to mimic a
> magnetic tape file.
Run for your lives! Data integrity will be lost using
identity columns! Check.
Hate to tell you Joe, but most younger people don't even
know anyone that has even heard of someone using mag tapes.
;->
Suppose, the tv_channel description for a given entity
changes or is entered wrong? Same entity, different
description. That means that all tables that join to
Playlists will also have to be changed along with all
records in the Playlists table. Suppose on entry, you don't
know the tv_channel and do know the timeslot or the other
way around or don't know either for sure? That means that
entity could not be entered into the system until that is
known or a bogus value must be entered. (Other logic can
determine whether a given Playlist item was valid (meaning
it needed a channel and timeslot)).
Using TV_Channel/Timeslot only works if both values are
absolutely know at time of entry and have an extraordinarily
high probability that they will not change over the lifetime
use of the application.
Thomas|||> No. Size, grandularity and the order of the columns in the declaration
> are more important.
Just to clarify, because when I first read this statement it seemed a bit
ambiguous. You are speaking of the declaration of the primary key, not of
the table/columns.|||On Thu, 7 Apr 2005 20:34:40 +0200, jem777 wrote:
(snip)
>The copy query:
>insert into MAINTABLE(id_tmp,data,rete,ora) execute('select
>id_tmp,data,rete,ora from TMPTABLE)
Hi jem777,
Are you aware that you don't need to use dynamic SQL for this?
INSERT INTO MainTable (id_tmp,data,rete,ora)
SELECT id_tmp,data,rete,ora
FROM TmpTable
will work just as well!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Suppose, the tv_channel description for a given entity changes or is
entered wrong? Same entity, different
description. <<
We could use a reference to another table of televison stations which
is keyed on call letters, and let DRI actions handle this.
know at time of entry and have an extraordinarily
high probability that they will not change over the lifetime use of the
application. <<
I am pretty certain that time will keep going on forever and that I can
break it into timeslots. I can then handle changing the call letters
with DRI actions the few rare times that happens. My one problem is
not knowing the show that is going to run. Having consulted at BRTN in
Belgium, I know that there is no industry standard for identifying
television shows.
I might want to allow time_slot_end to be NULL, so i can have
open-ended time slots.|||
> I am pretty certain that time will keep going on forever and that I can
> break it into timeslots.
Is this really something you want to risk? Stranger things have happened,
you know. Heck, just a few centuries ago they changed our caledar around. An
d
then there's crazy always changings things like daylight savings time.
Besides, what happens if they'll want to run shows in such a small timeslot
that SQL Server can't even differentiate between STart and End?
No, I say make a [DateTime] table: ID IDENTITY INT, DATE DATETIME. That way,
if the definition of "April 07, 2005 09:45PM" ever changes, you'll be set.
-- Alex Papadimoulis
As if it wasn't obvious, it's MonthID 6 (I misspelled February wrong twice
in my calendar and had to delete those entries). I have three more ws of
this!|||<snip>I am pretty certain that time will keep going on forever ...</snip>
I don't know about that, Joe! I just got done with a book on G?del and
Enstein that says There's no such thing as Time...
"--CELKO--" wrote:

> entered wrong? Same entity, different
> description. <<
> We could use a reference to another table of televison stations which
> is keyed on call letters, and let DRI actions handle this.
>
> know at time of entry and have an extraordinarily
> high probability that they will not change over the lifetime use of the
> application. <<
> I am pretty certain that time will keep going on forever and that I can
> break it into timeslots. I can then handle changing the call letters
> with DRI actions the few rare times that happens. My one problem is
> not knowing the show that is going to run. Having consulted at BRTN in
> Belgium, I know that there is no industry standard for identifying
> television shows.
> I might want to allow time_slot_end to be NULL, so i can have
> open-ended time slots.
>

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.