MS SQL Server :: Table With Duplicate Entry With Primary Keys

Duplicate Entry In A Primary Key Field
Hi everybody couldn't get through with saving my data on the table with two primary keys...

my table structure is this

pubidintUnchecked (primary key)
pubchar(1)Unchecked
publchar(1)Unchecked
pubcodechar(2)Unchecked (primary key)

a sample data is here

pubid pub publ pubcode

1 a b ab
1 b b bb
2 a b ab
2 b b bb

when i save this table modifying the pubid and pubcode as primary keys the following error displays...

Unable to create index 'PK_PUBS3'.
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '51'.
Could not create constraint. See previous errors.
The statement has been terminated.

what i understand is that on the primary key duplicates are not allowed how could i allow it?

thanks

View Replies !

BCP And Duplicate Primary Keys
Hi All,
I`m using BCP to import ASCII data text into a table that already has many records. BCP failed because of `Duplicate primary key`.
Now, is there any way using BCP to know precisely which record whose primary key caused that `violation of inserting duplicate key`.
I already used the option -O to output error to a `error.log`, but it doesn`t help much, because that error log contains the same error message mentioned above without telling me exactly which record so that I can pull that `duplicate record` out of my import data file.
TIA and you have a great day.
David Nguyen.

View Replies !

Ignore Duplicate Primary Keys
I have one table that stores log messages generated by a web service. I have a second table where I want to store just the distinct messages from the first table. This second table has two columns one for the message and the second for the checksum of the message. The checksum column is the primary key for the table.

My query for populating the second table looks like:
INSERT INTO TransactionMessages ( message, messageHash )
SELECT DISTINCT message, CHECKSUM( message )
FROM Log
WHERE logDate BETWEEN '2008-03-26 00:00:00' AND '2008-03-26 23:59:59'
AND NOT EXISTS (
SELECT * FROM TransactionMessages
WHERE messageHash = CHECKSUM( Log.message ) )

I run this query once per day to insert the new messages from the day before. It fails when a day has two messages that have the same checksum. In this case I would like to ignore the second message and let the query proceed. I tried creating an instead of insert trigger that only inserted unique primary keys. The trigger looks like:

IF( NOT EXISTS(
SELECT TM.messageHash
FROM TransactionMessages TM, inserted I
WHERE TM.messageHash = I.messageHash
) )
BEGIN
INSERT INTO TransactionMessages ( messageHash, message )
SELECT messageHash, message FROM inserted
END

That didn't work. I think the issue is that all the rows get committed to the table at the end of the whole query. That means the trigger cannot match the duplicate primary key because the initial row has not been inserted yet.

Does anyone know the best way to do this?

Thanks for your help,
Drew

View Replies !

Duplicate Primary Keys In Input File
'm trying to import a text file but the primary key column contains duplicatres (tunrs out to be the nature of the legacy data). How can I kick out all duplicates except, say, for a single primary key value?

TIA,

Barkingdog

View Replies !

Creating Inter-table Relationships Using Primary Keys/Foreign Keys Problem
Hello again,

I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.

Here's the query for my Classes table:

Code:

CREATE TABLE Classes
(
class_id
INT
IDENTITY
PRIMARY KEY
NOT NULL,

teacher_id
INT
NOT NULL,

class_title
VARCHAR(50)
NOT NULL,

class_grade
SMALLINT
NOT NULL
DEFAULT 6,

class_tardies
SMALLINT
NOT NULL
DEFAULT 0,

class_absences
SMALLINT
NOT NULL
DEFAULT 0,

CONSTRAINT Teacher_instructs_ClassFKIndex1 FOREIGN KEY (teacher_id)
REFERENCES Users (user_id)
)

This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:

Code:

CREATE TABLE Classes_have_Grades
(
class_id
INT
PRIMARY KEY
NOT NULL,

teacher_id
INT
NOT NULL,

grade_id
INT
NOT NULL,

CONSTRAINT Grades_for_ClassesFKIndex1 FOREIGN KEY (grade_id)
REFERENCES Grades (grade_id),

CONSTRAINT Classes_have_gradesFKIndex2 FOREIGN KEY (class_id, teacher_id)
REFERENCES Classes (class_id, teacher_id)
)

Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?

