| Author |
Topic |
|
parrot
Posting Yak Master
132 Posts |
 Posted - 2008-07-22 : 20:22:31
|
| I am using a CREATE TABLE command in a query that I develop dynamically in my C# program. However, I find that I am limited to the use of one primary key when defining a table using an SQL query. Is there anyway around this so that I can specify more than 1 primary key in a query command? I know I can specify more than 1 primary key manually in SQL Server Management Studio.CREATE TABLE MyTable (Field1 char(6) NOT NULL PRIMARY KEY, Field2 char(10) NOT NULL PRIMARY KEY, field3 char(4), etc.This command gives me an error saying that I can have only 1 primary key. That seems to be a sever limitation for most databases I work with have at least 2 primary keys.Dave |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-22 : 20:30:21
|
| It is not possible to have more than one primary key constraint on a table. Perhaps you should use a unique constraint instead.But are you referring to multiple columns in a primary key constraint?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2008-07-23 : 09:32:34
|
| I do want to have multiple columns as keys in my table. Can I just use the UNIQUE constraint on both columns then? Can you specify multiple columns in a primary key constraint?Dave |
|
|
|
rohitkumar
Constraint Violating Yak Guru
472 Posts |
Posted - 2008-07-23 : 09:38:27
|
| whats the need to have multiple primary columns, do you want to prevent duplicates?yes you can have multiple columns in a primary key, and a combination of these columns should be unique. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-23 : 09:54:15
|
| you can have multiple columns in primary key. but keep in mind that this will ensure only unique combination of them not unique values in each fields.i.e if you're creating PK on composite columns (col1,col2) then it only ensures col1 & col2 combination is unique not individually on each of col1 & col2however you can have pk on one column and unique constraint on other if you want to ensure unique values in them. |
|
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2008-07-23 : 09:56:38
|
| I do want to prevent duplicates and also it is supposed to make access quicker if you have keys in a table. CREATE TABLE MyTable (Field1 char(6) NOT NULL PRIMARY KEY, Field2 char(10) NOT NULL PRIMARY KEY, field3 char(4), etc.From the example above how would you recode it to make it into a multiple column key where field1 and field2 are the primary keys? |
|
|
|
rohitkumar
Constraint Violating Yak Guru
472 Posts |
Posted - 2008-07-23 : 09:59:45
|
| well not exactly...to prevent duplicates you can use unique constraint. Primary key enforces creation of clustered index which speeds up data access, you can have non-clustered indexes on multiple columns.to create multi column key read about COMPOSITE PRIMARY KEYhttp://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-23 : 10:00:08
|
| CREATE TABLE MyTable (Field1 char(6) NOT NULL, Field2 char(10) NOT NULL, field3 char(4),...,CONSTRAINT Your_Table_PK PRIMARY KEY (Field1,Field2)) |
|
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2008-07-23 : 10:14:42
|
| Thank you for the coding suggestion. I only want unique combinations of field1 and field2. I do not want a unique value in each. Therefore, the example given by visakh16 should work. I will test it today. I am an old COBOL programmer gradually making the transition into SQL databases away from the old index files of yore. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-23 : 10:35:12
|
quote:
Originally posted by parrot
Thank you for the coding suggestion. I only want unique combinations of field1 and field2. I do not want a unique value in each. Therefore, the example given by visakh16 should work. I will test it today. I am an old COBOL programmer gradually making the transition into SQL databases away from the old index files of yore.
Then it should do the trick for you. Try it out and let us know the outcome. |
|
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2008-07-23 : 14:30:47
|
| My testing was successful and I am able to create a table with multiple primary keys. Thanks to everyone for their help. Thank God for these forums.Dave |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-23 : 14:38:57
|
| Dave,Your wording is incorrect. You can never create multiple primary keys on a table. What you've done is create a single primary key with multiple columns. This is known as a composite primary key.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
parrot
Posting Yak Master
132 Posts |
Posted - 2008-07-23 : 21:25:19
|
| Thanks for your correction. I know what I want but unfortunately I am not totally familiar with all of the terminology.Dave |
|
|
|
|
Primary key limitation