I came across a database created by another person in the company that
frequently has 2 primary keys on its tables. I'm not sure why it was done
this way, my guess is to create uniqueness with the 2-field combination.
My question is: Is this poor design? Shouldn't we create constraints or
indexes if 2-field uniqueness is the purpose. Is one way more efficient
than the other? Any other thoughts as to why a table would be set up this
way? Thanks.
>> I came across a database created by another person in the company that[vbcol=seagreen]
Since 2 primary keys are impossible in a single SQL table, I guess you meant
it to be a single key with two columns.
[vbcol=seagreen]
The number of columns in a key by itself does not tell us whether a specific
design is good or bad. There is nothing wrong in having multi-column keys;
however multi-column references can occasionally mess up data integrity
since SQL products allow NULLs in referencing columns.
[vbcol=seagreen]
Not necessarily. Keys by themselves are constraints and guarantee uniqueness
any way.
[vbcol=seagreen]
That is mostly irrelevant since at the logical level, key selection should
not be based on efficiency which is determined at the implementation level.
Considering the physical implementation level, unless we consider all
possible query combinations, data access paths and optimization rules and
test out all of them on a specific system using on a specific dataset, we
cannot generalize and conclude single column keys are efficient than
multicolumn keys or vice-versa.
Anith
|||Sorry wasn't clear, you are correct, the tables have 2 columns combined to
make a concatenated primary key. In some instances, one column is a foreign
key of another table.
My next question: If a primary key consists of 2 columns, how would, or
could you, make that a foreign key in another table, and how would you query
such a thing?
Say for instance, I have a customer table, and an address table.
The address table fields:
CustomerID
AddressDescription
Address
Sample Data:
CustomerID AddressDescription Address
1 Main Office 123 Main
Street
1 Secondary Location 456 Main Street
So now I want to create another table that lists, for instance, people
located at each office. How would I set up the foreign key to point to the
address table. Wouldn't it be necessary to create another field in the
address table, maybe an identity field, and make that field the primary key?
And how would you query the database when joining tables? If you joined the
address table to a query, could you base it on both the CustomerID and
AddressDescription being equal to another field in another table. You can't
do this, can you? So I guess efficiency really isn't my question, maybe
more that it's a practicality or best practice issue: Under what
circumstances would a 2-column primary key be preferable to a nonmeaningful
primary key, with perhaps constraints on the 2 columns if the combined
uniqueness is the desired result?
I hope I'm making sense, thanks for your thoughts! And thanks for the
reminder about allowed nulls in the referenced columns, that could raise
serious issues.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OXm4hmstEHA.2184@.TK2MSFTNGP12.phx.gbl...
> Since 2 primary keys are impossible in a single SQL table, I guess you
meant
> it to be a single key with two columns.
>
> The number of columns in a key by itself does not tell us whether a
specific
> design is good or bad. There is nothing wrong in having multi-column keys;
> however multi-column references can occasionally mess up data integrity
> since SQL products allow NULLs in referencing columns.
>
> Not necessarily. Keys by themselves are constraints and guarantee
uniqueness
> any way.
>
> That is mostly irrelevant since at the logical level, key selection should
> not be based on efficiency which is determined at the implementation
level.
> Considering the physical implementation level, unless we consider all
> possible query combinations, data access paths and optimization rules and
> test out all of them on a specific system using on a specific dataset, we
> cannot generalize and conclude single column keys are efficient than
> multicolumn keys or vice-versa.
> --
> Anith
>
|||To avoid problems and other types of modifications to the data structures, I
would suggest switching to a surrogate key and making that the PK. You can
then create a UNIQUE index covering those two columns.
This surrogate key allows you more flexibility down the road. It's easier
to add tables, create the joins (less typing anyhow), modify the current PK
to something else etc.
HTH
Rick
"Rock" <rockisland@.yahoo.com> wrote in message
news:OLbfeCttEHA.2804@.TK2MSFTNGP14.phx.gbl...
> Sorry wasn't clear, you are correct, the tables have 2 columns combined to
> make a concatenated primary key. In some instances, one column is a
foreign
> key of another table.
> My next question: If a primary key consists of 2 columns, how would, or
> could you, make that a foreign key in another table, and how would you
query
> such a thing?
> Say for instance, I have a customer table, and an address table.
> The address table fields:
> CustomerID
> AddressDescription
> Address
> Sample Data:
> CustomerID AddressDescription Address
> 1 Main Office 123 Main
> Street
> 1 Secondary Location 456 Main Street
> So now I want to create another table that lists, for instance, people
> located at each office. How would I set up the foreign key to point to
the
> address table. Wouldn't it be necessary to create another field in the
> address table, maybe an identity field, and make that field the primary
key?
> And how would you query the database when joining tables? If you joined
the
> address table to a query, could you base it on both the CustomerID and
> AddressDescription being equal to another field in another table. You
can't
> do this, can you? So I guess efficiency really isn't my question, maybe
> more that it's a practicality or best practice issue: Under what
> circumstances would a 2-column primary key be preferable to a
nonmeaningful[vbcol=seagreen]
> primary key, with perhaps constraints on the 2 columns if the combined
> uniqueness is the desired result?
> I hope I'm making sense, thanks for your thoughts! And thanks for the
> reminder about allowed nulls in the referenced columns, that could raise
> serious issues.
>
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:OXm4hmstEHA.2184@.TK2MSFTNGP12.phx.gbl...
that[vbcol=seagreen]
> meant
> specific
keys;[vbcol=seagreen]
the[vbcol=seagreen]
> uniqueness
should[vbcol=seagreen]
> level.
and[vbcol=seagreen]
we
>
|||>> My next question: If a primary key consists of 2 columns, how would, or[vbcol=seagreen]
That would be simple. Just make sure your referencing table has two columns
with compatible datatypes and you can use the REFERENCES clause in the
CREATE TABLE or ALTER TABLE DDL like:
CREATE TABLR tbl (
c1 INT NOT NULL, c2 CHAR(30) NOT NULL, ...
( c1, c2 ) REFERENCES tbl2 ( c1, c2 )
PRIMARY KEY ( c1, c2 ) ) ;
For exact syntax, please refer to SQL Server Books Online.
[vbcol=seagreen]
Considering the example in your post, if the design is clean and both
columns are non-nullable they can be considered as valid choice for primary
key. ( Note that there are other desirable characteristics like stability,
minimality, simplicity and familiarity that come into play during key
selection as well )
[vbcol=seagreen]
As with many design considerations, the answer is it depends. If two columns
in the primary key can truly distinguish one entity from another, there is
no need to introduce a redundant identifier. And all the references to this
table must include the two columns and meaningful queries can be expressed
without loss of integrity.
However, multi-column keys can be often cumbersome, thereby could fail the
simplicity criteria. And NULLs in referencing columns is a serious issue in
SQL products which gives another logical reason to avoid multi-column keys
in some cases. That often gives a logical reason to use an additional
identifier which is unfamiliar, but stable, simple and minimal.
BTW, nothing prevents one from introducing artificial identifiers in all
your tables, but there are no proven advantages in doing so.
[vbcol=seagreen]
Yes, it does. But neither the SQL standards nor the vendors are interested
in addressing such issues since they are mostly busy implementing UFOs :-)
Anith
|||On Wed, 20 Oct 2004 12:14:14 -0600, Rock wrote:
>My next question: If a primary key consists of 2 columns, how would, or
>could you, make that a foreign key in another table, and how would you query
>such a thing?
>Say for instance, I have a customer table, and an address table.
>The address table fields:
>CustomerID
>AddressDescription
>Address
>Sample Data:
>CustomerID AddressDescription Address
>1 Main Office 123 Main
>Street
>1 Secondary Location 456 Main Street
>So now I want to create another table that lists, for instance, people
>located at each office. How would I set up the foreign key to point to the
>address table. Wouldn't it be necessary to create another field in the
>address table, maybe an identity field, and make that field the primary key?
Hi Rock,
That's not necessary. Your people table would look like this:
CREATE TABLE People (....,
....,
CustomerID int NOT NULL,
AddressDescription varchar(25) NOT NULL,
....,
FOREIGN KEY (CustomerID, AddressDescription)
REFERENCES Addresses,
....
)
>And how would you query the database when joining tables? If you joined the
>address table to a query, could you base it on both the CustomerID and
>AddressDescription being equal to another field in another table. You can't
>do this, can you?
Yes, you can:
SELECT some interesting columns
FROM People
INNER JOIN Addresses
ON Addresses.CustomerID = People.CustomerID
AND Addresses.AddressDescription = People.AddressDescription
WHERE ....
> So I guess efficiency really isn't my question, maybe
>more that it's a practicality or best practice issue: Under what
>circumstances would a 2-column primary key be preferable to a nonmeaningful
>primary key, with perhaps constraints on the 2 columns if the combined
>uniqueness is the desired result?
If the addresses table used CustomerID plus a short code for the
addresstype as it's primary key, I'd just use this natural key.
In your case, the more verbose AddressDescription is used as part of the
primary key. As you can see above, this complete description would have to
be repeated each time an address is referenced. This will use up more
space in your database. I would definitely consider using a shorter key.
This key *could* be the AddressType (moving the AddressDescription to
another table, listing all AddressTypes with their description), or I
could choose to introduce a surrogate key. This will take up more space in
the addresses table itself (as you add an extra column, plus an extra
index to check uniqueness on both the surrogate key and the combination of
CustomerID / AddressDescription), but it would reduce the amount of space
needed for references. It would increase speed on joins, but it might also
introduce the need to join in an extra table on some queries.
The bottom line is: there is no fixed answer, it all depends. At the end
of the day, you'll have to weigh all options against each other and choose
the one that fits your situation best.
>I hope I'm making sense, thanks for your thoughts! And thanks for the
>reminder about allowed nulls in the referenced columns, that could raise
>serious issues.
These can be easiliy avoided. Either set a NOT NULL constraint on the
columns, or (if you must allow NULLS) add a CHECK constraint to ensure
that either both columns are NULL, or both columns are not NULL.
CREATE TABLE People (....,
....,
CustomerID int, -- NULL allowed
AddressDescription varchar(25), -- NULL allowed
....,
FOREIGN KEY (CustomerID, AddressDescription)
REFERENCES Addresses,
CHECK ( ( CustomerID IS NULL
AND AddressDescription IS NULL)
OR ( CustomerID IS NOT NULL
AND AddressDescription IS NOT NULL))
....
)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo,
[vbcol=seagreen]
Not quite that easily. The problem is in regard to multi-column primary keys
and missing information. Relational literature has volumes about such
logical problems and the lack of theory behind handling missing information
being a serious issue.
We know SQL has only one way of dealing with missing information -- NULLs.
Since there is no mandatory requirement that all foreign key column values
should be non-nullable, SQL allows foreign key references on columns whose
values are known, unknown or partially known.
Now, consider a table with a multi-column primary key with columns, colA and
colB which is referenced by another table with corresponding multi-column
foreign key.
When the user inserts a referencing row with a value for colA and NULL for
colB, should the DBMS allow the insert? Since logically DBMS cannot preserve
the integrity of the relationship should it abort the insert? Or should it
consider the validity of the colA value alone and proceed with the insert?
What if there are duplicate values for colA in the referencing table which
cannot be evaluated due to NULLs in colB? If the colA value exists only once
should the DBMS substitute the only corresponding value for colB and proceed
with the insert ?
When deleting all the rows from the referenced table which contain a
specific value for colA, what should the DBMS do to the referencing table
that contains corresponding rows with a value for colA and a NULL for colB ?
While updating a few rows in the referenced table which contain a known colA
and colB, should the DBMS allow cascading actions on all corresponding rows
in the referencing table? Or should it evaluate the existence of rows with a
value for colA and exclude the rows with a null for colB ?
Anith
|||On Wed, 20 Oct 2004 17:21:36 -0500, Anith Sen wrote:
>Hugo,
>
>Not quite that easily. The problem is in regard to multi-column primary keys
>and missing information. Relational literature has volumes about such
>logical problems and the lack of theory behind handling missing information
>being a serious issue.
(snip)
>When the user inserts a referencing row with a value for colA and NULL for
>colB, should the DBMS allow the insert? Since logically DBMS cannot preserve
>the integrity of the relationship should it abort the insert? Or should it
>consider the validity of the colA value alone and proceed with the insert?
(snip more consequences of this proposed insert)
Hi Anith,
That is exactly why I suggested to either give all foreign key columns a
NOT NULL constraint, or (if NULL must be allowed) a CHECK to enforce that
they are either ALL null, or ALL not null. This will prevent all the
potential problems you describe.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Showing posts with label across. Show all posts
Showing posts with label across. Show all posts
Friday, March 23, 2012
Primary Keys
I came across a database created by another person in the company that
frequently has 2 primary keys on its tables. I'm not sure why it was done
this way, my guess is to create uniqueness with the 2-field combination.
My question is: Is this poor design? Shouldn't we create constraints or
indexes if 2-field uniqueness is the purpose. Is one way more efficient
than the other? Any other thoughts as to why a table would be set up this
way? Thanks.>> I came across a database created by another person in the company that
>> frequently has 2 primary keys on its tables. I'm not sure why it was
>> done this way, my guess is to create uniqueness with the 2-field
>> combination.
Since 2 primary keys are impossible in a single SQL table, I guess you meant
it to be a single key with two columns.
>> My question is: Is this poor design?
The number of columns in a key by itself does not tell us whether a specific
design is good or bad. There is nothing wrong in having multi-column keys;
however multi-column references can occasionally mess up data integrity
since SQL products allow NULLs in referencing columns.
>> Shouldn't we create constraints or indexes if 2-field uniqueness is the
>> purpose.
Not necessarily. Keys by themselves are constraints and guarantee uniqueness
any way.
>> Is one way more efficient than the other?
That is mostly irrelevant since at the logical level, key selection should
not be based on efficiency which is determined at the implementation level.
Considering the physical implementation level, unless we consider all
possible query combinations, data access paths and optimization rules and
test out all of them on a specific system using on a specific dataset, we
cannot generalize and conclude single column keys are efficient than
multicolumn keys or vice-versa.
--
Anith|||Sorry wasn't clear, you are correct, the tables have 2 columns combined to
make a concatenated primary key. In some instances, one column is a foreign
key of another table.
My next question: If a primary key consists of 2 columns, how would, or
could you, make that a foreign key in another table, and how would you query
such a thing?
Say for instance, I have a customer table, and an address table.
The address table fields:
CustomerID
AddressDescription
Address
Sample Data:
CustomerID AddressDescription Address
1 Main Office 123 Main
Street
1 Secondary Location 456 Main Street
So now I want to create another table that lists, for instance, people
located at each office. How would I set up the foreign key to point to the
address table. Wouldn't it be necessary to create another field in the
address table, maybe an identity field, and make that field the primary key?
And how would you query the database when joining tables? If you joined the
address table to a query, could you base it on both the CustomerID and
AddressDescription being equal to another field in another table. You can't
do this, can you? So I guess efficiency really isn't my question, maybe
more that it's a practicality or best practice issue: Under what
circumstances would a 2-column primary key be preferable to a nonmeaningful
primary key, with perhaps constraints on the 2 columns if the combined
uniqueness is the desired result?
I hope I'm making sense, thanks for your thoughts! And thanks for the
reminder about allowed nulls in the referenced columns, that could raise
serious issues.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OXm4hmstEHA.2184@.TK2MSFTNGP12.phx.gbl...
> >> I came across a database created by another person in the company that
> >> frequently has 2 primary keys on its tables. I'm not sure why it was
> >> done this way, my guess is to create uniqueness with the 2-field
> >> combination.
> Since 2 primary keys are impossible in a single SQL table, I guess you
meant
> it to be a single key with two columns.
> >> My question is: Is this poor design?
> The number of columns in a key by itself does not tell us whether a
specific
> design is good or bad. There is nothing wrong in having multi-column keys;
> however multi-column references can occasionally mess up data integrity
> since SQL products allow NULLs in referencing columns.
> >> Shouldn't we create constraints or indexes if 2-field uniqueness is the
> >> purpose.
> Not necessarily. Keys by themselves are constraints and guarantee
uniqueness
> any way.
> >> Is one way more efficient than the other?
> That is mostly irrelevant since at the logical level, key selection should
> not be based on efficiency which is determined at the implementation
level.
> Considering the physical implementation level, unless we consider all
> possible query combinations, data access paths and optimization rules and
> test out all of them on a specific system using on a specific dataset, we
> cannot generalize and conclude single column keys are efficient than
> multicolumn keys or vice-versa.
> --
> Anith
>|||To avoid problems and other types of modifications to the data structures, I
would suggest switching to a surrogate key and making that the PK. You can
then create a UNIQUE index covering those two columns.
This surrogate key allows you more flexibility down the road. It's easier
to add tables, create the joins (less typing anyhow), modify the current PK
to something else etc.
HTH
Rick
"Rock" <rockisland@.yahoo.com> wrote in message
news:OLbfeCttEHA.2804@.TK2MSFTNGP14.phx.gbl...
> Sorry wasn't clear, you are correct, the tables have 2 columns combined to
> make a concatenated primary key. In some instances, one column is a
foreign
> key of another table.
> My next question: If a primary key consists of 2 columns, how would, or
> could you, make that a foreign key in another table, and how would you
query
> such a thing?
> Say for instance, I have a customer table, and an address table.
> The address table fields:
> CustomerID
> AddressDescription
> Address
> Sample Data:
> CustomerID AddressDescription Address
> 1 Main Office 123 Main
> Street
> 1 Secondary Location 456 Main Street
> So now I want to create another table that lists, for instance, people
> located at each office. How would I set up the foreign key to point to
the
> address table. Wouldn't it be necessary to create another field in the
> address table, maybe an identity field, and make that field the primary
key?
> And how would you query the database when joining tables? If you joined
the
> address table to a query, could you base it on both the CustomerID and
> AddressDescription being equal to another field in another table. You
can't
> do this, can you? So I guess efficiency really isn't my question, maybe
> more that it's a practicality or best practice issue: Under what
> circumstances would a 2-column primary key be preferable to a
nonmeaningful
> primary key, with perhaps constraints on the 2 columns if the combined
> uniqueness is the desired result?
> I hope I'm making sense, thanks for your thoughts! And thanks for the
> reminder about allowed nulls in the referenced columns, that could raise
> serious issues.
>
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:OXm4hmstEHA.2184@.TK2MSFTNGP12.phx.gbl...
> > >> I came across a database created by another person in the company
that
> > >> frequently has 2 primary keys on its tables. I'm not sure why it was
> > >> done this way, my guess is to create uniqueness with the 2-field
> > >> combination.
> >
> > Since 2 primary keys are impossible in a single SQL table, I guess you
> meant
> > it to be a single key with two columns.
> >
> > >> My question is: Is this poor design?
> >
> > The number of columns in a key by itself does not tell us whether a
> specific
> > design is good or bad. There is nothing wrong in having multi-column
keys;
> > however multi-column references can occasionally mess up data integrity
> > since SQL products allow NULLs in referencing columns.
> >
> > >> Shouldn't we create constraints or indexes if 2-field uniqueness is
the
> > >> purpose.
> >
> > Not necessarily. Keys by themselves are constraints and guarantee
> uniqueness
> > any way.
> >
> > >> Is one way more efficient than the other?
> >
> > That is mostly irrelevant since at the logical level, key selection
should
> > not be based on efficiency which is determined at the implementation
> level.
> > Considering the physical implementation level, unless we consider all
> > possible query combinations, data access paths and optimization rules
and
> > test out all of them on a specific system using on a specific dataset,
we
> > cannot generalize and conclude single column keys are efficient than
> > multicolumn keys or vice-versa.
> >
> > --
> > Anith
> >
> >
>|||>> My next question: If a primary key consists of 2 columns, how would, or
>> could you, make that a foreign key in another table, and how would you
>> query such a thing?
That would be simple. Just make sure your referencing table has two columns
with compatible datatypes and you can use the REFERENCES clause in the
CREATE TABLE or ALTER TABLE DDL like:
CREATE TABLR tbl (
c1 INT NOT NULL, c2 CHAR(30) NOT NULL, ...
( c1, c2 ) REFERENCES tbl2 ( c1, c2 )
PRIMARY KEY ( c1, c2 ) ) ;
For exact syntax, please refer to SQL Server Books Online.
>> Wouldn't it be necessary to create another field in the address table,
>> maybe an identity field, and make that field the primary key?
Considering the example in your post, if the design is clean and both
columns are non-nullable they can be considered as valid choice for primary
key. ( Note that there are other desirable characteristics like stability,
minimality, simplicity and familiarity that come into play during key
selection as well )
>> Under what circumstances would a 2-column primary key be preferable to a
>> nonmeaningful primary key, with perhaps constraints on the 2 columns if
>> the combined uniqueness is the desired result?
As with many design considerations, the answer is it depends. If two columns
in the primary key can truly distinguish one entity from another, there is
no need to introduce a redundant identifier. And all the references to this
table must include the two columns and meaningful queries can be expressed
without loss of integrity.
However, multi-column keys can be often cumbersome, thereby could fail the
simplicity criteria. And NULLs in referencing columns is a serious issue in
SQL products which gives another logical reason to avoid multi-column keys
in some cases. That often gives a logical reason to use an additional
identifier which is unfamiliar, but stable, simple and minimal.
BTW, nothing prevents one from introducing artificial identifiers in all
your tables, but there are no proven advantages in doing so.
>> And thanks for the reminder about allowed nulls in the referenced
>> columns, that could raise serious issues.
Yes, it does. But neither the SQL standards nor the vendors are interested
in addressing such issues since they are mostly busy implementing UFOs :-)
--
Anith|||On Wed, 20 Oct 2004 12:14:14 -0600, Rock wrote:
>My next question: If a primary key consists of 2 columns, how would, or
>could you, make that a foreign key in another table, and how would you query
>such a thing?
>Say for instance, I have a customer table, and an address table.
>The address table fields:
>CustomerID
>AddressDescription
>Address
>Sample Data:
>CustomerID AddressDescription Address
>1 Main Office 123 Main
>Street
>1 Secondary Location 456 Main Street
>So now I want to create another table that lists, for instance, people
>located at each office. How would I set up the foreign key to point to the
>address table. Wouldn't it be necessary to create another field in the
>address table, maybe an identity field, and make that field the primary key?
Hi Rock,
That's not necessary. Your people table would look like this:
CREATE TABLE People (....,
....,
CustomerID int NOT NULL,
AddressDescription varchar(25) NOT NULL,
....,
FOREIGN KEY (CustomerID, AddressDescription)
REFERENCES Addresses,
....
)
>And how would you query the database when joining tables? If you joined the
>address table to a query, could you base it on both the CustomerID and
>AddressDescription being equal to another field in another table. You can't
>do this, can you?
Yes, you can:
SELECT some interesting columns
FROM People
INNER JOIN Addresses
ON Addresses.CustomerID = People.CustomerID
AND Addresses.AddressDescription = People.AddressDescription
WHERE ....
> So I guess efficiency really isn't my question, maybe
>more that it's a practicality or best practice issue: Under what
>circumstances would a 2-column primary key be preferable to a nonmeaningful
>primary key, with perhaps constraints on the 2 columns if the combined
>uniqueness is the desired result?
If the addresses table used CustomerID plus a short code for the
addresstype as it's primary key, I'd just use this natural key.
In your case, the more verbose AddressDescription is used as part of the
primary key. As you can see above, this complete description would have to
be repeated each time an address is referenced. This will use up more
space in your database. I would definitely consider using a shorter key.
This key *could* be the AddressType (moving the AddressDescription to
another table, listing all AddressTypes with their description), or I
could choose to introduce a surrogate key. This will take up more space in
the addresses table itself (as you add an extra column, plus an extra
index to check uniqueness on both the surrogate key and the combination of
CustomerID / AddressDescription), but it would reduce the amount of space
needed for references. It would increase speed on joins, but it might also
introduce the need to join in an extra table on some queries.
The bottom line is: there is no fixed answer, it all depends. At the end
of the day, you'll have to weigh all options against each other and choose
the one that fits your situation best.
>I hope I'm making sense, thanks for your thoughts! And thanks for the
>reminder about allowed nulls in the referenced columns, that could raise
>serious issues.
These can be easiliy avoided. Either set a NOT NULL constraint on the
columns, or (if you must allow NULLS) add a CHECK constraint to ensure
that either both columns are NULL, or both columns are not NULL.
CREATE TABLE People (....,
....,
CustomerID int, -- NULL allowed
AddressDescription varchar(25), -- NULL allowed
....,
FOREIGN KEY (CustomerID, AddressDescription)
REFERENCES Addresses,
CHECK ( ( CustomerID IS NULL
AND AddressDescription IS NULL)
OR ( CustomerID IS NOT NULL
AND AddressDescription IS NOT NULL))
....
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
>> These can be easiliy avoided. Either set a NOT NULL constraint on the
>> columns, or (if you must allow NULLS) add a CHECK constraint to ensure
>> that either both columns are NULL, or both columns are not NULL.
Not quite that easily. The problem is in regard to multi-column primary keys
and missing information. Relational literature has volumes about such
logical problems and the lack of theory behind handling missing information
being a serious issue.
We know SQL has only one way of dealing with missing information -- NULLs.
Since there is no mandatory requirement that all foreign key column values
should be non-nullable, SQL allows foreign key references on columns whose
values are known, unknown or partially known.
Now, consider a table with a multi-column primary key with columns, colA and
colB which is referenced by another table with corresponding multi-column
foreign key.
When the user inserts a referencing row with a value for colA and NULL for
colB, should the DBMS allow the insert? Since logically DBMS cannot preserve
the integrity of the relationship should it abort the insert? Or should it
consider the validity of the colA value alone and proceed with the insert?
What if there are duplicate values for colA in the referencing table which
cannot be evaluated due to NULLs in colB? If the colA value exists only once
should the DBMS substitute the only corresponding value for colB and proceed
with the insert ?
When deleting all the rows from the referenced table which contain a
specific value for colA, what should the DBMS do to the referencing table
that contains corresponding rows with a value for colA and a NULL for colB ?
While updating a few rows in the referenced table which contain a known colA
and colB, should the DBMS allow cascading actions on all corresponding rows
in the referencing table? Or should it evaluate the existence of rows with a
value for colA and exclude the rows with a null for colB ?
--
Anith|||On Wed, 20 Oct 2004 17:21:36 -0500, Anith Sen wrote:
>Hugo,
>> These can be easiliy avoided. Either set a NOT NULL constraint on the
>> columns, or (if you must allow NULLS) add a CHECK constraint to ensure
>> that either both columns are NULL, or both columns are not NULL.
>Not quite that easily. The problem is in regard to multi-column primary keys
>and missing information. Relational literature has volumes about such
>logical problems and the lack of theory behind handling missing information
>being a serious issue.
(snip)
>When the user inserts a referencing row with a value for colA and NULL for
>colB, should the DBMS allow the insert? Since logically DBMS cannot preserve
>the integrity of the relationship should it abort the insert? Or should it
>consider the validity of the colA value alone and proceed with the insert?
(snip more consequences of this proposed insert)
Hi Anith,
That is exactly why I suggested to either give all foreign key columns a
NOT NULL constraint, or (if NULL must be allowed) a CHECK to enforce that
they are either ALL null, or ALL not null. This will prevent all the
potential problems you describe.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql
frequently has 2 primary keys on its tables. I'm not sure why it was done
this way, my guess is to create uniqueness with the 2-field combination.
My question is: Is this poor design? Shouldn't we create constraints or
indexes if 2-field uniqueness is the purpose. Is one way more efficient
than the other? Any other thoughts as to why a table would be set up this
way? Thanks.>> I came across a database created by another person in the company that
>> frequently has 2 primary keys on its tables. I'm not sure why it was
>> done this way, my guess is to create uniqueness with the 2-field
>> combination.
Since 2 primary keys are impossible in a single SQL table, I guess you meant
it to be a single key with two columns.
>> My question is: Is this poor design?
The number of columns in a key by itself does not tell us whether a specific
design is good or bad. There is nothing wrong in having multi-column keys;
however multi-column references can occasionally mess up data integrity
since SQL products allow NULLs in referencing columns.
>> Shouldn't we create constraints or indexes if 2-field uniqueness is the
>> purpose.
Not necessarily. Keys by themselves are constraints and guarantee uniqueness
any way.
>> Is one way more efficient than the other?
That is mostly irrelevant since at the logical level, key selection should
not be based on efficiency which is determined at the implementation level.
Considering the physical implementation level, unless we consider all
possible query combinations, data access paths and optimization rules and
test out all of them on a specific system using on a specific dataset, we
cannot generalize and conclude single column keys are efficient than
multicolumn keys or vice-versa.
--
Anith|||Sorry wasn't clear, you are correct, the tables have 2 columns combined to
make a concatenated primary key. In some instances, one column is a foreign
key of another table.
My next question: If a primary key consists of 2 columns, how would, or
could you, make that a foreign key in another table, and how would you query
such a thing?
Say for instance, I have a customer table, and an address table.
The address table fields:
CustomerID
AddressDescription
Address
Sample Data:
CustomerID AddressDescription Address
1 Main Office 123 Main
Street
1 Secondary Location 456 Main Street
So now I want to create another table that lists, for instance, people
located at each office. How would I set up the foreign key to point to the
address table. Wouldn't it be necessary to create another field in the
address table, maybe an identity field, and make that field the primary key?
And how would you query the database when joining tables? If you joined the
address table to a query, could you base it on both the CustomerID and
AddressDescription being equal to another field in another table. You can't
do this, can you? So I guess efficiency really isn't my question, maybe
more that it's a practicality or best practice issue: Under what
circumstances would a 2-column primary key be preferable to a nonmeaningful
primary key, with perhaps constraints on the 2 columns if the combined
uniqueness is the desired result?
I hope I'm making sense, thanks for your thoughts! And thanks for the
reminder about allowed nulls in the referenced columns, that could raise
serious issues.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OXm4hmstEHA.2184@.TK2MSFTNGP12.phx.gbl...
> >> I came across a database created by another person in the company that
> >> frequently has 2 primary keys on its tables. I'm not sure why it was
> >> done this way, my guess is to create uniqueness with the 2-field
> >> combination.
> Since 2 primary keys are impossible in a single SQL table, I guess you
meant
> it to be a single key with two columns.
> >> My question is: Is this poor design?
> The number of columns in a key by itself does not tell us whether a
specific
> design is good or bad. There is nothing wrong in having multi-column keys;
> however multi-column references can occasionally mess up data integrity
> since SQL products allow NULLs in referencing columns.
> >> Shouldn't we create constraints or indexes if 2-field uniqueness is the
> >> purpose.
> Not necessarily. Keys by themselves are constraints and guarantee
uniqueness
> any way.
> >> Is one way more efficient than the other?
> That is mostly irrelevant since at the logical level, key selection should
> not be based on efficiency which is determined at the implementation
level.
> Considering the physical implementation level, unless we consider all
> possible query combinations, data access paths and optimization rules and
> test out all of them on a specific system using on a specific dataset, we
> cannot generalize and conclude single column keys are efficient than
> multicolumn keys or vice-versa.
> --
> Anith
>|||To avoid problems and other types of modifications to the data structures, I
would suggest switching to a surrogate key and making that the PK. You can
then create a UNIQUE index covering those two columns.
This surrogate key allows you more flexibility down the road. It's easier
to add tables, create the joins (less typing anyhow), modify the current PK
to something else etc.
HTH
Rick
"Rock" <rockisland@.yahoo.com> wrote in message
news:OLbfeCttEHA.2804@.TK2MSFTNGP14.phx.gbl...
> Sorry wasn't clear, you are correct, the tables have 2 columns combined to
> make a concatenated primary key. In some instances, one column is a
foreign
> key of another table.
> My next question: If a primary key consists of 2 columns, how would, or
> could you, make that a foreign key in another table, and how would you
query
> such a thing?
> Say for instance, I have a customer table, and an address table.
> The address table fields:
> CustomerID
> AddressDescription
> Address
> Sample Data:
> CustomerID AddressDescription Address
> 1 Main Office 123 Main
> Street
> 1 Secondary Location 456 Main Street
> So now I want to create another table that lists, for instance, people
> located at each office. How would I set up the foreign key to point to
the
> address table. Wouldn't it be necessary to create another field in the
> address table, maybe an identity field, and make that field the primary
key?
> And how would you query the database when joining tables? If you joined
the
> address table to a query, could you base it on both the CustomerID and
> AddressDescription being equal to another field in another table. You
can't
> do this, can you? So I guess efficiency really isn't my question, maybe
> more that it's a practicality or best practice issue: Under what
> circumstances would a 2-column primary key be preferable to a
nonmeaningful
> primary key, with perhaps constraints on the 2 columns if the combined
> uniqueness is the desired result?
> I hope I'm making sense, thanks for your thoughts! And thanks for the
> reminder about allowed nulls in the referenced columns, that could raise
> serious issues.
>
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:OXm4hmstEHA.2184@.TK2MSFTNGP12.phx.gbl...
> > >> I came across a database created by another person in the company
that
> > >> frequently has 2 primary keys on its tables. I'm not sure why it was
> > >> done this way, my guess is to create uniqueness with the 2-field
> > >> combination.
> >
> > Since 2 primary keys are impossible in a single SQL table, I guess you
> meant
> > it to be a single key with two columns.
> >
> > >> My question is: Is this poor design?
> >
> > The number of columns in a key by itself does not tell us whether a
> specific
> > design is good or bad. There is nothing wrong in having multi-column
keys;
> > however multi-column references can occasionally mess up data integrity
> > since SQL products allow NULLs in referencing columns.
> >
> > >> Shouldn't we create constraints or indexes if 2-field uniqueness is
the
> > >> purpose.
> >
> > Not necessarily. Keys by themselves are constraints and guarantee
> uniqueness
> > any way.
> >
> > >> Is one way more efficient than the other?
> >
> > That is mostly irrelevant since at the logical level, key selection
should
> > not be based on efficiency which is determined at the implementation
> level.
> > Considering the physical implementation level, unless we consider all
> > possible query combinations, data access paths and optimization rules
and
> > test out all of them on a specific system using on a specific dataset,
we
> > cannot generalize and conclude single column keys are efficient than
> > multicolumn keys or vice-versa.
> >
> > --
> > Anith
> >
> >
>|||>> My next question: If a primary key consists of 2 columns, how would, or
>> could you, make that a foreign key in another table, and how would you
>> query such a thing?
That would be simple. Just make sure your referencing table has two columns
with compatible datatypes and you can use the REFERENCES clause in the
CREATE TABLE or ALTER TABLE DDL like:
CREATE TABLR tbl (
c1 INT NOT NULL, c2 CHAR(30) NOT NULL, ...
( c1, c2 ) REFERENCES tbl2 ( c1, c2 )
PRIMARY KEY ( c1, c2 ) ) ;
For exact syntax, please refer to SQL Server Books Online.
>> Wouldn't it be necessary to create another field in the address table,
>> maybe an identity field, and make that field the primary key?
Considering the example in your post, if the design is clean and both
columns are non-nullable they can be considered as valid choice for primary
key. ( Note that there are other desirable characteristics like stability,
minimality, simplicity and familiarity that come into play during key
selection as well )
>> Under what circumstances would a 2-column primary key be preferable to a
>> nonmeaningful primary key, with perhaps constraints on the 2 columns if
>> the combined uniqueness is the desired result?
As with many design considerations, the answer is it depends. If two columns
in the primary key can truly distinguish one entity from another, there is
no need to introduce a redundant identifier. And all the references to this
table must include the two columns and meaningful queries can be expressed
without loss of integrity.
However, multi-column keys can be often cumbersome, thereby could fail the
simplicity criteria. And NULLs in referencing columns is a serious issue in
SQL products which gives another logical reason to avoid multi-column keys
in some cases. That often gives a logical reason to use an additional
identifier which is unfamiliar, but stable, simple and minimal.
BTW, nothing prevents one from introducing artificial identifiers in all
your tables, but there are no proven advantages in doing so.
>> And thanks for the reminder about allowed nulls in the referenced
>> columns, that could raise serious issues.
Yes, it does. But neither the SQL standards nor the vendors are interested
in addressing such issues since they are mostly busy implementing UFOs :-)
--
Anith|||On Wed, 20 Oct 2004 12:14:14 -0600, Rock wrote:
>My next question: If a primary key consists of 2 columns, how would, or
>could you, make that a foreign key in another table, and how would you query
>such a thing?
>Say for instance, I have a customer table, and an address table.
>The address table fields:
>CustomerID
>AddressDescription
>Address
>Sample Data:
>CustomerID AddressDescription Address
>1 Main Office 123 Main
>Street
>1 Secondary Location 456 Main Street
>So now I want to create another table that lists, for instance, people
>located at each office. How would I set up the foreign key to point to the
>address table. Wouldn't it be necessary to create another field in the
>address table, maybe an identity field, and make that field the primary key?
Hi Rock,
That's not necessary. Your people table would look like this:
CREATE TABLE People (....,
....,
CustomerID int NOT NULL,
AddressDescription varchar(25) NOT NULL,
....,
FOREIGN KEY (CustomerID, AddressDescription)
REFERENCES Addresses,
....
)
>And how would you query the database when joining tables? If you joined the
>address table to a query, could you base it on both the CustomerID and
>AddressDescription being equal to another field in another table. You can't
>do this, can you?
Yes, you can:
SELECT some interesting columns
FROM People
INNER JOIN Addresses
ON Addresses.CustomerID = People.CustomerID
AND Addresses.AddressDescription = People.AddressDescription
WHERE ....
> So I guess efficiency really isn't my question, maybe
>more that it's a practicality or best practice issue: Under what
>circumstances would a 2-column primary key be preferable to a nonmeaningful
>primary key, with perhaps constraints on the 2 columns if the combined
>uniqueness is the desired result?
If the addresses table used CustomerID plus a short code for the
addresstype as it's primary key, I'd just use this natural key.
In your case, the more verbose AddressDescription is used as part of the
primary key. As you can see above, this complete description would have to
be repeated each time an address is referenced. This will use up more
space in your database. I would definitely consider using a shorter key.
This key *could* be the AddressType (moving the AddressDescription to
another table, listing all AddressTypes with their description), or I
could choose to introduce a surrogate key. This will take up more space in
the addresses table itself (as you add an extra column, plus an extra
index to check uniqueness on both the surrogate key and the combination of
CustomerID / AddressDescription), but it would reduce the amount of space
needed for references. It would increase speed on joins, but it might also
introduce the need to join in an extra table on some queries.
The bottom line is: there is no fixed answer, it all depends. At the end
of the day, you'll have to weigh all options against each other and choose
the one that fits your situation best.
>I hope I'm making sense, thanks for your thoughts! And thanks for the
>reminder about allowed nulls in the referenced columns, that could raise
>serious issues.
These can be easiliy avoided. Either set a NOT NULL constraint on the
columns, or (if you must allow NULLS) add a CHECK constraint to ensure
that either both columns are NULL, or both columns are not NULL.
CREATE TABLE People (....,
....,
CustomerID int, -- NULL allowed
AddressDescription varchar(25), -- NULL allowed
....,
FOREIGN KEY (CustomerID, AddressDescription)
REFERENCES Addresses,
CHECK ( ( CustomerID IS NULL
AND AddressDescription IS NULL)
OR ( CustomerID IS NOT NULL
AND AddressDescription IS NOT NULL))
....
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
>> These can be easiliy avoided. Either set a NOT NULL constraint on the
>> columns, or (if you must allow NULLS) add a CHECK constraint to ensure
>> that either both columns are NULL, or both columns are not NULL.
Not quite that easily. The problem is in regard to multi-column primary keys
and missing information. Relational literature has volumes about such
logical problems and the lack of theory behind handling missing information
being a serious issue.
We know SQL has only one way of dealing with missing information -- NULLs.
Since there is no mandatory requirement that all foreign key column values
should be non-nullable, SQL allows foreign key references on columns whose
values are known, unknown or partially known.
Now, consider a table with a multi-column primary key with columns, colA and
colB which is referenced by another table with corresponding multi-column
foreign key.
When the user inserts a referencing row with a value for colA and NULL for
colB, should the DBMS allow the insert? Since logically DBMS cannot preserve
the integrity of the relationship should it abort the insert? Or should it
consider the validity of the colA value alone and proceed with the insert?
What if there are duplicate values for colA in the referencing table which
cannot be evaluated due to NULLs in colB? If the colA value exists only once
should the DBMS substitute the only corresponding value for colB and proceed
with the insert ?
When deleting all the rows from the referenced table which contain a
specific value for colA, what should the DBMS do to the referencing table
that contains corresponding rows with a value for colA and a NULL for colB ?
While updating a few rows in the referenced table which contain a known colA
and colB, should the DBMS allow cascading actions on all corresponding rows
in the referencing table? Or should it evaluate the existence of rows with a
value for colA and exclude the rows with a null for colB ?
--
Anith|||On Wed, 20 Oct 2004 17:21:36 -0500, Anith Sen wrote:
>Hugo,
>> These can be easiliy avoided. Either set a NOT NULL constraint on the
>> columns, or (if you must allow NULLS) add a CHECK constraint to ensure
>> that either both columns are NULL, or both columns are not NULL.
>Not quite that easily. The problem is in regard to multi-column primary keys
>and missing information. Relational literature has volumes about such
>logical problems and the lack of theory behind handling missing information
>being a serious issue.
(snip)
>When the user inserts a referencing row with a value for colA and NULL for
>colB, should the DBMS allow the insert? Since logically DBMS cannot preserve
>the integrity of the relationship should it abort the insert? Or should it
>consider the validity of the colA value alone and proceed with the insert?
(snip more consequences of this proposed insert)
Hi Anith,
That is exactly why I suggested to either give all foreign key columns a
NOT NULL constraint, or (if NULL must be allowed) a CHECK to enforce that
they are either ALL null, or ALL not null. This will prevent all the
potential problems you describe.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql
Primary Keys
I came across a database created by another person in the company that
frequently has 2 primary keys on its tables. I'm not sure why it was done
this way, my guess is to create uniqueness with the 2-field combination.
My question is: Is this poor design? Shouldn't we create constraints or
indexes if 2-field uniqueness is the purpose. Is one way more efficient
than the other? Any other thoughts as to why a table would be set up this
way? Thanks.>> I came across a database created by another person in the company that[vbcol=seagreen]
Since 2 primary keys are impossible in a single SQL table, I guess you meant
it to be a single key with two columns.
[vbcol=seagreen]
The number of columns in a key by itself does not tell us whether a specific
design is good or bad. There is nothing wrong in having multi-column keys;
however multi-column references can occasionally mess up data integrity
since SQL products allow NULLs in referencing columns.
[vbcol=seagreen]
Not necessarily. Keys by themselves are constraints and guarantee uniqueness
any way.
[vbcol=seagreen]
That is mostly irrelevant since at the logical level, key selection should
not be based on efficiency which is determined at the implementation level.
Considering the physical implementation level, unless we consider all
possible query combinations, data access paths and optimization rules and
test out all of them on a specific system using on a specific dataset, we
cannot generalize and conclude single column keys are efficient than
multicolumn keys or vice-versa.
Anith|||Sorry wasn't clear, you are correct, the tables have 2 columns combined to
make a concatenated primary key. In some instances, one column is a foreign
key of another table.
My next question: If a primary key consists of 2 columns, how would, or
could you, make that a foreign key in another table, and how would you query
such a thing?
Say for instance, I have a customer table, and an address table.
The address table fields:
CustomerID
AddressDescription
Address
Sample Data:
CustomerID AddressDescription Address
1 Main Office 123 Main
Street
1 Secondary Location 456 Main Street
So now I want to create another table that lists, for instance, people
located at each office. How would I set up the foreign key to point to the
address table. Wouldn't it be necessary to create another field in the
address table, maybe an identity field, and make that field the primary key?
And how would you query the database when joining tables? If you joined the
address table to a query, could you base it on both the CustomerID and
AddressDescription being equal to another field in another table. You can't
do this, can you? So I guess efficiency really isn't my question, maybe
more that it's a practicality or best practice issue: Under what
circumstances would a 2-column primary key be preferable to a nonmeaningful
primary key, with perhaps constraints on the 2 columns if the combined
uniqueness is the desired result?
I hope I'm making sense, thanks for your thoughts! And thanks for the
reminder about allowed nulls in the referenced columns, that could raise
serious issues.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OXm4hmstEHA.2184@.TK2MSFTNGP12.phx.gbl...
> Since 2 primary keys are impossible in a single SQL table, I guess you
meant
> it to be a single key with two columns.
>
> The number of columns in a key by itself does not tell us whether a
specific
> design is good or bad. There is nothing wrong in having multi-column keys;
> however multi-column references can occasionally mess up data integrity
> since SQL products allow NULLs in referencing columns.
>
> Not necessarily. Keys by themselves are constraints and guarantee
uniqueness
> any way.
>
> That is mostly irrelevant since at the logical level, key selection should
> not be based on efficiency which is determined at the implementation
level.
> Considering the physical implementation level, unless we consider all
> possible query combinations, data access paths and optimization rules and
> test out all of them on a specific system using on a specific dataset, we
> cannot generalize and conclude single column keys are efficient than
> multicolumn keys or vice-versa.
> --
> Anith
>|||To avoid problems and other types of modifications to the data structures, I
would suggest switching to a surrogate key and making that the PK. You can
then create a UNIQUE index covering those two columns.
This surrogate key allows you more flexibility down the road. It's easier
to add tables, create the joins (less typing anyhow), modify the current PK
to something else etc.
HTH
Rick
"Rock" <rockisland@.yahoo.com> wrote in message
news:OLbfeCttEHA.2804@.TK2MSFTNGP14.phx.gbl...
> Sorry wasn't clear, you are correct, the tables have 2 columns combined to
> make a concatenated primary key. In some instances, one column is a
foreign
> key of another table.
> My next question: If a primary key consists of 2 columns, how would, or
> could you, make that a foreign key in another table, and how would you
query
> such a thing?
> Say for instance, I have a customer table, and an address table.
> The address table fields:
> CustomerID
> AddressDescription
> Address
> Sample Data:
> CustomerID AddressDescription Address
> 1 Main Office 123 Main
> Street
> 1 Secondary Location 456 Main Street
> So now I want to create another table that lists, for instance, people
> located at each office. How would I set up the foreign key to point to
the
> address table. Wouldn't it be necessary to create another field in the
> address table, maybe an identity field, and make that field the primary
key?
> And how would you query the database when joining tables? If you joined
the
> address table to a query, could you base it on both the CustomerID and
> AddressDescription being equal to another field in another table. You
can't
> do this, can you? So I guess efficiency really isn't my question, maybe
> more that it's a practicality or best practice issue: Under what
> circumstances would a 2-column primary key be preferable to a
nonmeaningful
> primary key, with perhaps constraints on the 2 columns if the combined
> uniqueness is the desired result?
> I hope I'm making sense, thanks for your thoughts! And thanks for the
> reminder about allowed nulls in the referenced columns, that could raise
> serious issues.
>
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:OXm4hmstEHA.2184@.TK2MSFTNGP12.phx.gbl...
that[vbcol=seagreen]
> meant
> specific
keys;[vbcol=seagreen]
the[vbcol=seagreen]
> uniqueness
should[vbcol=seagreen]
> level.
and[vbcol=seagreen]
we[vbcol=seagreen]
>|||>> My next question: If a primary key consists of 2 columns, how would, or[vbcol=seagreen]
That would be simple. Just make sure your referencing table has two columns
with compatible datatypes and you can use the REFERENCES clause in the
CREATE TABLE or ALTER TABLE DDL like:
CREATE TABLR tbl (
c1 INT NOT NULL, c2 CHAR(30) NOT NULL, ...
( c1, c2 ) REFERENCES tbl2 ( c1, c2 )
PRIMARY KEY ( c1, c2 ) ) ;
For exact syntax, please refer to SQL Server Books Online.
[vbcol=seagreen]
Considering the example in your post, if the design is clean and both
columns are non-nullable they can be considered as valid choice for primary
key. ( Note that there are other desirable characteristics like stability,
minimality, simplicity and familiarity that come into play during key
selection as well )
[vbcol=seagreen]
As with many design considerations, the answer is it depends. If two columns
in the primary key can truly distinguish one entity from another, there is
no need to introduce a redundant identifier. And all the references to this
table must include the two columns and meaningful queries can be expressed
without loss of integrity.
However, multi-column keys can be often cumbersome, thereby could fail the
simplicity criteria. And NULLs in referencing columns is a serious issue in
SQL products which gives another logical reason to avoid multi-column keys
in some cases. That often gives a logical reason to use an additional
identifier which is unfamiliar, but stable, simple and minimal.
BTW, nothing prevents one from introducing artificial identifiers in all
your tables, but there are no proven advantages in doing so.
[vbcol=seagreen]
Yes, it does. But neither the SQL standards nor the vendors are interested
in addressing such issues since they are mostly busy implementing UFOs :-)
Anith|||On Wed, 20 Oct 2004 12:14:14 -0600, Rock wrote:
>My next question: If a primary key consists of 2 columns, how would, or
>could you, make that a foreign key in another table, and how would you quer
y
>such a thing?
>Say for instance, I have a customer table, and an address table.
>The address table fields:
>CustomerID
>AddressDescription
>Address
>Sample Data:
>CustomerID AddressDescription Address
>1 Main Office 123 Main
>Street
>1 Secondary Location 456 Main Street
>So now I want to create another table that lists, for instance, people
>located at each office. How would I set up the foreign key to point to the
>address table. Wouldn't it be necessary to create another field in the
>address table, maybe an identity field, and make that field the primary key?[/vbcol
]
Hi Rock,
That's not necessary. Your people table would look like this:
CREATE TABLE People (....,
...,
CustomerID int NOT NULL,
AddressDescription varchar(25) NOT NULL,
...,
FOREIGN KEY (CustomerID, AddressDescription)
REFERENCES Addresses,
...
)
[vbcol=seagreen]
>And how would you query the database when joining tables? If you joined th
e
>address table to a query, could you base it on both the CustomerID and
>AddressDescription being equal to another field in another table. You can'
t
>do this, can you?
Yes, you can:
SELECT some interesting columns
FROM People
INNER JOIN Addresses
ON Addresses.CustomerID = People.CustomerID
AND Addresses.AddressDescription = People.AddressDescription
WHERE ....
> So I guess efficiency really isn't my question, maybe
>more that it's a practicality or best practice issue: Under what
>circumstances would a 2-column primary key be preferable to a nonmeaningful
>primary key, with perhaps constraints on the 2 columns if the combined
>uniqueness is the desired result?
If the addresses table used CustomerID plus a short code for the
addresstype as it's primary key, I'd just use this natural key.
In your case, the more verbose AddressDescription is used as part of the
primary key. As you can see above, this complete description would have to
be repeated each time an address is referenced. This will use up more
space in your database. I would definitely consider using a shorter key.
This key *could* be the AddressType (moving the AddressDescription to
another table, listing all AddressTypes with their description), or I
could choose to introduce a surrogate key. This will take up more space in
the addresses table itself (as you add an extra column, plus an extra
index to check uniqueness on both the surrogate key and the combination of
CustomerID / AddressDescription), but it would reduce the amount of space
needed for references. It would increase speed on joins, but it might also
introduce the need to join in an extra table on some queries.
The bottom line is: there is no fixed answer, it all depends. At the end
of the day, you'll have to weigh all options against each other and choose
the one that fits your situation best.
>I hope I'm making sense, thanks for your thoughts! And thanks for the
>reminder about allowed nulls in the referenced columns, that could raise
>serious issues.
These can be easiliy avoided. Either set a NOT NULL constraint on the
columns, or (if you must allow NULLS) add a CHECK constraint to ensure
that either both columns are NULL, or both columns are not NULL.
CREATE TABLE People (....,
...,
CustomerID int, -- NULL allowed
AddressDescription varchar(25), -- NULL allowed
...,
FOREIGN KEY (CustomerID, AddressDescription)
REFERENCES Addresses,
CHECK ( ( CustomerID IS NULL
AND AddressDescription IS NULL)
OR ( CustomerID IS NOT NULL
AND AddressDescription IS NOT NULL))
...
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
[vbcol=seagreen]
Not quite that easily. The problem is in regard to multi-column primary keys
and missing information. Relational literature has volumes about such
logical problems and the lack of theory behind handling missing information
being a serious issue.
We know SQL has only one way of dealing with missing information -- NULLs.
Since there is no mandatory requirement that all foreign key column values
should be non-nullable, SQL allows foreign key references on columns whose
values are known, unknown or partially known.
Now, consider a table with a multi-column primary key with columns, colA and
colB which is referenced by another table with corresponding multi-column
foreign key.
When the user inserts a referencing row with a value for colA and NULL for
colB, should the DBMS allow the insert? Since logically DBMS cannot preserve
the integrity of the relationship should it abort the insert? Or should it
consider the validity of the colA value alone and proceed with the insert?
What if there are duplicate values for colA in the referencing table which
cannot be evaluated due to NULLs in colB? If the colA value exists only once
should the DBMS substitute the only corresponding value for colB and proceed
with the insert ?
When deleting all the rows from the referenced table which contain a
specific value for colA, what should the DBMS do to the referencing table
that contains corresponding rows with a value for colA and a NULL for colB ?
While updating a few rows in the referenced table which contain a known colA
and colB, should the DBMS allow cascading actions on all corresponding rows
in the referencing table? Or should it evaluate the existence of rows with a
value for colA and exclude the rows with a null for colB ?
Anith|||On Wed, 20 Oct 2004 17:21:36 -0500, Anith Sen wrote:
>Hugo,
>
>Not quite that easily. The problem is in regard to multi-column primary key
s
>and missing information. Relational literature has volumes about such
>logical problems and the lack of theory behind handling missing information
>being a serious issue.
(snip)
>When the user inserts a referencing row with a value for colA and NULL for
>colB, should the DBMS allow the insert? Since logically DBMS cannot preserv
e
>the integrity of the relationship should it abort the insert? Or should it
>consider the validity of the colA value alone and proceed with the insert?
(snip more consequences of this proposed insert)
Hi Anith,
That is exactly why I suggested to either give all foreign key columns a
NOT NULL constraint, or (if NULL must be allowed) a CHECK to enforce that
they are either ALL null, or ALL not null. This will prevent all the
potential problems you describe.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
frequently has 2 primary keys on its tables. I'm not sure why it was done
this way, my guess is to create uniqueness with the 2-field combination.
My question is: Is this poor design? Shouldn't we create constraints or
indexes if 2-field uniqueness is the purpose. Is one way more efficient
than the other? Any other thoughts as to why a table would be set up this
way? Thanks.>> I came across a database created by another person in the company that[vbcol=seagreen]
Since 2 primary keys are impossible in a single SQL table, I guess you meant
it to be a single key with two columns.
[vbcol=seagreen]
The number of columns in a key by itself does not tell us whether a specific
design is good or bad. There is nothing wrong in having multi-column keys;
however multi-column references can occasionally mess up data integrity
since SQL products allow NULLs in referencing columns.
[vbcol=seagreen]
Not necessarily. Keys by themselves are constraints and guarantee uniqueness
any way.
[vbcol=seagreen]
That is mostly irrelevant since at the logical level, key selection should
not be based on efficiency which is determined at the implementation level.
Considering the physical implementation level, unless we consider all
possible query combinations, data access paths and optimization rules and
test out all of them on a specific system using on a specific dataset, we
cannot generalize and conclude single column keys are efficient than
multicolumn keys or vice-versa.
Anith|||Sorry wasn't clear, you are correct, the tables have 2 columns combined to
make a concatenated primary key. In some instances, one column is a foreign
key of another table.
My next question: If a primary key consists of 2 columns, how would, or
could you, make that a foreign key in another table, and how would you query
such a thing?
Say for instance, I have a customer table, and an address table.
The address table fields:
CustomerID
AddressDescription
Address
Sample Data:
CustomerID AddressDescription Address
1 Main Office 123 Main
Street
1 Secondary Location 456 Main Street
So now I want to create another table that lists, for instance, people
located at each office. How would I set up the foreign key to point to the
address table. Wouldn't it be necessary to create another field in the
address table, maybe an identity field, and make that field the primary key?
And how would you query the database when joining tables? If you joined the
address table to a query, could you base it on both the CustomerID and
AddressDescription being equal to another field in another table. You can't
do this, can you? So I guess efficiency really isn't my question, maybe
more that it's a practicality or best practice issue: Under what
circumstances would a 2-column primary key be preferable to a nonmeaningful
primary key, with perhaps constraints on the 2 columns if the combined
uniqueness is the desired result?
I hope I'm making sense, thanks for your thoughts! And thanks for the
reminder about allowed nulls in the referenced columns, that could raise
serious issues.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OXm4hmstEHA.2184@.TK2MSFTNGP12.phx.gbl...
> Since 2 primary keys are impossible in a single SQL table, I guess you
meant
> it to be a single key with two columns.
>
> The number of columns in a key by itself does not tell us whether a
specific
> design is good or bad. There is nothing wrong in having multi-column keys;
> however multi-column references can occasionally mess up data integrity
> since SQL products allow NULLs in referencing columns.
>
> Not necessarily. Keys by themselves are constraints and guarantee
uniqueness
> any way.
>
> That is mostly irrelevant since at the logical level, key selection should
> not be based on efficiency which is determined at the implementation
level.
> Considering the physical implementation level, unless we consider all
> possible query combinations, data access paths and optimization rules and
> test out all of them on a specific system using on a specific dataset, we
> cannot generalize and conclude single column keys are efficient than
> multicolumn keys or vice-versa.
> --
> Anith
>|||To avoid problems and other types of modifications to the data structures, I
would suggest switching to a surrogate key and making that the PK. You can
then create a UNIQUE index covering those two columns.
This surrogate key allows you more flexibility down the road. It's easier
to add tables, create the joins (less typing anyhow), modify the current PK
to something else etc.
HTH
Rick
"Rock" <rockisland@.yahoo.com> wrote in message
news:OLbfeCttEHA.2804@.TK2MSFTNGP14.phx.gbl...
> Sorry wasn't clear, you are correct, the tables have 2 columns combined to
> make a concatenated primary key. In some instances, one column is a
foreign
> key of another table.
> My next question: If a primary key consists of 2 columns, how would, or
> could you, make that a foreign key in another table, and how would you
query
> such a thing?
> Say for instance, I have a customer table, and an address table.
> The address table fields:
> CustomerID
> AddressDescription
> Address
> Sample Data:
> CustomerID AddressDescription Address
> 1 Main Office 123 Main
> Street
> 1 Secondary Location 456 Main Street
> So now I want to create another table that lists, for instance, people
> located at each office. How would I set up the foreign key to point to
the
> address table. Wouldn't it be necessary to create another field in the
> address table, maybe an identity field, and make that field the primary
key?
> And how would you query the database when joining tables? If you joined
the
> address table to a query, could you base it on both the CustomerID and
> AddressDescription being equal to another field in another table. You
can't
> do this, can you? So I guess efficiency really isn't my question, maybe
> more that it's a practicality or best practice issue: Under what
> circumstances would a 2-column primary key be preferable to a
nonmeaningful
> primary key, with perhaps constraints on the 2 columns if the combined
> uniqueness is the desired result?
> I hope I'm making sense, thanks for your thoughts! And thanks for the
> reminder about allowed nulls in the referenced columns, that could raise
> serious issues.
>
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:OXm4hmstEHA.2184@.TK2MSFTNGP12.phx.gbl...
that[vbcol=seagreen]
> meant
> specific
keys;[vbcol=seagreen]
the[vbcol=seagreen]
> uniqueness
should[vbcol=seagreen]
> level.
and[vbcol=seagreen]
we[vbcol=seagreen]
>|||>> My next question: If a primary key consists of 2 columns, how would, or[vbcol=seagreen]
That would be simple. Just make sure your referencing table has two columns
with compatible datatypes and you can use the REFERENCES clause in the
CREATE TABLE or ALTER TABLE DDL like:
CREATE TABLR tbl (
c1 INT NOT NULL, c2 CHAR(30) NOT NULL, ...
( c1, c2 ) REFERENCES tbl2 ( c1, c2 )
PRIMARY KEY ( c1, c2 ) ) ;
For exact syntax, please refer to SQL Server Books Online.
[vbcol=seagreen]
Considering the example in your post, if the design is clean and both
columns are non-nullable they can be considered as valid choice for primary
key. ( Note that there are other desirable characteristics like stability,
minimality, simplicity and familiarity that come into play during key
selection as well )
[vbcol=seagreen]
As with many design considerations, the answer is it depends. If two columns
in the primary key can truly distinguish one entity from another, there is
no need to introduce a redundant identifier. And all the references to this
table must include the two columns and meaningful queries can be expressed
without loss of integrity.
However, multi-column keys can be often cumbersome, thereby could fail the
simplicity criteria. And NULLs in referencing columns is a serious issue in
SQL products which gives another logical reason to avoid multi-column keys
in some cases. That often gives a logical reason to use an additional
identifier which is unfamiliar, but stable, simple and minimal.
BTW, nothing prevents one from introducing artificial identifiers in all
your tables, but there are no proven advantages in doing so.
[vbcol=seagreen]
Yes, it does. But neither the SQL standards nor the vendors are interested
in addressing such issues since they are mostly busy implementing UFOs :-)
Anith|||On Wed, 20 Oct 2004 12:14:14 -0600, Rock wrote:
>My next question: If a primary key consists of 2 columns, how would, or
>could you, make that a foreign key in another table, and how would you quer
y
>such a thing?
>Say for instance, I have a customer table, and an address table.
>The address table fields:
>CustomerID
>AddressDescription
>Address
>Sample Data:
>CustomerID AddressDescription Address
>1 Main Office 123 Main
>Street
>1 Secondary Location 456 Main Street
>So now I want to create another table that lists, for instance, people
>located at each office. How would I set up the foreign key to point to the
>address table. Wouldn't it be necessary to create another field in the
>address table, maybe an identity field, and make that field the primary key?[/vbcol
]
Hi Rock,
That's not necessary. Your people table would look like this:
CREATE TABLE People (....,
...,
CustomerID int NOT NULL,
AddressDescription varchar(25) NOT NULL,
...,
FOREIGN KEY (CustomerID, AddressDescription)
REFERENCES Addresses,
...
)
[vbcol=seagreen]
>And how would you query the database when joining tables? If you joined th
e
>address table to a query, could you base it on both the CustomerID and
>AddressDescription being equal to another field in another table. You can'
t
>do this, can you?
Yes, you can:
SELECT some interesting columns
FROM People
INNER JOIN Addresses
ON Addresses.CustomerID = People.CustomerID
AND Addresses.AddressDescription = People.AddressDescription
WHERE ....
> So I guess efficiency really isn't my question, maybe
>more that it's a practicality or best practice issue: Under what
>circumstances would a 2-column primary key be preferable to a nonmeaningful
>primary key, with perhaps constraints on the 2 columns if the combined
>uniqueness is the desired result?
If the addresses table used CustomerID plus a short code for the
addresstype as it's primary key, I'd just use this natural key.
In your case, the more verbose AddressDescription is used as part of the
primary key. As you can see above, this complete description would have to
be repeated each time an address is referenced. This will use up more
space in your database. I would definitely consider using a shorter key.
This key *could* be the AddressType (moving the AddressDescription to
another table, listing all AddressTypes with their description), or I
could choose to introduce a surrogate key. This will take up more space in
the addresses table itself (as you add an extra column, plus an extra
index to check uniqueness on both the surrogate key and the combination of
CustomerID / AddressDescription), but it would reduce the amount of space
needed for references. It would increase speed on joins, but it might also
introduce the need to join in an extra table on some queries.
The bottom line is: there is no fixed answer, it all depends. At the end
of the day, you'll have to weigh all options against each other and choose
the one that fits your situation best.
>I hope I'm making sense, thanks for your thoughts! And thanks for the
>reminder about allowed nulls in the referenced columns, that could raise
>serious issues.
These can be easiliy avoided. Either set a NOT NULL constraint on the
columns, or (if you must allow NULLS) add a CHECK constraint to ensure
that either both columns are NULL, or both columns are not NULL.
CREATE TABLE People (....,
...,
CustomerID int, -- NULL allowed
AddressDescription varchar(25), -- NULL allowed
...,
FOREIGN KEY (CustomerID, AddressDescription)
REFERENCES Addresses,
CHECK ( ( CustomerID IS NULL
AND AddressDescription IS NULL)
OR ( CustomerID IS NOT NULL
AND AddressDescription IS NOT NULL))
...
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
[vbcol=seagreen]
Not quite that easily. The problem is in regard to multi-column primary keys
and missing information. Relational literature has volumes about such
logical problems and the lack of theory behind handling missing information
being a serious issue.
We know SQL has only one way of dealing with missing information -- NULLs.
Since there is no mandatory requirement that all foreign key column values
should be non-nullable, SQL allows foreign key references on columns whose
values are known, unknown or partially known.
Now, consider a table with a multi-column primary key with columns, colA and
colB which is referenced by another table with corresponding multi-column
foreign key.
When the user inserts a referencing row with a value for colA and NULL for
colB, should the DBMS allow the insert? Since logically DBMS cannot preserve
the integrity of the relationship should it abort the insert? Or should it
consider the validity of the colA value alone and proceed with the insert?
What if there are duplicate values for colA in the referencing table which
cannot be evaluated due to NULLs in colB? If the colA value exists only once
should the DBMS substitute the only corresponding value for colB and proceed
with the insert ?
When deleting all the rows from the referenced table which contain a
specific value for colA, what should the DBMS do to the referencing table
that contains corresponding rows with a value for colA and a NULL for colB ?
While updating a few rows in the referenced table which contain a known colA
and colB, should the DBMS allow cascading actions on all corresponding rows
in the referencing table? Or should it evaluate the existence of rows with a
value for colA and exclude the rows with a null for colB ?
Anith|||On Wed, 20 Oct 2004 17:21:36 -0500, Anith Sen wrote:
>Hugo,
>
>Not quite that easily. The problem is in regard to multi-column primary key
s
>and missing information. Relational literature has volumes about such
>logical problems and the lack of theory behind handling missing information
>being a serious issue.
(snip)
>When the user inserts a referencing row with a value for colA and NULL for
>colB, should the DBMS allow the insert? Since logically DBMS cannot preserv
e
>the integrity of the relationship should it abort the insert? Or should it
>consider the validity of the colA value alone and proceed with the insert?
(snip more consequences of this proposed insert)
Hi Anith,
That is exactly why I suggested to either give all foreign key columns a
NOT NULL constraint, or (if NULL must be allowed) a CHECK to enforce that
they are either ALL null, or ALL not null. This will prevent all the
potential problems you describe.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Primary Key Violation Error
SQL 2000 Sp4.
I have a table (SearchStore) with has a composite primary key across all
fields.
I am trying to insert 28 UNIQUE records in it, but am getting a primary key
violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
there any restirction on how many fields a composite primary key can include?
It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can see
the records are unique by the 'Records' field
Below is the create statment and the Records, any ideas?
CREATE TABLE [dbo].[SearchStore] (
[Record] [int] NOT NULL ,
[WorldTwoTier_WorldResDestCode] [nvarchar] (278) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WorldTwoTier_HotelID] [int] NOT NULL ,
[WorldTwoTier_HotelCode] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[ImageUsedID] [int] NOT NULL ,
[SearchedAt] [datetime] NOT NULL ,
[UserGUID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SearchStore] WITH NOCHECK ADD
CONSTRAINT [PK_SearchStore] PRIMARY KEY CLUSTERED
(
[Record],
[WorldTwoTier_WorldResDestCode],
[WorldTwoTier_HotelID],
[WorldTwoTier_HotelCode],
[ImageUsedID],
[SearchedAt],
[UserGUID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SearchStore] ADD
CONSTRAINT [DF_SearchStore_SearchedAt] DEFAULT (getdate()) FOR [SearchedAt]
GO
CREATE INDEX [pkUSERGUID] ON [dbo].[SearchStore]([UserGUID]) ON [PRIMARY]
GO
-----
RECORDS:
11652IORESI73756002065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26 09:53:17.237
12207IORITC
73755872065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
13208IORYLM
73751692065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
143389IOMINA
73755882065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
15213IOALMA
73750872065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
16651IORYAC
73756712065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
174578IOBASH
73755452065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
184573IODARM
73755052065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
194579IOALQA
73750062065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
20206IOBURJ
73751102065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
21209IOLEMJ
73751502065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
22647IOBABV
73756382065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
23210IOJUBC
73751432065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
24205IOJUMB
73753362065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
253418IOGHYT
73756062065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
263422IOFMDX
73756702065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
274731IOOASB
73753062065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
284732IOSJUM
73755692065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
294724IODXMB
73751602065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
304730IOMMIN
73752512065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
314730IOMMIN
73752512065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
324726IOGHDB
73752632065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
334809IOMADI
73753472065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
344725IOHILJ
73757072065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
354611IOGROS
73750862065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
364727IOHATA
73749942065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
374729IOJEBA
73750122065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
384728IOHYDX
73756812065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
> I am trying to insert 28 UNIQUE records in it, but am getting a primary
> key
> violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
> there any restirction on how many fields a composite primary key can
> include?
The maximum number of key columns is 16.
> It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can
> see
> the records are unique by the 'Records' field
The sample data does not match the table schema you posted. Can you post
INSERT statements that reproduce the problem?
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:08646BEE-6A42-483A-84C9-DD2988F2EC5F@.microsoft.com...
> SQL 2000 Sp4.
> I have a table (SearchStore) with has a composite primary key across all
> fields.
> I am trying to insert 28 UNIQUE records in it, but am getting a primary
> key
> violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
> there any restirction on how many fields a composite primary key can
> include?
> It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can
> see
> the records are unique by the 'Records' field
> Below is the create statment and the Records, any ideas?
>
> ----
> CREATE TABLE [dbo].[SearchStore] (
> [Record] [int] NOT NULL ,
> [WorldTwoTier_WorldResDestCode] [nvarchar] (278) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [WorldTwoTier_HotelID] [int] NOT NULL ,
> [WorldTwoTier_HotelCode] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [ImageUsedID] [int] NOT NULL ,
> [SearchedAt] [datetime] NOT NULL ,
> [UserGUID] [uniqueidentifier] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SearchStore] WITH NOCHECK ADD
> CONSTRAINT [PK_SearchStore] PRIMARY KEY CLUSTERED
> (
> [Record],
> [WorldTwoTier_WorldResDestCode],
> [WorldTwoTier_HotelID],
> [WorldTwoTier_HotelCode],
> [ImageUsedID],
> [SearchedAt],
> [UserGUID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SearchStore] ADD
> CONSTRAINT [DF_SearchStore_SearchedAt] DEFAULT (getdate()) FOR
> [SearchedAt]
> GO
> CREATE INDEX [pkUSERGUID] ON [dbo].[SearchStore]([UserGUID]) ON [PRIMARY]
> GO
> -----
> RECORDS:
> ----
> 11 652 IORESI 7375600 2065943A-68FE-4BA1-A6E7-B5D901AFE50D
> 248xxITC_PK_ISxxDubai 2008-02-26 09:53:17.237
> 12 207 IORITC
> 7375587 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 13 208 IORYLM
> 7375169 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 14 3389 IOMINA
> 7375588 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 15 213 IOALMA
> 7375087 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 16 651 IORYAC
> 7375671 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 17 4578 IOBASH
> 7375545 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 18 4573 IODARM
> 7375505 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 19 4579 IOALQA
> 7375006 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 20 206 IOBURJ
> 7375110 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 21 209 IOLEMJ
> 7375150 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 22 647 IOBABV
> 7375638 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 23 210 IOJUBC
> 7375143 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 24 205 IOJUMB
> 7375336 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 25 3418 IOGHYT
> 7375606 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 26 3422 IOFMDX
> 7375670 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 27 4731 IOOASB
> 7375306 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 28 4732 IOSJUM
> 7375569 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 29 4724 IODXMB
> 7375160 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 30 4730 IOMMIN
> 7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 31 4730 IOMMIN
> 7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 32 4726 IOGHDB
> 7375263 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 33 4809 IOMADI
> 7375347 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 34 4725 IOHILJ
> 7375707 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 35 4611 IOGROS
> 7375086 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 36 4727 IOHATA
> 7374994 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 37 4729 IOJEBA
> 7375012 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 38 4728 IOHYDX
> 7375681 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> ----
>
>
|||Your sample data is messed up. It looks like the row with 4730, 'IOMMIN',
7375251 ... seems to be duplicated and your key constraints may be violated.
Anith
I have a table (SearchStore) with has a composite primary key across all
fields.
I am trying to insert 28 UNIQUE records in it, but am getting a primary key
violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
there any restirction on how many fields a composite primary key can include?
It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can see
the records are unique by the 'Records' field
Below is the create statment and the Records, any ideas?
CREATE TABLE [dbo].[SearchStore] (
[Record] [int] NOT NULL ,
[WorldTwoTier_WorldResDestCode] [nvarchar] (278) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WorldTwoTier_HotelID] [int] NOT NULL ,
[WorldTwoTier_HotelCode] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[ImageUsedID] [int] NOT NULL ,
[SearchedAt] [datetime] NOT NULL ,
[UserGUID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SearchStore] WITH NOCHECK ADD
CONSTRAINT [PK_SearchStore] PRIMARY KEY CLUSTERED
(
[Record],
[WorldTwoTier_WorldResDestCode],
[WorldTwoTier_HotelID],
[WorldTwoTier_HotelCode],
[ImageUsedID],
[SearchedAt],
[UserGUID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SearchStore] ADD
CONSTRAINT [DF_SearchStore_SearchedAt] DEFAULT (getdate()) FOR [SearchedAt]
GO
CREATE INDEX [pkUSERGUID] ON [dbo].[SearchStore]([UserGUID]) ON [PRIMARY]
GO
-----
RECORDS:
11652IORESI73756002065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26 09:53:17.237
12207IORITC
73755872065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
13208IORYLM
73751692065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
143389IOMINA
73755882065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
15213IOALMA
73750872065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
16651IORYAC
73756712065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
174578IOBASH
73755452065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
184573IODARM
73755052065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
194579IOALQA
73750062065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
20206IOBURJ
73751102065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
21209IOLEMJ
73751502065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
22647IOBABV
73756382065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
23210IOJUBC
73751432065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
24205IOJUMB
73753362065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
253418IOGHYT
73756062065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
263422IOFMDX
73756702065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
274731IOOASB
73753062065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
284732IOSJUM
73755692065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
294724IODXMB
73751602065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
304730IOMMIN
73752512065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
314730IOMMIN
73752512065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
324726IOGHDB
73752632065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
334809IOMADI
73753472065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
344725IOHILJ
73757072065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
354611IOGROS
73750862065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
364727IOHATA
73749942065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
374729IOJEBA
73750122065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
384728IOHYDX
73756812065943A-68FE-4BA1-A6E7-B5D901AFE50D248xxITC_PK_ISxxDubai2008-02-26
09:53:17.237
> I am trying to insert 28 UNIQUE records in it, but am getting a primary
> key
> violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
> there any restirction on how many fields a composite primary key can
> include?
The maximum number of key columns is 16.
> It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can
> see
> the records are unique by the 'Records' field
The sample data does not match the table schema you posted. Can you post
INSERT statements that reproduce the problem?
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:08646BEE-6A42-483A-84C9-DD2988F2EC5F@.microsoft.com...
> SQL 2000 Sp4.
> I have a table (SearchStore) with has a composite primary key across all
> fields.
> I am trying to insert 28 UNIQUE records in it, but am getting a primary
> key
> violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
> there any restirction on how many fields a composite primary key can
> include?
> It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can
> see
> the records are unique by the 'Records' field
> Below is the create statment and the Records, any ideas?
>
> ----
> CREATE TABLE [dbo].[SearchStore] (
> [Record] [int] NOT NULL ,
> [WorldTwoTier_WorldResDestCode] [nvarchar] (278) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [WorldTwoTier_HotelID] [int] NOT NULL ,
> [WorldTwoTier_HotelCode] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [ImageUsedID] [int] NOT NULL ,
> [SearchedAt] [datetime] NOT NULL ,
> [UserGUID] [uniqueidentifier] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SearchStore] WITH NOCHECK ADD
> CONSTRAINT [PK_SearchStore] PRIMARY KEY CLUSTERED
> (
> [Record],
> [WorldTwoTier_WorldResDestCode],
> [WorldTwoTier_HotelID],
> [WorldTwoTier_HotelCode],
> [ImageUsedID],
> [SearchedAt],
> [UserGUID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SearchStore] ADD
> CONSTRAINT [DF_SearchStore_SearchedAt] DEFAULT (getdate()) FOR
> [SearchedAt]
> GO
> CREATE INDEX [pkUSERGUID] ON [dbo].[SearchStore]([UserGUID]) ON [PRIMARY]
> GO
> -----
> RECORDS:
> ----
> 11 652 IORESI 7375600 2065943A-68FE-4BA1-A6E7-B5D901AFE50D
> 248xxITC_PK_ISxxDubai 2008-02-26 09:53:17.237
> 12 207 IORITC
> 7375587 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 13 208 IORYLM
> 7375169 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 14 3389 IOMINA
> 7375588 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 15 213 IOALMA
> 7375087 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 16 651 IORYAC
> 7375671 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 17 4578 IOBASH
> 7375545 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 18 4573 IODARM
> 7375505 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 19 4579 IOALQA
> 7375006 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 20 206 IOBURJ
> 7375110 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 21 209 IOLEMJ
> 7375150 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 22 647 IOBABV
> 7375638 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 23 210 IOJUBC
> 7375143 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 24 205 IOJUMB
> 7375336 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 25 3418 IOGHYT
> 7375606 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 26 3422 IOFMDX
> 7375670 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 27 4731 IOOASB
> 7375306 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 28 4732 IOSJUM
> 7375569 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 29 4724 IODXMB
> 7375160 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 30 4730 IOMMIN
> 7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 31 4730 IOMMIN
> 7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 32 4726 IOGHDB
> 7375263 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 33 4809 IOMADI
> 7375347 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 34 4725 IOHILJ
> 7375707 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 35 4611 IOGROS
> 7375086 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 36 4727 IOHATA
> 7374994 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 37 4729 IOJEBA
> 7375012 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 38 4728 IOHYDX
> 7375681 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> ----
>
>
|||Your sample data is messed up. It looks like the row with 4730, 'IOMMIN',
7375251 ... seems to be duplicated and your key constraints may be violated.
Anith
Primary Key Violation Error
SQL 2000 Sp4.
I have a table (SearchStore) with has a composite primary key across all
fields.
I am trying to insert 28 UNIQUE records in it, but am getting a primary key
violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
there any restirction on how many fields a composite primary key can include?
It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can see
the records are unique by the 'Records' field
Below is the create statment and the Records, any ideas?
----
CREATE TABLE [dbo].[SearchStore] (
[Record] [int] NOT NULL ,
[WorldTwoTier_WorldResDestCode] [nvarchar] (278) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WorldTwoTier_HotelID] [int] NOT NULL ,
[WorldTwoTier_HotelCode] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[ImageUsedID] [int] NOT NULL ,
[SearchedAt] [datetime] NOT NULL ,
[UserGUID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SearchStore] WITH NOCHECK ADD
CONSTRAINT [PK_SearchStore] PRIMARY KEY CLUSTERED
(
[Record],
[WorldTwoTier_WorldResDestCode],
[WorldTwoTier_HotelID],
[WorldTwoTier_HotelCode],
[ImageUsedID],
[SearchedAt],
[UserGUID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SearchStore] ADD
CONSTRAINT [DF_SearchStore_SearchedAt] DEFAULT (getdate()) FOR [SearchedAt]
GO
CREATE INDEX [pkUSERGUID] ON [dbo].[SearchStore]([UserGUID]) ON [PRIMARY]
G
-----
RECORDS
----
11 652 IORESI 7375600 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26 09:53:17.237
12 207 IORITC
7375587 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
13 208 IORYLM
7375169 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
14 3389 IOMINA
7375588 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
15 213 IOALMA
7375087 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
16 651 IORYAC
7375671 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
17 4578 IOBASH
7375545 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
18 4573 IODARM
7375505 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
19 4579 IOALQA
7375006 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
20 206 IOBURJ
7375110 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
21 209 IOLEMJ
7375150 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
22 647 IOBABV
7375638 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
23 210 IOJUBC
7375143 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
24 205 IOJUMB
7375336 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
25 3418 IOGHYT
7375606 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
26 3422 IOFMDX
7375670 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
27 4731 IOOASB
7375306 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
28 4732 IOSJUM
7375569 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
29 4724 IODXMB
7375160 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
30 4730 IOMMIN
7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
31 4730 IOMMIN
7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
32 4726 IOGHDB
7375263 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
33 4809 IOMADI
7375347 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
34 4725 IOHILJ
7375707 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
35 4611 IOGROS
7375086 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
36 4727 IOHATA
7374994 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
37 4729 IOJEBA
7375012 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
38 4728 IOHYDX
7375681 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
----> I am trying to insert 28 UNIQUE records in it, but am getting a primary
> key
> violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
> there any restirction on how many fields a composite primary key can
> include?
The maximum number of key columns is 16.
> It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can
> see
> the records are unique by the 'Records' field
The sample data does not match the table schema you posted. Can you post
INSERT statements that reproduce the problem?
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:08646BEE-6A42-483A-84C9-DD2988F2EC5F@.microsoft.com...
> SQL 2000 Sp4.
> I have a table (SearchStore) with has a composite primary key across all
> fields.
> I am trying to insert 28 UNIQUE records in it, but am getting a primary
> key
> violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
> there any restirction on how many fields a composite primary key can
> include?
> It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can
> see
> the records are unique by the 'Records' field
> Below is the create statment and the Records, any ideas?
>
> ----
> CREATE TABLE [dbo].[SearchStore] (
> [Record] [int] NOT NULL ,
> [WorldTwoTier_WorldResDestCode] [nvarchar] (278) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [WorldTwoTier_HotelID] [int] NOT NULL ,
> [WorldTwoTier_HotelCode] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [ImageUsedID] [int] NOT NULL ,
> [SearchedAt] [datetime] NOT NULL ,
> [UserGUID] [uniqueidentifier] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SearchStore] WITH NOCHECK ADD
> CONSTRAINT [PK_SearchStore] PRIMARY KEY CLUSTERED
> (
> [Record],
> [WorldTwoTier_WorldResDestCode],
> [WorldTwoTier_HotelID],
> [WorldTwoTier_HotelCode],
> [ImageUsedID],
> [SearchedAt],
> [UserGUID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SearchStore] ADD
> CONSTRAINT [DF_SearchStore_SearchedAt] DEFAULT (getdate()) FOR
> [SearchedAt]
> GO
> CREATE INDEX [pkUSERGUID] ON [dbo].[SearchStore]([UserGUID]) ON [PRIMARY]
> GO
> -----
> RECORDS:
> ----
> 11 652 IORESI 7375600 2065943A-68FE-4BA1-A6E7-B5D901AFE50D
> 248xxITC_PK_ISxxDubai 2008-02-26 09:53:17.237
> 12 207 IORITC
> 7375587 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 13 208 IORYLM
> 7375169 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 14 3389 IOMINA
> 7375588 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 15 213 IOALMA
> 7375087 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 16 651 IORYAC
> 7375671 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 17 4578 IOBASH
> 7375545 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 18 4573 IODARM
> 7375505 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 19 4579 IOALQA
> 7375006 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 20 206 IOBURJ
> 7375110 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 21 209 IOLEMJ
> 7375150 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 22 647 IOBABV
> 7375638 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 23 210 IOJUBC
> 7375143 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 24 205 IOJUMB
> 7375336 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 25 3418 IOGHYT
> 7375606 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 26 3422 IOFMDX
> 7375670 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 27 4731 IOOASB
> 7375306 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 28 4732 IOSJUM
> 7375569 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 29 4724 IODXMB
> 7375160 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 30 4730 IOMMIN
> 7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 31 4730 IOMMIN
> 7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 32 4726 IOGHDB
> 7375263 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 33 4809 IOMADI
> 7375347 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 34 4725 IOHILJ
> 7375707 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 35 4611 IOGROS
> 7375086 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 36 4727 IOHATA
> 7374994 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 37 4729 IOJEBA
> 7375012 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 38 4728 IOHYDX
> 7375681 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> ----
>
>|||Your sample data is messed up. It looks like the row with 4730, 'IOMMIN',
7375251 ... seems to be duplicated and your key constraints may be violated.
--
Anith
I have a table (SearchStore) with has a composite primary key across all
fields.
I am trying to insert 28 UNIQUE records in it, but am getting a primary key
violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
there any restirction on how many fields a composite primary key can include?
It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can see
the records are unique by the 'Records' field
Below is the create statment and the Records, any ideas?
----
CREATE TABLE [dbo].[SearchStore] (
[Record] [int] NOT NULL ,
[WorldTwoTier_WorldResDestCode] [nvarchar] (278) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[WorldTwoTier_HotelID] [int] NOT NULL ,
[WorldTwoTier_HotelCode] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[ImageUsedID] [int] NOT NULL ,
[SearchedAt] [datetime] NOT NULL ,
[UserGUID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SearchStore] WITH NOCHECK ADD
CONSTRAINT [PK_SearchStore] PRIMARY KEY CLUSTERED
(
[Record],
[WorldTwoTier_WorldResDestCode],
[WorldTwoTier_HotelID],
[WorldTwoTier_HotelCode],
[ImageUsedID],
[SearchedAt],
[UserGUID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SearchStore] ADD
CONSTRAINT [DF_SearchStore_SearchedAt] DEFAULT (getdate()) FOR [SearchedAt]
GO
CREATE INDEX [pkUSERGUID] ON [dbo].[SearchStore]([UserGUID]) ON [PRIMARY]
G
-----
RECORDS
----
11 652 IORESI 7375600 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26 09:53:17.237
12 207 IORITC
7375587 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
13 208 IORYLM
7375169 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
14 3389 IOMINA
7375588 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
15 213 IOALMA
7375087 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
16 651 IORYAC
7375671 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
17 4578 IOBASH
7375545 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
18 4573 IODARM
7375505 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
19 4579 IOALQA
7375006 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
20 206 IOBURJ
7375110 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
21 209 IOLEMJ
7375150 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
22 647 IOBABV
7375638 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
23 210 IOJUBC
7375143 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
24 205 IOJUMB
7375336 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
25 3418 IOGHYT
7375606 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
26 3422 IOFMDX
7375670 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
27 4731 IOOASB
7375306 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
28 4732 IOSJUM
7375569 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
29 4724 IODXMB
7375160 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
30 4730 IOMMIN
7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
31 4730 IOMMIN
7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
32 4726 IOGHDB
7375263 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
33 4809 IOMADI
7375347 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
34 4725 IOHILJ
7375707 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
35 4611 IOGROS
7375086 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
36 4727 IOHATA
7374994 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
37 4729 IOJEBA
7375012 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
38 4728 IOHYDX
7375681 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai 2008-02-26
09:53:17.237
----> I am trying to insert 28 UNIQUE records in it, but am getting a primary
> key
> violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
> there any restirction on how many fields a composite primary key can
> include?
The maximum number of key columns is 16.
> It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can
> see
> the records are unique by the 'Records' field
The sample data does not match the table schema you posted. Can you post
INSERT statements that reproduce the problem?
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:08646BEE-6A42-483A-84C9-DD2988F2EC5F@.microsoft.com...
> SQL 2000 Sp4.
> I have a table (SearchStore) with has a composite primary key across all
> fields.
> I am trying to insert 28 UNIQUE records in it, but am getting a primary
> key
> violation error (Violation of PRIMARY KEY constraint 'PK_SearchStore'). Is
> there any restirction on how many fields a composite primary key can
> include?
> It fail on rows with 'Record' 20 & 21, and also 29 & 30, but as you can
> see
> the records are unique by the 'Records' field
> Below is the create statment and the Records, any ideas?
>
> ----
> CREATE TABLE [dbo].[SearchStore] (
> [Record] [int] NOT NULL ,
> [WorldTwoTier_WorldResDestCode] [nvarchar] (278) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [WorldTwoTier_HotelID] [int] NOT NULL ,
> [WorldTwoTier_HotelCode] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [ImageUsedID] [int] NOT NULL ,
> [SearchedAt] [datetime] NOT NULL ,
> [UserGUID] [uniqueidentifier] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SearchStore] WITH NOCHECK ADD
> CONSTRAINT [PK_SearchStore] PRIMARY KEY CLUSTERED
> (
> [Record],
> [WorldTwoTier_WorldResDestCode],
> [WorldTwoTier_HotelID],
> [WorldTwoTier_HotelCode],
> [ImageUsedID],
> [SearchedAt],
> [UserGUID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SearchStore] ADD
> CONSTRAINT [DF_SearchStore_SearchedAt] DEFAULT (getdate()) FOR
> [SearchedAt]
> GO
> CREATE INDEX [pkUSERGUID] ON [dbo].[SearchStore]([UserGUID]) ON [PRIMARY]
> GO
> -----
> RECORDS:
> ----
> 11 652 IORESI 7375600 2065943A-68FE-4BA1-A6E7-B5D901AFE50D
> 248xxITC_PK_ISxxDubai 2008-02-26 09:53:17.237
> 12 207 IORITC
> 7375587 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 13 208 IORYLM
> 7375169 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 14 3389 IOMINA
> 7375588 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 15 213 IOALMA
> 7375087 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 16 651 IORYAC
> 7375671 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 17 4578 IOBASH
> 7375545 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 18 4573 IODARM
> 7375505 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 19 4579 IOALQA
> 7375006 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 20 206 IOBURJ
> 7375110 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 21 209 IOLEMJ
> 7375150 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 22 647 IOBABV
> 7375638 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 23 210 IOJUBC
> 7375143 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 24 205 IOJUMB
> 7375336 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 25 3418 IOGHYT
> 7375606 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 26 3422 IOFMDX
> 7375670 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 27 4731 IOOASB
> 7375306 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 28 4732 IOSJUM
> 7375569 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 29 4724 IODXMB
> 7375160 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 30 4730 IOMMIN
> 7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 31 4730 IOMMIN
> 7375251 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 32 4726 IOGHDB
> 7375263 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 33 4809 IOMADI
> 7375347 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 34 4725 IOHILJ
> 7375707 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 35 4611 IOGROS
> 7375086 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 36 4727 IOHATA
> 7374994 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 37 4729 IOJEBA
> 7375012 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> 38 4728 IOHYDX
> 7375681 2065943A-68FE-4BA1-A6E7-B5D901AFE50D 248xxITC_PK_ISxxDubai
> 2008-02-26
> 09:53:17.237
> ----
>
>|||Your sample data is messed up. It looks like the row with 4730, 'IOMMIN',
7375251 ... seems to be duplicated and your key constraints may be violated.
--
Anith
Subscribe to:
Posts (Atom)