In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?

Thank you for your assistance.

View Replies !

Auto Incremented Integer Primary Keys Vs Varchar Primary Keys
Hi,

I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.

For example:

id [unique integer auto incremented primary key - not null],
ClientCode [unique index varchar - not null],
name [varchar null],
surname [varchar null]

isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.

Regards
Mike

View Replies !

Preventing Duplicate Entry For A Given Foreign Key In Db Table
Hi,i am using SQL server 2005 and have a table with 4 columns.Column1 is primary key,col2 is foreign key and col3 and col4 are regular data column.When the user enters the data i want to make sure that for a given foreign key(col2),entries in col3 are not duplicated.Is there a way,i can make sure this at db level,using some kind of constraints or something?Thanks a bunch..

View Replies !

Script For Creating Table With Multiple Primary Keys
Hi,

I am trying to execute following sql script in sql-server 2000 query analyzer

CREATE TABLE user_courses (user_id varchar(30) NOT NULL PRIMARY KEY,
course_id varchar(10) NOT NULL PRIMARY KEY)

Its give's me following error :-
Cannot specify multiple primary key constraint

Hence I am not able to ceate table with multiple primary keys. So can any one tell me how to get this done?.

Secondly, Primary key must be unique i.e duplicate values are not allowed in P.K field. But in this case since I am declaring two fileds as primary keys.
Will it allow me to have following records in the user_courses table?
user_id(P.K) course_id(P.K)
bob CRS235
alice CRS235
Tim CRS235
tom CRS635

So, if we consider both the fields as primary keys together than I am not voilating Uniqueness constraint. But, if I look at course_id alone then I am voilating uniqeness property?

Thanks,

View Replies !

Generate Script For Primary Keys And Foreing Keys

Pls let me know How I generate script for All primary keys and foreign keys in a table. Thereafter that can be used to add primary keys and foreign keys in another databse with same structure.

Also how I script default and other constraints of a table?

View Replies !

Urgent !!!!! Nee Explanation On Primary Keys And FK Keys
Can somebody explain to me how to best do inserts where you have primary keys and foreign keys.l'm battling.

Is there an article on primary keys/Pk ?

View Replies !

Import Csv Data To Dbo.Tables Via CREATE TABLE && BUKL INSERT:How To Designate The Primary-Foreign Keys && Set Up Relationship?
Hi all,

I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations:

-- ImportCSVprojects.sql --

USE ChemDatabase

GO

CREATE TABLE Projects

(

ProjectID int,

ProjectName nvarchar(25),

LabName nvarchar(25)

);

BULK INSERT dbo.Projects

FROM 'c:myfileProjects.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO
=======================================
-- ImportCSVsamples.sql --

USE ChemDatabase

GO

CREATE TABLE Samples

(

SampleID int,

SampleName nvarchar(25),

Matrix nvarchar(25),

SampleType nvarchar(25),

ChemGroup nvarchar(25),

ProjectID int

);

BULK INSERT dbo.Samples

FROM 'c:myfileSamples.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO
=========================================
-- ImportCSVtestResult.sql --

USE ChemDatabase

GO

CREATE TABLE TestResults

(

AnalyteID int,

AnalyteName nvarchar(25),

Result decimal(9,3),

UnitForConc nvarchar(25),

SampleID int

);

BULK INSERT dbo.TestResults

FROM 'c:myfileLabTests.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO

========================================
The 3 csv files were successfully imported into the ChemDatabase of my SSMSE.

