Can we insert NULL in place of foreign key.

I have a couple of tables in my database (SQL server 2000)
1. Student -- Columns are as
"StudentID" -- INT
"StudentName" -- NVARCHAR
"DepartmentID" -- INT
2. Department -- Columns are as
"DepartmentID" -- INT
"DepartmentName" -- NVARCHAR
So in the table "Student", the field "DepartmentID" is a foreign key. Now I
tried out turning "Allow Null" on for the column "DepartmentID" in the
"Student" table. As soon as I did this, I was able to insert a row with
"DepartmentID" as <NULL>.
Now isn't this wrong? Or is it that the database overrides the checking of
the foreign key against the table "Department" when I check the "Allow Null"
option ON?
It would be really helpful if someone could elaborate on this.
Locate me in the blogosphere: http://spaces.msn.com/aayushpuriHi
Yes, referencing table may accept NULL's. Like people that don't have
children.
"Aayush Puri" <aayushpuri @. h o t m a i l . c o m> wrote in message
news:C5A2D39D-5C28-4661-B68B-B2CA6BA48CDF@.microsoft.com...
>I have a couple of tables in my database (SQL server 2000)
> 1. Student -- Columns are as
> "StudentID" -- INT
> "StudentName" -- NVARCHAR
> "DepartmentID" -- INT
> 2. Department -- Columns are as
> "DepartmentID" -- INT
> "DepartmentName" -- NVARCHAR
> So in the table "Student", the field "DepartmentID" is a foreign key. Now
> I
> tried out turning "Allow Null" on for the column "DepartmentID" in the
> "Student" table. As soon as I did this, I was able to insert a row with
> "DepartmentID" as <NULL>.
> Now isn't this wrong? Or is it that the database overrides the checking of
> the foreign key against the table "Department" when I check the "Allow
> Null"
> option ON?
> It would be really helpful if someone could elaborate on this.
> --
> Locate me in the blogosphere: http://spaces.msn.com/aayushpuri|||Aayush Puri wrote:
> I have a couple of tables in my database (SQL server 2000)
> 1. Student -- Columns are as
> "StudentID" -- INT
> "StudentName" -- NVARCHAR
> "DepartmentID" -- INT
> 2. Department -- Columns are as
> "DepartmentID" -- INT
> "DepartmentName" -- NVARCHAR
> So in the table "Student", the field "DepartmentID" is a foreign key. Now
I
> tried out turning "Allow Null" on for the column "DepartmentID" in the
> "Student" table. As soon as I did this, I was able to insert a row with
> "DepartmentID" as <NULL>.
> Now isn't this wrong? Or is it that the database overrides the checking of
> the foreign key against the table "Department" when I check the "Allow Nul
l"
> option ON?
> It would be really helpful if someone could elaborate on this.
> --
> Locate me in the blogosphere: http://spaces.msn.com/aayushpuri
SQL's foreign key constraints work like check constraints.
Specifically, values are permitted as long as the constraint isn't
violated. A null comparison returns an UNKNOWN result rather than a
FALSE one and UNKNOWN isn't regarded as a violation of the constraint.
So the behaviour you mentioned is "right" according to the SQL
standard. Whether it makes good logical sense is a different matter.
I'd suggest that you either disallow nulls or that you create some
default value in the Depatrtments table to represent the case that you
would otherwise have used a null for - a "Not Applicable" department
for example.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Can we do a double Cursor?

Can we do a double Cursor where we select the data form one table using a
cursor and then use the key data such as ORDER number to select data from
another table using a cursor?
regards,
RonYou can. You can also try to take your bike on the freeway at rush hour and
challenge a street racer...
Any reason you can't do this with a simple join? What exactly are you
trying to accomplish?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
<Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
news:A22E9344-5385-4A00-92CD-B9EF611E6C47@.microsoft.com...
> Can we do a double Cursor where we select the data form one table using a
> cursor and then use the key data such as ORDER number to select data from
> another table using a cursor?
> --
> regards,
> Ron|||I’m trying to simulate SQR program. Do you have an example where you use
double cursor.
"Aaron [SQL Server MVP]" wrote:

