composite key in Query Analyzer

composite key in Query Analyzer

Post by David Porta » Sun, 16 Nov 2003 09:57:50



CREATE TABLE Sometable (col1 INTEGER, col2 INTEGER, col3 INTEGER NOT NULL,
col4 INTEGER NOT NULL, CONSTRAINT PK_ConstraintName PRIMARY KEY (col1,col2),
CONSTRAINT UQ_ConstraintName UNIQUE (col3,col4))

--
David Portas
------------
Please reply only to the newsgroup
--

 
 
 

composite key in Query Analyzer

Post by <anonym.. » Fri, 21 Nov 2003 02:06:35


Thanx for your help!
Quote:>-----Original Message-----
>CREATE TABLE Sometable (col1 INTEGER, col2 INTEGER, col3
INTEGER NOT NULL,
>col4 INTEGER NOT NULL, CONSTRAINT PK_ConstraintName

PRIMARY KEY (col1,col2),
Quote:>CONSTRAINT UQ_ConstraintName UNIQUE (col3,col4))

>--
>David Portas
>------------
>Please reply only to the newsgroup
>--

>.


 
 
 

1. Key construction with composite Keys

I am building a database that is making use of composite keys.  To insert
data, and in the process creating keys, I am using stored procs with code
like the following pseudo-code:

select

from
    SomeTable
where
    <blah criteria>


begin



    begin

        select

        from
            SomeTable
        where
            <blah criteria>


data>)


            break


    end

I added this loop so that in the rare case that another transaction updated
the table after the 2nd select and before the 3rd, the stored proc would
automatically retry to create a key and insert the data.  Do I even need
this loop?  Will SqlServer's locking mechanism protect me from returning the
same 'max(Table.KeyPart) + 1)' twice?  I know it will if I set the
transaction type to Serialized, but, as I understand it, that would impact
perf considerablebly.  I'd like to keep the Transaction type as Read
Committed (default?).

Also, should I put that first select statement in the loop?

Thanks,
Wells

2. Retrieve data from cube ?

3. Compound Key vs Composite Key

4. AZ-Phoenix ** Great Opportunity for Sybase DBA (recruiter)

5. Performance comparision of retrieving records using composite key or single key

6. Copy Family in version 7???

7. Primary Key, Composite Foreign Key - Can I ?

8. SQL Mail - saving message in SentItems folder

9. Regarding previous post about finding primary key/composite keys

10. Determining primary key (or composite keys) of a table through JDBC

11. Composite Primary key or 1 primary key column with all values

12. Row based keys or composite key

13. Composite Primary key or 1 primary key column with all values