2 questions to ask:
(1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables?
Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period?
(2) How can I set up the relationships among these 3 dbo Tables?

Please help and advise.

Thanks in advance,
Scott Chang

View Replies !

Duplicate Entry
Hi All,

I have a table with name C1_Subscribers with three fields (1)-MobileNumber [varchar]
(2)-ReceivedTime [datetime]
(3)-Status [char].
Now here how to remove duplicate entry of same mobile number to MobileNumber field?

Regards
Shaji

View Replies !

How Duplicate Data Can Make An Entry????
Hi,
I want to know the different sources, mediums and ways using which the duplicate recoprds or adta can make an entry in our database tables. I found 4 ways for this from many articles on net that are as follows:

Duplicate data might arrive at your database via an interface to another system
Data is loaded into table from other sources because during data loads, the integrity constraints are disabled
Merging data from disparate systems
Inheriting a poorly designed databaseBut is there any other ways also present????? Please tell me???

Thanks,

View Replies !

Removing Duplicate Rows With CTE And Partition - Need Most Recent Entry
I have tested the code below to remove duplicate table entries based on the field IntOOS. This code works; however, I want to ensure I am removing the oldest entries and keeping the most recent entry. The field to key on this would be something like Max(ID). What would be the best way to ensure I keep the most recent table entry?

/*** Removes duplicate rows from ampfm.rpt_AdtVisitDiag table

by Chuck King 20070928

***/

;WITH CTE

as

(

SELECT

ROW_NUMBER() OVER (Partition By IntOOS Order BY IntOOS) AS ROWID

,DischDate

,AdmDiagCode

,ID

,AdmDiagCodeDesc

,AdmittingDiagnosis

,AnyDx

,DischDx

,ECode

,IntOOS

,PrinDxAnesDesc

,PrinDxAnesInd

,PrinDxCode

,PrinDxCondDesc

,PrinDxCondInd

,PrinDxDesc

,PrinDxEqualsAdmDxYnu

,SecDx10AnesDesc

,SecDx10AnesInd

,SecDx10Code

,SecDx10CondDesc

,SecDx10CondInd

,SecDx10Description

,SecDx11AnesDesc

,SecDx11AnesInd

,SecDx11Code

,SecDx11CondDesc

,SecDx11CondInd

,SecDx11Description

,SecDx12AnesDesc

,SecDx12AnesInd

,SecDx12Code

,SecDx12CondDesc

,SecDx12CondInd

,SecDx12Description

,SecDx13AnesDesc

,SecDx13AnesInd

,SecDx13Code

,SecDx13CondDesc

,SecDx13CondInd

,SecDx13Description

,SecDx14AnesDesc

,SecDx14AnesInd

,SecDx14Code

,SecDx14CondDesc

,SecDx14CondInd

,SecDx14Description

,SecDx15Code

,SecDx15Description

,SecDx1AnesDesc

,SecDx1AnesInd

,SecDx1Code

,SecDx1CondDesc

,SecDx1CondInd

,SecDx1Description

,SecDx2AnesDesc

,SecDx2AnesInd

,SecDx2Code

,SecDx2CondDesc

,SecDx2CondInd

,SecDx2Description

,SecDx3AnesDesc

,SecDx3AnesInd

,SecDx3Code

,SecDx3CondDesc

,SecDx3CondInd

,SecDx3Description

,SecDx4AnesDesc

,SecDx4AnesInd

,SecDx4Code

,SecDx4CondDesc

,SecDx4CondInd

,SecDx4Description

,SecDx5AnesDesc

,SecDx5AnesInd

,SecDx5Code

,SecDx5CondDesc

,SecDx5CondInd

,SecDx5Description

,SecDx6AnesDesc

,SecDx6AnesInd

,SecDx6Code

,SecDx6CondDesc

,SecDx6CondInd

,SecDx6Description

,SecDx7AnesDesc

,SecDx7AnesInd

,SecDx7Code

,SecDx7CondDesc

,SecDx7CondInd

,SecDx7Description

,SecDx8AnesDesc

,SecDx8AnesInd

,SecDx8Code

,SecDx8CondDesc

,SecDx8CondInd

,SecDx8Description

,SecDx9AnesDesc

,SecDx9AnesInd

,SecDx9Code

,SecDx9CondDesc

,SecDx9CondInd

,SecDx9Description

,VisitTypeCode

,accountnumber

,DischVisitTypeCode

FROM ampfm.rpt_AdtVisitDiag

)

--Select * From CTE

Delete From CTE Where ROWID > 1

View Replies !

How To Prevent A Second Entry Being Inserted With Primary Key Value? (C#)
Ok, this is a really stupid question, but I can't seem to find an answer I understand. In my SQL database I have a a table called MasterSkillList, to which the user can write by using a little web form with a text box and a drop down list. The table has 2 fields, Skill and Attribute. Skill is the primary key, as no skill can appear twice. What I want to do is prevent just that, I don't want people to enter the same skill more than once. So how do I tell the user that the entry allready exists in the database?
My C# Code is as follows:1 protected void btnSubmit_Click(object sender, EventArgs e)
2 {
3 srcAddSkill.InsertParameters["Skill"].DefaultValue = txtSkillName.Text;
4 srcAddSkill.InsertParameters["Attribute"].DefaultValue = ddlAbility.SelectedValue;
5 try
6 {
7 srcAddSkill.Insert();
8 lblErrorMessage.Text = "The skill '" + txtSkillName.Text + "' has been added. It is based on a character's " + ddlAbility.SelectedItem + " score.";
9 lblErrorMessage.Visible = true;
10 txtSkillName.Text = "";
11 }
12 catch (Exception ex)
13 {
14 lblErrorMessage.Text = "An exception has occurred. " + ex.Message;
15 lblErrorMessage.Visible = true;
16 }

View Replies !

Insert From Formview And Checking Database To Avoid A Duplicate Entry
I have a form view that I am using to insert new data into a sql express database and would like to find a way to avoid attempting to insert a record if the key already exists. is there a way to do this with the formview insert command. Everything works great until I try to add a record with an already existing value in the unique key field, then it breaks.

View Replies !

Duplicate Data Entry When Using The ASP.NET Web Site Administration Tool For Logins
Hello All,

I am using the Web Site Administration Tool with the ASP.NET login controls to create/manage user logins & roles for my site.

I have exported the ASPNETDB.MDF into my SQL Database ready to use with my app. The problem that I'm having is that whenever I add a user, using either the Web Site Administration Tool or the Classes available in the code-behind. The entry is doubled up in the database.

This is not a problem when logging on....however, when it comes to deleting a user it will only take one of the entries out and leave the duplicate in there. So if the administrator has deleted a user from the app and then tried to create another one with the same UserName (Which whomever is well within his/her rights to be able to do), they cannot because the entry which has "stay behind" in the database conflicts with the entry.

Any help on this matter would be greatly apprieciated. If any more info on this matter is required please ask (It was quite hard to try and explain this :-)

View Replies !

Problem With Duplicate Keys
Hello,There is a program which performs some scripted actions via ODBC on tablesin some database on mssql 2000. Sometimes that program tries to insertrecord with key that is already present in the database. The error comes upand the program stops.Is there any way to globally configure the database or the whole mssqlserver to ignore such attempts and let the script continue without any errorwhen the script tries to insert duplicate-key records?Thank you for any suggestions.Pawel Banys

View Replies !

Duplicate Foreign Keys.
Hi all,

SQL server allows to create as many as foreign key constraints on a same table for a same column.

Will this affect the design or performance in anyway ?

Naming the constraint would be a good way to avoid this.But in case if someone has already created, How do I remove the existing duplicate keys ?

======================
For Example , I have 2 tables Author and Book. I could execute the below query n times and create as many as foreign keys I want.

ALTER TABLE Books
ADD
FOREIGN KEY (AuthorID)
REFERENCES Authors (AuthorID)

======================

Thanks is advance,

DBAnalyst

View Replies !

Duplicate Keys And Conversion
Hi!
I am new .. very grateful for some advice. How do I eliminate duplicate ref keys Or should I have cache mode PARTIAL?

[Data Conversion [4910]] Error: Data conversion failed while converting column "salesperson_id" (88) to column "Copy of salesperson_id" (4929). The conversion returned status value 6 and status text "Conversion failed because the data value overflowed the specified type.".
[Lookup [3882]] Warning: The Lookup transformation encountered duplicate reference key values when caching reference data. The Lookup transformation found duplicate key values when caching metadata in PreExecute. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.

View Replies !

Importing Data With Duplicate Keys
I'm trying to merge two Access databases into one SQL server database. I have 3 tables that are all related with primary and foreign keys.

When I try to import my second set of 3 tables I get errors about the keys already existing in the database. Is there any way to force SQL server to assign new keys while preserving my existing relationships? Thanks!

View Replies !

Finding Duplicate Foreign Keys
Hi

i tried the following query and able to get the list of foreign keys with column names as well as referred tables and referenced column

select parent_column_id as 'child Column',object_name(constraint_object_id)as 'FK Name',object_name(parent_object_id) as 'parent table',name,object_name(referenced_object_id)as 'referenced table',referenced_column_id
from sys.foreign_key_columns inner join sys.columns on (parent_column_id = column_id and parent_object_id=object_id)
Order by object_name(parent_object_id) asc

but i am not able to get the fks created more than once on same column refering to same pk

Thanks in Advance

View Replies !

Elimenating Duplicate Keys With Unique Row.
I have a large table that consists of the columns zip, state, city, county. The primary
key "zip" has duplicates but the rows are unique.
How do I filter out only the duplicate zips. So in effect I only have one row per unique key.
Randy Garland

if you just want a list of all rows with duplicate zipcodes then ...

SELECT * FROM TableName WHERE zip IN ( SELECT zip FROM TableName
GROUP BY zip HAVING COUNT(*)>1 )

Duncan

Duncan, I tried this but it does not return one row per key.
Randy Garland

View Replies !

Finding Duplicate Entries (with Different Keys)
Yet another simple query that is eluding me. I need to find records in a table that have the same first name and last name. Because the table has a primaty key, these people were entered twice or they share the same first and last name.

How could you query this:

ID fname lname
10001 Bill Jones
10002 Joe Smith
10003 Sue Jenkins
10004 John Sanders
10005 Joe Smith
10006 Harrold Simpson
10007 Sue Jenkins
10008 Sam Worden

and get a result set of this:

ID fname lname
10002 Joe Smith
10005 Joe Smith
10003 Sue Jenkins
10007 Sue Jenkins

View Replies !

Primary Keys
"Violation of PRIMARY KEY of restriction 'PK_Approve_Overtime'. The overlapping key cannot be inserted in object 'Dbo.Approve_Overtime'. The statement was ended."
can soemone explain to me why i have this kind of error?
i have this two tables. approve_overtime table has a primary key id_no and application_input table with a primary key of id_no!
all the values from of application_input will be stored also in approve_overtime.
sometimes the datas can be stored.sometimes it cannot and produces an error!

what do u think?

hmmm pls help!

View Replies !

Primary Keys
Hi everyone,
Does someone knows how can I drop a primary key (that I don't know the name) from a table in one sql statement.

Thanks,
Fady

View Replies !

Primary Keys
HI ,

I accidently removed the primary keys from my table by mistake. Is there anyway ,That i can get the PK's back to what is used to be. Need Help pls...... When I try "resetting" the PK I kep getting this error:

'table_name' table
- Unable to create index 'PK_tablename'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '1'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]Warning: The table 'tDetail' has been created but its maximum row size (12521) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

View Replies !

Two Primary Keys
In access you can have two fields that are primary keys with one or the other repeating as long as the combination is not repeated.
i.e.
key1 key2
200410 12345
200410 12346
200410 12588
etc for all 200410 there can not be a repeat of any value of key 2

is there a way to have this dual key in MSSQL :confused:

View Replies !

Two Primary Keys???
I have read that SQL Server tables can't have more than one primary key. I know in Access two keys are allowed. Why can't there be two primary keys in a single table in SQL Server 7.

Thanks

View Replies !

Primary Keys
Using SQL Svr 7.0. It appears that primary keys are created as nonclustered
unique indexes. Is there a configuration setting I can use to make them be
created as clustered unique indexes?

View Replies !

Primary Keys
If a table has a column defined as Int, Identity(1,1) which is to be used as the primary key, should that index be defined as clustered or non-clustered? In Enterprise manager when you create a PK on a table it defaults to being a clustered index. I am sure the answer depends on the other index requirements and columns in the table but I'd like to see what other ppl think about this.

Thanks!

View Replies !

Primary Keys
I'm hoping this is a simple question and I just haven't been able to find the answer in the help file:
Does anyone know of a limatation on primary keys regarding single column keys and how big they can be? It looks like you can easily define a primary key based on one column, but which would be the best datatype for that column? I'm guessing it won't recycle old deleted keys, so at what point would it be unable to create a new primary key value? Or is this even an issue? 8 )

Thanks!

View Replies !

DTS And Primary Keys
I'm using DTS to import a number of tables. The problem is that some tables have primary keys, but after the import, when I look at the new tables that have been imported, they never have primary keys. Am I doing something wrong? If so, please tell me.

Thanks.

View Replies !

Primary Keys
Hi there,

is there any way I can use INSERT so if I try to copy duplicate primary key data, it automatically skips the task and moves on to the next data?

View Replies !

Primary Keys
I have declared a table with a single column to be a Primary key. If I write 2 records, the first comprsing "A" and the second "a" then I get an exception about violating the uniqueness of the key. Is there a way that I can define the key to be case sensitive.

Many thanks

View Replies !

Help With Primary Keys
I have two tables with similar primary keys, table a and table b, and I want to find out all the key values that are disimilar between the tables. Can this be done with a select? if so what would it be.

View Replies !

Primary Keys
Hi

How can 2 columns be clubbed together to form a primary key , i mean I have 2 columns job & Batch , need to club them together to form the primary key

How is it done I mean in the design , how to define that both the columns togethter form a primary key ,

Cause when I go to the table , it allows me to create a PK through the EM only for one column

Please help

View Replies !

Rest Primary Keys?
HiI have a database and I been inserting some dummy data into it but now I want to upload it to my website but I want to delete all the dummy data and start the PK back at 1. I truancted all the data but it still keeps counting from the last one.So how do I reset it?

View Replies !

Primary, Forgein Keys
I am not entirly sure what a forigen key is, is it a unique ID which is the same as the primary key? If adding a foreign key to a table that already has data, will it update each row with a unique ID or will it only create a unique ID on newly inserted records (for the foreign key)? If the foreign key is the same as the primary key then why do we need to even add a foreign key at all?For example a table like: Table Name : Customers--CustomerID uniqueidentity (primary Key)--FirstName nchar Table Name : Orders --OrderID int--CustomerID int Obviuously CustomerID are going to be the same in both tables, so why would you need to add a foreign key on 'CustomerID' to the 'Order' table, can't SQL match the customerID in each table any way? Cheers

View Replies !

Wide Primary Keys
I'm working on a system that is very address-centric and detection ofduplicate addresses is very important. As a result we have brokenaddresses down into many parts (DDL below, but I've left out somereference tables for conciseness), these being state, locality, street,street number, and address. The breakdown is roughly consistent withAustralian addressing standards, we're working on finalising this.Because we carry the primary key down each of the levels, this hasresulted in our address table having a very wide primary key (around170 characters). We refer to addresses from a number of other tablesand although my instinct is to use this natural key in the other tablesI wonder if we should just put a unique index on the natural key,create a surrogate primary key and use it in the other table. Anythoughts?CREATE TABLE dbo.States (StateID varchar (3) NOT NULL ,StateName varchar (50) NOT NULL ,CONSTRAINT PK_AddressStates PRIMARY KEY NONCLUSTERED(StateID))CREATE TABLE dbo.Localities (Locality varchar (46) NOT NULL ,StateID varchar (3) NOT NULL ,Postcode char (4) NOT NULL ,CONSTRAINT PK_Localities PRIMARY KEY NONCLUSTERED(Locality,StateID,Postcode),CONSTRAINT FK_AddressLocalities_AddressStates FOREIGN KEY(StateID) REFERENCES dbo.States (StateID))CREATE TABLE dbo.Streets (StreetName varchar (35) NOT NULL ,StreetTypeID varchar (10) NOT NULL ,StreetDirectionID varchar (2) NOT NULL ,Locality varchar (46) NOT NULL ,StateID varchar (3) NOT NULL ,Postcode char (4) NOT NULL ,CONSTRAINT PK_Streets PRIMARY KEY CLUSTERED(StreetName,StreetTypeID,StreetDirectionID,Locality,StateID,Postcode),CONSTRAINT FK_Streets_Localities FOREIGN KEY(Postcode,Locality,StateID) REFERENCES dbo.Localities (Postcode,Locality,StateID))CREATE TABLE dbo.StreetNumbers (StreetName varchar (35) NOT NULL ,StreetTypeID varchar (10) NOT NULL ,StreetDirectionID varchar (2) NOT NULL ,Locality varchar (46) NOT NULL ,StateID varchar (3) NOT NULL ,Postcode char (4) NOT NULL ,StreetNumber varchar (15) NOT NULL ,BuildingName varchar (100) NOT NULL ,CONSTRAINT PK_StreetNumbers PRIMARY KEY CLUSTERED(StreetName,StreetTypeID,StreetDirectionID,Locality,StateID,Postcode,StreetNumber),CONSTRAINT FK_StreetNumbers_Streets FOREIGN KEY(StreetName,StreetTypeID,StreetDirectionID,Locality,StateID,Postcode) REFERENCES dbo.Streets (StreetName,StreetTypeID,StreetDirectionID,Locality,StateID,Postcode))CREATE TABLE dbo.Addresses (StreetName varchar (35) NOT NULL ,StreetTypeID varchar (10) NOT NULL ,StreetDirectionID varchar (2) NOT NULL ,Locality varchar (46) NOT NULL ,StateID varchar (3) NOT NULL ,Postcode char (4) NOT NULL ,StreetNumber varchar (15) NOT NULL ,AddressTypeID varchar (6) NOT NULL ,AddressName varchar (20) NOT NULL ,CONSTRAINT PK_StreetNumberPrefixes PRIMARY KEY CLUSTERED(StreetName,StreetTypeID,StreetDirectionID,Locality,StateID,Postcode,StreetNumber,AddressTypeID,AddressName),CONSTRAINT FK_Addresses_StreetNumbers FOREIGN KEY(StreetName,StreetTypeID,StreetDirectionID,Locality,StateID,Postcode,StreetNumber) REFERENCES dbo.StreetNumbers (StreetName,StreetTypeID,StreetDirectionID,Locality,StateID,Postcode,StreetNumber))

View Replies !

Foreign And Primary Keys
I have an application in which i need to get the foreign key fieldsfrom a table and then get all the foreign keys primary key field fromthe linking table. Could some one tell me how i do this usingINFORMATION_SCHEMA. I have tried and can get the foreign keys but notsure how to get the associated primary keys.

View Replies !

Fliegroups And Primary Keys
I have a question with regads to placement of data files/indexes onmultiple filegroups. Here is the current scenario:I have a database comprised of two filegroups - PRIMARY and INDEX. ThePRIMARY filegroup is comprised of two files, one residing on the Rdrive and another residing on the O drive. The INDEX filegroupconsists of a file on the S drive. The transaction logs reside on theT drive. The box itself has five individual drive slots, not RAID'edor mirrored. The tables are created in the PRIMARY filegroup, theindexes in the INDEX group.My question is this: is there any inherent benefit to create thePRIMARY KEYS in a different filegroup? Currently, they are beingcreated in the PRIMARY group along with the actual data tables.Thank you in adavance for any help.Anthony Robinson

View Replies !

Using SQL To Find Primary Keys
What query do I use to list the primary key for each user table, i.e.TABLE | PRIMARY_KEY |Regards,Alan*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View Replies !

Missing Primary Keys
HI Folks ,

I have a problem with the primary keys in my main Db , I want to setup replication and looks like someone tampered with my database by removing the primary keys.and in order to setup replication i need this table to have primary keys .THere are duplicates in that table but they are nessecary...and for this reason the primary keys do not want to "stick" when i try and specify them.Can anybody help

Burner

View Replies !

Forcing Primary Keys
Hi all,

As our DB has no primary keys or indexes ive taken a copy of all populated tables and tried to force primary keys within a new DB.

the problem is all off the tables have multiple datasets within them, a dataset for each year. This causes all instances of ID numbers to not be unique as they are replicated for every year they are active.

Its a school database so a student who has been here for 3 years will have 3 instances of his ID number, one for each years' data set.

So how do i force primary keys if there is no unique identifier? ive been highlighting both data set and ID columns and setting that combination as the primary key.

Essentially i need to analyse the relationships between the tabls in a diagram and also run some speed tests to see how fast the db works when it has indexes and primary keys.

the reason im writing is that ive done this on ten tables and with another 160 to do im just checking im doing the right thing?

greg

View Replies !

Table With Duplicate Entry With Primary Keys

We have a SQL Server 6.5 table, with composite Primary Key, having the Duplicate Entry for the Key. I wonder how it got entered there? Now when we are trying to import this table to SQL2K, it's failing with Duplicate row error. Any Help?