> You can. You can also try to take your bike on the freeway at rush hour a
nd
> challenge a street racer...
> Any reason you can't do this with a simple join? What exactly are you
> trying to accomplish?
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> <Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
> news:A22E9344-5385-4A00-92CD-B9EF611E6C47@.microsoft.com...
>
>|||I’m trying to simulate SQR program. Do you have an example where you use
double cursor.
"Aaron [SQL Server MVP]" wrote:

> You can. You can also try to take your bike on the freeway at rush hour a
nd
> challenge a street racer...
> Any reason you can't do this with a simple join? What exactly are you
> trying to accomplish?
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> <Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
> news:A22E9344-5385-4A00-92CD-B9EF611E6C47@.microsoft.com...
>
>|||I have absolutely no idea what an "SQR program" is. Do you mean square
root?
Anyway, a double cursor would look like this:
CREATE TABLE #pk
(
id INT PRIMARY KEY
)
CREATE TABLE #fk
(
id int FOREIGN KEY REFERENCES #pk(id),
foo VARCHAR(2)
)
SET NOCOUNT ON
INSERT #pk
SELECT 1
UNION SELECT 2
INSERT #fk
SELECT 1, 'a'
UNION SELECT 1, 'b'
UNION SELECT 2, 'c'
DECLARE @.pkID INT, @.foo VARCHAR(2)
DECLARE c1 CURSOR FOR
SELECT id FROM #pk
OPEN c1
FETCH NEXT FROM c1 INTO @.pkID
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE c2 CURSOR FOR
SELECT foo FROM #fk WHERE id = @.pkID
OPEN c2
FETCH NEXT FROM c2 INTO @.foo
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Outer loop: '+RTRIM(@.pkID)
PRINT 'Inner loop: '+@.foo
FETCH NEXT FROM c2 INTO @.foo
END
CLOSE c2
DEALLOCATE c2
FETCH NEXT FROM c1 INTO @.pkID
END
CLOSE c1
DEALLOCATE c1
DROP TABLE #fk, #pk
Please don't do this on a production system. If you do, don't tell them I
told you how to do it. I will deny it and tell them it was Celko, spoofing
my IP and all.
On 3/22/05 8:03 PM, in article
9E814E08-0578-4953-8BD4-E0CEB718ADD2@.microsoft.com, "Ron,hayim"
<Ronhayim@.discussions.microsoft.com> wrote:

> Im trying to simulate SQR program. Do you have an example where you use
> double cursor.|||On Tue, 22 Mar 2005 15:41:02 -0800, Ron wrote:

>Can we do a double Cursor where we select the data form one table using a
>cursor and then use the key data such as ORDER number to select data from
>another table using a cursor?
Hi Ron,
Even if you really do need a cursor (which I doubt - and the statistics
are on my side), there is really no need to use two of the beasts.
Why not create one query that joins the two tables the way you want them
to be joined, filters rows you don't need and returns only the columns
you need? Then, if you really must, you can always use that query for
your cursor...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The script was a lot of help. SQR is a program that used by Peoplesoft.
This was just a special need. All of you right, just joining the tables
should do the it.
"Aaron [SQL Server MVP]" wrote:

> I have absolutely no idea what an "SQR program" is. Do you mean square
> root?
> Anyway, a double cursor would look like this:
>
> CREATE TABLE #pk
> (
> id INT PRIMARY KEY
> )
> CREATE TABLE #fk
> (
> id int FOREIGN KEY REFERENCES #pk(id),
> foo VARCHAR(2)
> )
> SET NOCOUNT ON
> INSERT #pk
> SELECT 1
> UNION SELECT 2
> INSERT #fk
> SELECT 1, 'a'
> UNION SELECT 1, 'b'
> UNION SELECT 2, 'c'
> DECLARE @.pkID INT, @.foo VARCHAR(2)
> DECLARE c1 CURSOR FOR
> SELECT id FROM #pk
> OPEN c1
> FETCH NEXT FROM c1 INTO @.pkID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> DECLARE c2 CURSOR FOR
> SELECT foo FROM #fk WHERE id = @.pkID
> OPEN c2
> FETCH NEXT FROM c2 INTO @.foo
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Outer loop: '+RTRIM(@.pkID)
> PRINT 'Inner loop: '+@.foo
> FETCH NEXT FROM c2 INTO @.foo
> END
> CLOSE c2
> DEALLOCATE c2
> FETCH NEXT FROM c1 INTO @.pkID
> END
> CLOSE c1
> DEALLOCATE c1
> DROP TABLE #fk, #pk
>
> Please don't do this on a production system. If you do, don't tell them I
> told you how to do it. I will deny it and tell them it was Celko, spoofin
g
> my IP and all.
>
> On 3/22/05 8:03 PM, in article
> 9E814E08-0578-4953-8BD4-E0CEB718ADD2@.microsoft.com, "Ron,hayim"
> <Ronhayim@.discussions.microsoft.com> wrote:
>
>

