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.

CREATE TABLE with multiple-column primary key?

Is it possible to issue the CREATE TABLE command and specify a multiple-colu
mn primary key?
If so, what is the syntax? I've checked BOL and as far as I can tell, you ma
y only select
a single column as the primary key *within the CREATE TABLE command*; ALTER
TABLE must be used
for multiple-column primary keys.
For example (this does *not* work):
CREATE TABLE #TEMPProcedures (ProcedureID int NOT NULL, ProcedureSuffix int
NOT NULL
PRIMARY KEY ProcedureID, ProcedureSuffix)
Thanks in advance --
CarlOnly a matter of a comma and a parenthesis. Below work fine:
CREATE TABLE #TEMPProcedures
(ProcedureID int NOT NULL
,ProcedureSuffix int NOT NULL
,PRIMARY KEY (ProcedureID, ProcedureSuffix))
I prefer to name all my contraints...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Carl Imthurn" <nospam@.all.com> wrote in message news:uIvG984GGHA.1032@.TK2MSFTNGP12.phx.gbl
..
> Is it possible to issue the CREATE TABLE command and specify a multiple-co
lumn primary key?
> If so, what is the syntax? I've checked BOL and as far as I can tell, you
may only select
> a single column as the primary key *within the CREATE TABLE command*; ALTE
R TABLE must be used
> for multiple-column primary keys.
> For example (this does *not* work):
> CREATE TABLE #TEMPProcedures (ProcedureID int NOT NULL, ProcedureSuffix in
t NOT NULL
> PRIMARY KEY ProcedureID, ProcedureSuffix)
> Thanks in advance --
> Carl
>|||Thanks Tibor - that worked.
And, your comment about naming all constraints is well taken.
Carl
Tibor Karaszi wrote:

> Only a matter of a comma and a parenthesis. Below work fine:
> CREATE TABLE #TEMPProcedures (ProcedureID int NOT NULL
> ,ProcedureSuffix int NOT NULL
> ,PRIMARY KEY (ProcedureID, ProcedureSuffix))
> I prefer to name all my contraints...

Create table on multiple Filegroups .. is it possible ?

Can i create a table/index that spans multilple FGs such as
CREATE TABLE T1
( cola int PRIMARY KEY,
colb char(8) )
ON FG1,FG2,FG3Are you confused between files and filegroups? Have a read in bol about them.
The answer to the question you have posed is no - you have a clustered index
which resides on the data filegroup.
But I don't think it's the question you wanted to ask.
"Hassan" wrote:
> Can i create a table/index that spans multilple FGs such as
> CREATE TABLE T1
> ( cola int PRIMARY KEY,
> colb char(8) )
> ON FG1,FG2,FG3
>
>|||As Nigel says, it isn't possible. Why do you ask? The purpose of
filegroups is to provide a logical entity on which to place data. The
PHYSICAL placement of data is determined by the location of files,
rather than filegroups. So it should be possible to achieve whatever
configuration you need using a single filegroup per object.
--
David Portas
SQL Server MVP
--

Create Table and Alter table:

Hi All,

I am using SQl server studio management to create table.

How to set two attributes as a primary key: composite key. like ( proj_id, emp id) both as primary key.

How to specify the forign key constraint. using alter table

Please give me the example: don't syntax which msdn gives.

Thanks and Regards

Abdul M.G

Hi,

Look at this.

1CREATE TABLE Menu2(3 MenuIdint,4 Titlevarchar(50),5 Urlvarchar(256),6 ParentIdintNULL7)89ALTER TABLE MenuADD CONSTRAINT MenuIdPRIMARY KEY1011ALTER TABLE MenuADD CONSTRAINT ParentIdREFERENCES Menu(MenuId)

sql

Create table + index + primary

for MS SQL 2000
how can I do this in one time (into the CREATE TABLE)

CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL ,
[Name] [nvarchar] (100) NULL,
[Serial] [nvarchar] (100) NULL,
) ON [PRIMARY]

ALTER TABLE [dbo].[Users] WITH NOCHECK ADD
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[id_Users]
) ON [PRIMARY]

CREATE UNIQUE INDEX [IX_Users] ON [Users]([Serial]) ON [PRIMARY]

and that one

CREATE TABLE [dbo].[UsersExtra] (
[id_Users] [int] NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[UsersExtra] ADD
CONSTRAINT [FK_UsersExtra_Users] FOREIGN KEY
(
[id_Users]
) REFERENCES [Users] (
[id_Users]
) ON DELETE CASCADE

thank youi am getting an error

CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL PRIMARY KEY clustered,
[Name] [nvarchar] (100) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[UsersExtra] (
[id_UsersExtra] [int] NOT NULL REFERENCES [Users].[id_Users] ON DELETE CASCADE
) ON [PRIMARY]

Msg 1767, Level 16, State 0, Line 50
Foreign key 'FK__Users__id_Co__05D9AC15' references invalid table 'Users.id_Users'.
Msg 1750, Level 16, State 0, Line 50|||you are trying to reference a non-existing table, 'Users.id_Users'|||but I have created it just before

CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL PRIMARY KEY clustered,
[Name] [nvarchar] (100) NULL
) ON [PRIMARY]|||To get back on the first question ...
You can't create a table and an index in one single statement, except when that index is the PK ... so more than one statement is required to get the job done. You can however combine the create and alter table statements into one. See BOL 'create table'.

