Thursday, March 29, 2012

Create table with PK on two columns

Hi,
Could you tell me the syntax to set primary key on two columns when I create
a table? I can't find it in the books. The syntax I found is
CREATE TABLE MyTable (c1 INT PRIMARY KEY,c2 INT)
How can put c2 as part of PRIMARY KEY?
I don't know how to use CONSTRAINT. So if CONSTRAINT is need, pls tell me.
Thanks.Chrissi wrote:
> Hi,
> Could you tell me the syntax to set primary key on two columns when I
> create a table? I can't find it in the books. The syntax I found is
> CREATE TABLE MyTable (c1 INT PRIMARY KEY,c2 INT)
> How can put c2 as part of PRIMARY KEY?
> I don't know how to use CONSTRAINT. So if CONSTRAINT is need, pls
> tell me.
> Thanks.
Create table MyTable (
c1 INT NOT NULL,
c2 INT NOT NULL
PRIMARY KEY (c1, c2) )
or
Create table MyTable (
c1 INT NOT NULL,
c2 INT NOT NULL )
Alter Table MyTable
ADD PRIMARY KEY (c1, c2)
David Gugick
Imceda Software
www.imceda.com|||Create Table MyTable
(c1 INT Not Null,
c2 INT Not Null,
Primary Key (C1, c2))
"§Chrissi§" wrote:

> Hi,
> Could you tell me the syntax to set primary key on two columns when I crea
te
> a table? I can't find it in the books. The syntax I found is
> CREATE TABLE MyTable (c1 INT PRIMARY KEY,c2 INT)
> How can put c2 as part of PRIMARY KEY?
> I don't know how to use CONSTRAINT. So if CONSTRAINT is need, pls tell me
.
> Thanks.
>
>|||CREATE TABLE MyTable (
c1 INT NOT NULL,
c2 INT NOT NULL,
CONSTRAINT pk_MyTable PRIMARY KEY(c1 ,c2)
)
or
CREATE TABLE MyTable (
c1 INT NOT NULL,
c2 INT NOT NULL,
PRIMARY KEY(c1 ,c2)
)
It's technically a constraint in both cases, but you aren't
required to give it a name.
Steve Kass
Drew University
Chrissi wrote:

>Hi,
>Could you tell me the syntax to set primary key on two columns when I creat
e
>a table? I can't find it in the books. The syntax I found is
>CREATE TABLE MyTable (c1 INT PRIMARY KEY,c2 INT)
>How can put c2 as part of PRIMARY KEY?
>I don't know how to use CONSTRAINT. So if CONSTRAINT is need, pls tell me.
>Thanks.
>
>|||Server: Msg 1911, Level 16, State 1, Line 1
Column name 'C1' does not exist in the target table.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
Watch your spelling! Some of us choose a case-sensitive
collation now and then. ;)
SK
CBretana wrote:
>Create Table MyTable
> (c1 INT Not Null,
> c2 INT Not Null,
> Primary Key (C1, c2))
>"§Chrissi§" wrote:
>
>|||Oops! My typing is never good (two finger hint n pec) but I noticed the
upper case C and left it that way anyway... Out of curiousity, why are you
using case-sensitive collation?
"Steve Kass" wrote:

> Server: Msg 1911, Level 16, State 1, Line 1
> Column name 'C1' does not exist in the target table.
> Server: Msg 1750, Level 16, State 1, Line 1
> Could not create constraint. See previous errors.
> Watch your spelling! Some of us choose a case-sensitive
> collation now and then. ;)
> SK
> CBretana wrote:
>
>|||
CBretana wrote:

> Oops! My typing is never good (two finger hint n pec) but I noticed the
> upper case C and left it that way anyway... Out of curiousity, why are you
> using case-sensitive collation?
Mostly so I can generate the appropriate error messages to include in posts
like this one. ;)
I didn't used to pay attention to this, and it didn't matter as much when
keypunch machines were uppercase-only, or with case-insensitive languages
like Pascal. I had to break sloppy habits when C came along, and though I
slipped into old habits when I started using SQL, I've found more and more
reasons not to be sloppy lately, such as keeping Erland from bugging me
if I put "northwind"."orders" in examples I post. ;)
There are plenty of things you can write that will behave differently
according to collation and language settings, and forcing myself to
be careful about case helps me see and avoid them.
SK
> "Steve Kass" wrote:
>|||>> Out of curiousity, why are you using case-sensitive collation? <<
Because Standard SQL is case-sensitive.

No comments:

Post a Comment