Can we define multiple key columns for a mining structure?

Hi, all,

Just found that we are not able to define multiple key columns for a mining structure in SQL Server 2005 Data Mining engine, just wondering is there other way to define multiple key columns for a mining structure there? As in many cases, the table we are mining are with composite key consisting of different foriengn keys, e.g. A fact table are with transaction information and other foreign keys. If I am not able to define these composite key here for this fact table, I will have to have a named calculation in data source view to have a key column which is based on these original composite keys? Is this a better way to solve this problem or there is any other alternatives to figure it out?

Hope my question is clear for your help and I am looking forward to heaing from you shortly for your kind advices and help and thanks a lot in advance.

With best regards,

Yours sincerely,

Multiple key columns are not permitted in a mining structure. However, you can specify multiple bindings for the Key column in the mining structure: in the Mining Structure tab in BI Dev Studio, click on KeyColumns in the Properties pane and then click on the "..." button in the value field to bring up an editor that allows you add additional bindings. You will also need to specify a NameColumn binding (the next field in Properties) that binds to a source that contains unique names for the composite key you've specified via the multiple bindings under KeyColumns.

The option you suggest (adding a named calculation in the DSV) will also work.

|||

Hi, Raman,

Thanks for your advices.

But still we need a name column whenever we have a muitiple columns bindings for the key attribute?

With best regards,

Yours sincerely,

|||Yes, that's correct - otherwise the server does not know what to name the composite key value (it can't do a simple concatenation because of type differences).|||

Hi, Raman,

Thanks.

Best regards,

Yours sincerely,

Can we create composite logical key in data source view?

Hi, all,

Just found that I need to create a composite logical key consisting of a few columns in data source view in order to uniquely identify each row of record for the table in data source view, but then found that I am not able to create this kind of logical key? Is there any ways for us to create such a compoiste logical key in the data source view?

Thanks in advance and I am very much looking forward to hearing from you for your kind and helpful advices.

With best regards,

Yours sincerely,

You can create composite logical keys in the DSV, just multi-select columns, right click, and mark as logical key. However, if an existing physical key (i.e. a key reported by the relational source), then the DSV will not allow you to create any logical key.|||

Hi,

Thanks a lot.

WIth best regards,

Yours sincerely,

Can we allow NULL value to nested key?

Hi, all here,

I am having a problem with NULL value for the nested key of the nested table. Is it possible to allow ''null'' value to nested key? If so, how can we deal with that? As the column I am using for nested key contains null values, thus the process failed. Could please any expert here give me any advices for that?

Really need help for that.

With best regards,

Yours sincerely,

You cannot have a NULL value as nested key. The simple way to work around is to replace your nested table, in the data source view, with a named query which filters out the NULL columns. I think replacing the table with a query is an option if you right click on the table in the Data Source View Designer

|||

Hi, Bogdan, thank you very much for your guidance.

With best regards,

Yours sincerely,

Can two Tables have the same primary key ?

I have been given a project to complete where ... two tables "Flights" and "ScheduledFlights" have a column called "FlightNo" . In both these tables it is mentioned that "FlightNo" is primary key ?
Is this possible ? i am not talking about foreign key here....

Here is the script that i have created..