Gr,
Yveau|||Add Go and try it again|||but I have created it just beforeyes, you did

however, that is not the source of your problem

you said REFERENCES [Users].[id_Users]

this is invalid syntax, because it is trying to reference a table called "id_Users" belonging to user called "Users"

for the correct syntax, please see the manual

:)

CREATE TABLE - two fields combine to make the primary key

Perhaps I'm dating myself.
In a table, I want to create a primary key that consists
of two columns (patrolId and incidentId). From the
documentation I have, it looks like you can only have one
field that is a primary key.
Can you help?
hi Ed,
Ed H wrote:
> Perhaps I'm dating myself.
> In a table, I want to create a primary key that consists
> of two columns (patrolId and incidentId). From the
> documentation I have, it looks like you can only have one
> field that is a primary key.
> Can you help?
SET NOCOUNT ON
USE tempdb
GO
CREATE TABLE test_table (
patrolId INT NOT NULL ,
incidentId INT NOT NULL ,
Data VARCHAR(10) NOT NULL ,
CONSTRAINT pk_test_table
PRIMARY KEY ( patrolId , incidentId )
)
GO
DROP TABLE test_table
CREATE TABLE synopsis is available at
http://msdn.microsoft.com/library/de...eate2_8g9x.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

Create sequential numbers in a column

I have a temp table that's populated with an insert query in as tored
procedure. The temp table has a uniqueID as the primary key.
In that table I have a column SortOrder.
What I want to do is to create a sequential number in SortOrder but
only for records matching a WHERE statement, for example:
(pardon the shorthand...)
Insert *.tblPermanent into tblTemp
If myField = 1 then
SortOrder = 1(2,3,4,5,....etc.)
else
SortOrder = 0
Thanks
lqLauren Quantrell (laurenquantrell@.hotmail.com) writes:
> I have a temp table that's populated with an insert query in as tored
> procedure. The temp table has a uniqueID as the primary key.
> In that table I have a column SortOrder.
> What I want to do is to create a sequential number in SortOrder but
> only for records matching a WHERE statement, for example:
> (pardon the shorthand...)
> Insert *.tblPermanent into tblTemp
> If myField = 1 then
> SortOrder = 1(2,3,4,5,....etc.)
> else
> SortOrder = 0

There are a lot of things that I don't know about, so I have to make
a guess. First, I make the guess that the tblPermanent has a primary-
key column called id. In such case, you can do:

INSERT tblTemp(id, sortorder, ....)
SELECT id, (SELECT COUNT(*)
FROM tblPermanent b
WHERE b.id >= a.id
AND b.myfield = 1
AND a.myfield = 1), ...
FROM tblPermanent

If this does answer your question, please provide the following:

o CREATE TABLE statements for your table.
o INSERT statements with sample data.
o The desired result from the sample data.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Create Primary Key with increment and format?

I have an access table that has a primary key (entitled "ID Number"), no duplicates, the field is an integer.
And, importantly, the value is set to "increment".
The format is "phd"000 - so it starts outphd001,phd002, and so on...
How to do this in an SQL table? Can that format be done? Or is it better not to do it via SQL but in coding instead?In SQL Server you do Unique constraint or Unique index for no duplicates the former allow nulls the later not null being primary key and set IDENTITY property on the column for auto increament. Run a search for Unique constraint and Unique index and the IDENTITY property in SQL Server BOL(books online). Hope this helps.

Create linked server in SQL 2005 from Excel spreadsheet and have primary key?

Is it possible to create a linked server from an Excel spreadsheet and give it a primary key? If so, how?

Thanks,

--Stan

This was for a Report Builder issue that I've resolved another way, but it's still an interesting question for other uses...

|||

No, Excel has no idea of Primary Keys, using Report Builder you probably would create a logical primary key to accomplish the creation of relationships.

Jens K. Suessmeyer

http://www.sqlserver2005.de

CREATE FULLTEXT CATALOG x ON FILEGROUP 'PRIMARY' issue

Books online recommends creating the FTS catalog in a new filegroup
I have created a FileGroup called FTS (not readonly and not default)
However the following syntax fails.
CREATE FULLTEXT CATALOG Z_Search_Freetext
ON FILEGROUP 'FTS'
IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
'f:\MSSQL\FDATA\'
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION dbo
with
Incorrect syntax near 'FTS'.
If I remove ON FILEGROUP 'FTS' the following works without error
CREATE FULLTEXT CATALOG Z_Search_Freetext
IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
'f:\MSSQL\FDATA\'
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION dbo
Any assistance would be appreciated.Looks like I forgot to add a file to the filegroup. DOH!
"Richard Yeo" wrote:

> Books online recommends creating the FTS catalog in a new filegroup
> I have created a FileGroup called FTS (not readonly and not default)
> However the following syntax fails.
> CREATE FULLTEXT CATALOG Z_Search_Freetext
> ON FILEGROUP 'FTS'
> IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA' -- TODO
> 'f:\MSSQL\FDATA'
> WITH ACCENT_SENSITIVITY = ON
> AUTHORIZATION dbo
> with
> Incorrect syntax near 'FTS'.
> If I remove ON FILEGROUP 'FTS' the following works without error
> CREATE FULLTEXT CATALOG Z_Search_Freetext
> IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA' -- TODO
> 'f:\MSSQL\FDATA'
> WITH ACCENT_SENSITIVITY = ON
> AUTHORIZATION dbo
> Any assistance would be appreciated.

CREATE FULLTEXT CATALOG x ON FILEGROUP 'PRIMARY' issue

Books online recommends creating the FTS catalog in a new filegroup
I have created a FileGroup called FTS (not readonly and not default)
However the following syntax fails.
CREATE FULLTEXT CATALOG Z_Search_Freetext
ON FILEGROUP 'FTS'
IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
'f:\MSSQL\FDATA\'
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION dbo
with
Incorrect syntax near 'FTS'.
If I remove ON FILEGROUP 'FTS' the following works without error
CREATE FULLTEXT CATALOG Z_Search_Freetext
IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
'f:\MSSQL\FDATA\'
WITH ACCENT_SENSITIVITY = ON
AUTHORIZATION dbo
Any assistance would be appreciated.Looks like I forgot to add a file to the filegroup. DOH!
"Richard Yeo" wrote:
> Books online recommends creating the FTS catalog in a new filegroup
> I have created a FileGroup called FTS (not readonly and not default)
> However the following syntax fails.
> CREATE FULLTEXT CATALOG Z_Search_Freetext
> ON FILEGROUP 'FTS'
> IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
> 'f:\MSSQL\FDATA\'
> WITH ACCENT_SENSITIVITY = ON
> AUTHORIZATION dbo
> with
> Incorrect syntax near 'FTS'.
> If I remove ON FILEGROUP 'FTS' the following works without error
> CREATE FULLTEXT CATALOG Z_Search_Freetext
> IN PATH 'C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\' -- TODO
> 'f:\MSSQL\FDATA\'
> WITH ACCENT_SENSITIVITY = ON
> AUTHORIZATION dbo
> Any assistance would be appreciated.

Create Database Failed - Primary file must be at least 3 MB ...

I am trying to create a database with the following command

CREATE DATABASE [db1] ON PRIMARY
( NAME = N'db1', FILENAME = N'C:\Databases\Main\db1.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'db1_log', FILENAME = N'C:\Databases\Main\db1_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

However, I always get the following error:

CREATE DATABASE failed. Primary file must be at least 3 MB to accommodate a copy of the model database.

I don't have a clue what's going wrong? The very same command is working fine on other machines. Its only one particular machine where it fails with the error message. Strangely enough, it worked on the "problem machine" too until I had to format the hard disk of the machine and re-install everything from scratch.

Any idea what could be going wrong? Please note that I am using SQL Server 2005.

On the server where this fails, verify the size of the Model database. I suspect it has inadvertently become larger than expected.

All newly created databases use the Model database as a 'template'. The new databases will start out no smaller than the Model database.

As this message indicates, since the Model database is about 3 MB in size, you MUST have a minimum size of 3 MB.

And you can reduce the size of the Model database. See Books Online, Topics: DBCC Shrinkdatabase, DBCC Shrinkfile

|||Thanks for your response. I checked the size of model database and found that it has a size of 3MB on that particular server as you said.
Actually I am creating a installation package which has to run on different machines. I am new to SQL Server so have a lesser understanding of many features of SQL Server.
Since, size of model database may just vary from machines to machines, I decided to entirely remove the SIZE=2048KB option from the CREATE DATABASE command. I tried it on my test machines and seems to work fine.
It seems that removing the SIZE option should not be a problem as system seems to pick up the size of model database on the server where is is executing.
Please correct if I am wrong or you see any negative impacts of this.
|||

You are correct, removing the [SIZE] parameter will ensure that you don't run into a similar problem.

I would suggest that immediately after the CREATE DATABASE statement, if your needs dictate a certain size to start with, that your code check the defined size and issue a ALTER DATABASE statement to increase if necessary.

|||I do not have any such requirement as of now so I might not be needing an 'alter database'
Its just that I am new to SQL server and I exported this script through SQL Server management studio. I wasn't aware that SIZE has a dependency on model database.

Anyway, thanks a lot for your suggestions.