Is it safe to add PK ? What about INSERT INTO ?

Question

Is it safe to add PK ? What about INSERT INTO ?

Allen Nugent

Right there with Babe

Points: 734
More actions
September 14, 2010 at 6:55 pm

#148693

I have identified a flaw in a program that relies on 2 related tables: one containing sales order headers (QHeader) and the other sales order lines (QDetail). Neither of these tables has a primary key (!!!), and the program relies on the rows from QDetail being returned in the same order in which they were written. I want to add a PK to QDetail and alter the program logic so as to order the returned data set by PK.
So far, the assumption that a view on QDetail always returns the rows in the same order seems to hold up -- unless some rows (order lines) have been deleted and added. So, I can't afford to mess up the status quo. In other words, I don't want to do anything that would reorder the rows in QDetail.
Thus, I have two concerns. First, if I use INSERT INTO to create a backup of QDetail, will the rows be copied in the same order to the new table? Second, if I insert a PK on QDetail, will the rows remain in the same order?
Thanks for any advice!
-- nervous Al

Viewing 7 posts - 1 through 6 (of 6 total)

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

Allen Nugent Right there with Babe Points: 734 More actions · Answer 1

I should have said that neither table has an IDENTITY column. The parent table (QHeader) does have a primary key (QID). The child table (QDetail) has no PK, but uses QID as a foreigh key.

SQLRNNR SSC Guru Points: 281324 More actions · Answer 2

Well, if the view on the table always returns the rows in the same order - how does the view ensure that order?

I would use the same logic from the view to create the insert statement for the backup table.

Also, the view might give some insight into what should be a good PK for the Detail table.

Could you provide the ddl for the tables and the view?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Allen Nugent Right there with Babe Points: 734 More actions · Answer 3

Thanks, Jason.

BTW: It's actually not a view but dynamic SQL executed via a Delphi ADO component.

The view looks like

SELECT * FROM QDetail WHERE QID = 'someqid'

and the table DDL is

CREATE TABLE [dbo].[QDetail](

[QID] [varchar](50) NOT NULL,

[Rindex] [smallint] NULL,

[RFran] [char](4) NULL,

[RPart] [char](16) NULL,

[Rqty] [smallint] NULL,

[Rsell] [money] NULL,

[Rext] [money] NULL,

[Rdescr] [varchar](60) NULL,

[SFran] [char](4) NULL,

[SPart] [char](16) NULL,

[SQty] [smallint] NULL,

[SSell] [money] NULL,

[SExt] [money] NULL,

[SDescr] [varchar](60) NULL,

[SSource] [char](4) NULL,

[SrcName] [varchar](50) NULL,

[Selected] [smallint] NULL,

[Requested] [smallint] NULL,

[Availability] [varchar](50) NULL,

[Unit_Weight] [numeric](18, 3) NULL,

[Orig_sell] [money] NULL,

[Disc_Perc] [smallint] NULL,

[BT_Ref] [varchar](50) NULL,

[BTSource] [char](4) NULL,

[modby] [char](5) NULL

) ON [PRIMARY]

So, the query does nothing to order the rows in its dataset. I thinkwe've just been lucky.

P.S. Using SQL Server 2005.

SQLRNNR SSC Guru Points: 281324 More actions · Answer 4

I would have to agree that you have been lucky.

It looks like QID and Rindex may be the columns for a key. you may be able to create the new backup table with an identity pk Clustered index if you order by those two columns when doing the insert / select into.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Allen Nugent Right there with Babe Points: 734 More actions · Answer 5

Thanks again.

I'll try to figure out the syntax I need, as I haven't really worked with keys and indexes in T-SQL.

The design of the QID key doesn't help matters; it contains datetime information, but it's not in a good sequence for sorting (i.e. ddmmyyyyhhmmss). Also, the 1st 5 chars of QID are a user ID. So, sorting on it will intermix clusters of old and new records.

I've modified the program to let me play with copies of the tables. so I'll do some testing before I commit to modifying the live table.

Allen Nugent Right there with Babe Points: 734 More actions · Answer 6

Just remembered: I can't sort the QDetail table without causing trouble.

Some rows have the same QID and RIndex, but NULL values for RQty, RSell, RExt, RDescr. This indicates a split line: the order is being supplied with equivalent but different part numbers, stored in separate but sequential rows. If the row with the non-NULL values doesn't precede its associated split lines, the order won't appear correctly when retrieved and displayed by the program.

(Nb. I can't make any changes to the program without going through a massive change management cycle.)