create table flights
(
FlightNo char(5) constraint FlightNo primary key clustered not null,
DepTime char(5) not null,
ArrTime char(5) not null,
AircraftTypeID char(4) references Aircraft(AircraftTypeID) not null,
SectorID Char(5) references Sector(SectorID) not null
)

create table ScheduledFlights
(
FlightNo char(5) constraint FlightNo2 primary key clustered not null,
FlightDate datetime not null,
FirstClassSeatsAvailable int not null,
BusinessClassSeatsAvailable int not null,
EconomyClassSeatsAvailable int not null
)

Now the problem is with FlightNo attribute .. ? Can two Tables have the same primary key ?
(FlightNo2 is given as a temporary solution)

Hi

I think your question sounds a bit ambiguous because you do not distinguish between Primary Key itself and Primary Key's Name.

Two tables can have primary keys defined on the columns with the same name. But no two tables can share the same Primary Key name. To awoid confusion you can call your Primary keys :

CONSTRAINT pkFligts PRIMARY KEY (FlightNo )

CONSTRAINT pkSheduledFligts PRIMARY KEY (FlightNo )

NB.

|||Thanks. I will ask my teacher about that... can u tell me one more thing ...

I have another table called " Passenger" which has an attribute called Travel Date which is>

"Date Of travel. The flight number and the date of travel together form a foreign key that references the flight number and flight date in the flight table."

In short ,

FlightNo(in Flights Table)+DeptTime(in Flights Table)

how will i write this in the create table statement... sorry i am learning if this sounds stupid.|||

That explains it :-)

When you need to create a multi-column Foreigh Key , you add your constraint separately after a list of columns. Make sure , the columns references by

CREATE TABLE Passenger

( <your list of columns>,

CONSTRAINT fkPassengerFlights FOREIGN KEY (FlightNo, DeptTime)

REFERENCES Fligts (FlightNo, DeptTime)

)

NB. Good luck with your studing.

|||

Hello,

What you need is a composite foreign key. A composite key is made of more than one column. A composite primary key will have say col1, col2 and col3 etc in it. Any foreign key referencing such composite primary key will have col1, col2 and col3 in it. Such a Foreign Key is called Composite Foreign Key. Below is a sample code..hope it helps...

create table.....

constraint [FK_AnyRelevantName] Foreign Key (col1, col2, col3)

References [TableName_Containing_Primary_Key] (Col1, Col2, col3) -- these will be the primary key columns defined on another table.

I Hope this is helpful.

Thanks......

|||Thank you very much for taking time off and helping me out.

Can two Tables have the same primary key ?

I have been given a project to complete where ... two tables "Flights" and "ScheduledFlights" have a column called "FlightNo" . In both these tables it is mentioned that "FlightNo" is primary key ?
Is this possible ? i am not talking about foreign key here....

Here is the script that i have created..

create table flights
(
FlightNo char(5) constraint FlightNo primary key clustered not null,
DepTime char(5) not null,
ArrTime char(5) not null,
AircraftTypeID char(4) references Aircraft(AircraftTypeID) not null,
SectorID Char(5) references Sector(SectorID) not null
)

create table ScheduledFlights
(
FlightNo char(5) constraint FlightNo2 primary key clustered not null,
FlightDate datetime not null,
FirstClassSeatsAvailable int not null,
BusinessClassSeatsAvailable int not null,
EconomyClassSeatsAvailable int not null
)

Now the problem is with FlightNo attribute .. ? Can two Tables have the same primary key ?
(FlightNo2 is given as a temporary solution)

Hi

I think your question sounds a bit ambiguous because you do not distinguish between Primary Key itself and Primary Key's Name.

Two tables can have primary keys defined on the columns with the same name. But no two tables can share the same Primary Key name. To awoid confusion you can call your Primary keys :

CONSTRAINT pkFligts PRIMARY KEY (FlightNo )

CONSTRAINT pkSheduledFligts PRIMARY KEY (FlightNo )

NB.

|||Thanks. I will ask my teacher about that... can u tell me one more thing ...

I have another table called " Passenger" which has an attribute called Travel Date which is>

"Date Of travel. The flight number and the date of travel together form a foreign key that references the flight number and flight date in the flight table."

In short ,

FlightNo(in Flights Table)+DeptTime(in Flights Table)

how will i write this in the create table statement... sorry i am learning if this sounds stupid.|||

That explains it :-)

When you need to create a multi-column Foreigh Key , you add your constraint separately after a list of columns. Make sure , the columns references by

CREATE TABLE Passenger

( <your list of columns>,

CONSTRAINT fkPassengerFlights FOREIGN KEY (FlightNo, DeptTime)

REFERENCES Fligts (FlightNo, DeptTime)

)

NB. Good luck with your studing.

|||

Hello,

What you need is a composite foreign key. A composite key is made of more than one column. A composite primary key will have say col1, col2 and col3 etc in it. Any foreign key referencing such composite primary key will have col1, col2 and col3 in it. Such a Foreign Key is called Composite Foreign Key. Below is a sample code..hope it helps...

create table.....

constraint [FK_AnyRelevantName] Foreign Key (col1, col2, col3)

References [TableName_Containing_Primary_Key] (Col1, Col2, col3) -- these will be the primary key columns defined on another table.

I Hope this is helpful.

Thanks......

|||Thank you very much for taking time off and helping me out.

can this be done with a check constraint?

i have a zip code table downloaded from the usps in excel and imported
into a sql database table.
zipcode is not a primary key in the table, as it lists all the cities
and aliases for cities that include each zipcode.
however there is a rule in the data:
for any given zipcode, only ONE row of data can have a citytype value
of 'D'
what i am wondering is how i can turn that rule into a database level
constraint. i can't create a unique constraint on zipcode + citytype,
because any number of rows for the same zipcode can have citytype N and
A, for example. it is only the D value that can appear only once per
zipcode.
is this something a check constraint can accomplish? i'm new to check
constraints so i'm not sure what that constraint rule would look like.
it seems more complicated than "thisfield > thatfield" type logic.
thanks,
jasonI think this is beyond the capabilities of a check constraint.
A Check constraint is an expression which is used to check that the contents
of a column conform to a given rule. The constraint cannot reference
information on a different row of data than the one which is being checked.
You may be able to accomplish your scenario using a trigger. There are
times however, then the integrity checking would be so expensive that it's
simply more efficient to push said rule to a different business logic layer.
Colin.
"jason" <iaesun@.yahoo.com> wrote in message
news:1142884753.165122.99270@.u72g2000cwu.googlegroups.com...
>i have a zip code table downloaded from the usps in excel and imported
> into a sql database table.
> zipcode is not a primary key in the table, as it lists all the cities
> and aliases for cities that include each zipcode.
> however there is a rule in the data:
> for any given zipcode, only ONE row of data can have a citytype value
> of 'D'
> what i am wondering is how i can turn that rule into a database level
> constraint. i can't create a unique constraint on zipcode + citytype,
> because any number of rows for the same zipcode can have citytype N and
> A, for example. it is only the D value that can appear only once per
> zipcode.
> is this something a check constraint can accomplish? i'm new to check
> constraints so i'm not sure what that constraint rule would look like.
> it seems more complicated than "thisfield > thatfield" type logic.
> thanks,
> jason
>|||Jason,
2 ways to accomplish that:
1. google up "nullbuster" and create an index on computed columns
2. create an indexed view for
select zipcode from mytable where citytype = 'D'
Good luck!sql

can the foreign key be from different database?

Hi,
I have 2 database, one with all static config data and another one is the us
er data. There is a table in the user database one column have to refer the
column in static database, can I add a foreign key to it that refers a prima
ry key in static database?
how? ThanksYou can use a trigger to emulate a FK between tables in different databases
or servers even.
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:92C2B56D-747A-4960-8C84-AA8B6C106E27@.microsoft.com...

quote:

---

> Hi,
> I have 2 database, one with all static config data and another one is the

---

user data. There is a table in the user database one column have to refer
the column in static database, can I add a foreign key to it that refers a
primary key in static database? how? Thanks

Can sp_changearticle help when needing to change the primary key?

We need to add a new column to a table that is part of a multi-table
publication replicated via transactional replication. We've read (and
re-read) the sp_changearticle page in Books Online without fully
understanding how we might be able to use this command to help with our task.
Can someone please provide two important answers -- 1) can we use
sp_changearticle to make this kind of change to our publication? 2) can you
offer an example of how sp_changearticle is coded for such purposes.
Actually, we'd be interested in seeing how sp_changearticle is coded in
general, even if it cannot be used for our particular task.
Thanks,
Barry Spiegel
barry.spiegel@.eds.com
2) sp_changearticle 'pubs', 'jobs','description','this is the new
description'
1) no, you use use sp_repladdcolumn like this:
sp_repladdcolumn 'jobs','intcol','int not null default(1)'
This column will be modified in all publications and their subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Barry Spiegel" <Barry Spiegel@.discussions.microsoft.com> wrote in message
news:732D9DB5-C5A6-4A65-AEB7-37384C791401@.microsoft.com...
> We need to add a new column to a table that is part of a multi-table
> publication replicated via transactional replication. We've read (and
> re-read) the sp_changearticle page in Books Online without fully
> understanding how we might be able to use this command to help with our
task.
> Can someone please provide two important answers -- 1) can we use
> sp_changearticle to make this kind of change to our publication? 2) can
you
> offer an example of how sp_changearticle is coded for such purposes.
> Actually, we'd be interested in seeing how sp_changearticle is coded in
> general, even if it cannot be used for our particular task.
> Thanks,
> Barry Spiegel
> barry.spiegel@.eds.com
>

can primary key be derived/created from 2 columns?

Hello,
In MS Access a primary key can be created from 2 (or more) non-unique
columns which would comprise a unique column -- as follows (from these 2
repeating columns) using the Indexes dialog box in table design and this
places a Key symbol on the respective columns:
col1 col2
1 mon
1 tue
1 wed
1 thu
1 fri
2 mon
2 tue
2 wed
2 thu
2 fri
...
The combination of col1 and col2 would comprise the primary key here. So in
Access I create a PrimaryKey name and just add fields to that name. Is
something like this doable in sql Server?
I have been able to create a unique key from 2 non-unique fields in a sql
server table and they serve as a constraint. But I don't get the Primary Ke
y
symbol on the columns like in Access. Matter of fact, I observed that only
one column can take the Primary Key symbol. I guess my real question (or
next question) is what the significance is of the Primary Key Symbol and if
it is correct that only one column in a sql server table can have the Primar
y
Key symbol on it?
The purpose of my question(s) is to have as much understanding of Primary
Keys in Sql Server as I can. Right now, it appears that a primary key serve
s
mainly as a constraint. What are other significances of the Primary key?
Thanks,
RichNever mind about the question for getting the double keys. I just figured i
t
out. But if I want to join the primary key to a foreign key in another
table, how can I make a unique join to the primary key from the other table?
Thanks,
Rich|||>> But I don't get the Primary Key symbol on the columns like in Access. <<
What does that mean? It sounds like you are drawing pictures to
program! Do you know how to program? With real code, in a language?
While youy are learning to be a real programmer, instead of a video
game, learn DDL and the "PRIMARY KEY(<col1>, <col2> )" syntax.|||Rich (Rich@.discussions.microsoft.com) writes:
> Never mind about the question for getting the double keys. I just
> figured it out. But if I want to join the primary key to a foreign key
> in another table, how can I make a unique join to the primary key from
> the other table?
SELECT ...
FROM a
JOIN b ON a.col1 = b.col1
AND a.col2 = b.col2
Or did you ask how to do this through point and click? I'm afraid that
I don't know the answer to that question. I know that there is a query
designer in Enterprise Manager, and also in Management Studio. But
this designer is a very limited tool, that only can handle simple queries.
If you feed it more complex queries, you will find that it is prone to
rewrite the queries, so that the meaning of them changes.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:86745BD5-0521-43BB-B615-D1C68A78ABFB@.microsoft.com...
> Hello,
> In MS Access a primary key can be created from 2 (or more) non-unique
> columns which would comprise a unique column -- as follows (from these 2
> repeating columns) using the Indexes dialog box in table design and this
> places a Key symbol on the respective columns:
> col1 col2
> 1 mon
> 1 tue
> 1 wed
> 1 thu
> 1 fri
> 2 mon
> 2 tue
> 2 wed
> 2 thu
> 2 fri
> ...
> The combination of col1 and col2 would comprise the primary key here. So
> in
> Access I create a PrimaryKey name and just add fields to that name. Is
> something like this doable in sql Server?
> I have been able to create a unique key from 2 non-unique fields in a sql
> server table and they serve as a constraint. But I don't get the Primary
> Key
> symbol on the columns like in Access. Matter of fact, I observed that
> only
> one column can take the Primary Key symbol. I guess my real question (or
> next question) is what the significance is of the Primary Key Symbol and
> if
> it is correct that only one column in a sql server table can have the
> Primary
> Key symbol on it?
> The purpose of my question(s) is to have as much understanding of Primary
> Keys in Sql Server as I can. Right now, it appears that a primary key
> serves
> mainly as a constraint. What are other significances of the Primary key?
> Thanks,
> Rich
SQL Server isn't Access. Try learning SQL rather than playing with the
Enterprise Manager interface. It may seem hard work for you right now but
eventually you'll benefit from more control, better results and better
understanding of what you are doing. Example:
ALTER TABLE your_table
ADD CONSTRAINT pk_your_table
PRIMARY KEY (col1,col2);

> Right now, it appears that a primary key serves
> mainly as a constraint. What are other significances of the Primary key?
Primary key is a constraint. SQL Server also creates an index for a primary
key constraint but the first purpose of a primary key is to support entity
integrity and referential integrity.
David Portas
SQL Server MVP
--|||Thank you very much for your explanantion. This was very informative. My
learning is derived from reading on the subject, doing, and asking a lot of
questions. Thus, I thank you kindly for sharing your explanation and for
giving an example. I was not aware that you could use more than one column
in this statement:
ALTER TABLE your_table
ADD CONSTRAINT pk_your_table
PRIMARY KEY (col1,col2);
These are the little details that I miss in my reading, and so I ask the
question and someone points out these more obscure details.
Thanks again,
Rich
"David Portas" wrote:

> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:86745BD5-0521-43BB-B615-D1C68A78ABFB@.microsoft.com...
> SQL Server isn't Access. Try learning SQL rather than playing with the
> Enterprise Manager interface. It may seem hard work for you right now but
> eventually you'll benefit from more control, better results and better
> understanding of what you are doing. Example:
> ALTER TABLE your_table
> ADD CONSTRAINT pk_your_table
> PRIMARY KEY (col1,col2);
>
> Primary key is a constraint. SQL Server also creates an index for a primar
y
> key constraint but the first purpose of a primary key is to support entity
> integrity and referential integrity.
> --
> David Portas
> SQL Server MVP
> --
>
>|||Thank you for your explanation on how to join tables where the primary key
consists of more than one column.
Rich
"Erland Sommarskog" wrote:

> Rich (Rich@.discussions.microsoft.com) writes:
> SELECT ...
> FROM a
> JOIN b ON a.col1 = b.col1
> AND a.col2 = b.col2
> Or did you ask how to do this through point and click? I'm afraid that
> I don't know the answer to that question. I know that there is a query
> designer in Enterprise Manager, and also in Management Studio. But
> this designer is a very limited tool, that only can handle simple queries.
> If you feed it more complex queries, you will find that it is prone to
> rewrite the queries, so that the meaning of them changes.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Try using the product tools before bashing somebody.
The symbol the OP is talking about is on Enterprise Manager table designer.
Not everybody wants to learn DDL, they just want a simple method of storing
data for their application - that can all be done through the GUI now,
despite what you might say, it is a good thing and widens database use.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1136241163.051698.144990@.g49g2000cwa.googlegroups.com...
> What does that mean? It sounds like you are drawing pictures to
> program! Do you know how to program? With real code, in a language?
> While youy are learning to be a real programmer, instead of a video
> game, learn DDL and the "PRIMARY KEY(<col1>, <col2> )" syntax.
>