Composite Primay Key and foreign Key

Hi all,

I get a big problem when I doing my assignment.
It told me to:

- Create a table with one composite primary key
- Create another table with one primary key and one foreign key refer to last table

Here is my SQL statement
CREATE TABLE table1
(
FirstName varchar(10)
LastName varchar(10)
PRIMARY KEY (FirstName, LastName)
)

CREATE TABLE table2
(
PlanID int,
PRIMARY KEY (PlanID)
FirstName varchar(10) FOREIGN KEY REFERENCES table1(FirstName)
LastName varchar(10) FOREIGN KEY REFERENCES table1(LastName)
)

It failed. I have searched for google long time but no result for that.
Hope you can save me. Thanks!!!Your problem is probably caused because you reference a column to a partial key. What exact error do you get?

Another solution could be:

CREATE TABLE table1
(
keyField int not null
FirstName varchar(10)
LastName varchar(10)
PRIMARY KEY (keyField)
)

CREATE TABLE table2
(
PlanID int not null,
PRIMARY KEY (PlanID)
t1_keyField int FOREIGN KEY REFERENCES table1(keyField)
)

If you want the combination of the first and last name to be unique you can always create a unique constraint on the two columns.|||[QUOTE][SIZE=1]Originally posted by jora
Your problem is probably caused because you reference a column to a partial key. What exact error do you get?

The error said
"There are no primary or candidate keys in the referenced table 'table1'
that match the referencing column list in the foreign key 'FK_table2_FirstName'

Thanks for your suggestion but table1 should have a composite primary key instead of a single primary key.|||Oh! I get it.

Here is the code:
CREATE TABLE table1
(
FirstName varchar(10),
LastName varchar(10),
PRIMARY KEY (FirstName, LastName),
)

CREATE TABLE table2
(
PlanID int,
FirstName varchar(10),
LastName varchar(10),
PRIMARY KEY (PlanID),
FOREIGN KEY(FirstName,LastName) REFERENCES table1 FirstName,LastName),
)

composite primary key

How can i implement :
Foreign key of one table referencing composite primary key of other table?Here's a sample of ours...

Check out BOL though

ALTER TABLE [dbo].[WH_Proof] ADD
FOREIGN KEY
(
[Company_Name],
[Tax_Agency_ID],
[Region],
[Tax_Class],
[Sub_Region]
) REFERENCES [dbo].[WH_Proof_List] (
[Company_Name],
[Tax_Agency_ID],
[Region],
[Tax_Class],
[Sub_Region]
),
FOREIGN KEY
(
[Company_Name],
[Tax_Agency_ID],
[Region],
[Tax_Class],
[Proof_Year],
[Proof_Period]
) REFERENCES [dbo].[Disb_Proof] (
[Company_Name],
[Tax_Agency_ID],
[Region],
[Tax_Class],
[Proof_Year],
[Proof_Period]
),
FOREIGN KEY
(
[Period_Type]
) REFERENCES [dbo].[Period_Type] (
[Period_Type]
),
FOREIGN KEY
(
[Status]
) REFERENCES [dbo].[Proof_Status] (
[Status]
)
GO|||can i define the composite foreign key while creating the table?

Complicated query

I have a table called PizzaToppings. The columns are Topping ID & PizzaID.

ToppingID is a foreign key from Toppings table & PizzaID is a foreign key from Pizza table.

Together they form the composite primary key for PizzaToppigs table.

I need to find out what the most popular topping is.

ie I have to find the ToppingID with the maximum number of occurrences in the PizzaTopping table.

PLEASE HELP!

ThanksHey,

Given the relation

A B
---- ----
a b1
a b2
a b3
a b4
b b2

select a, count(*)
from help
group by a
having count(*) >= all (select count(*) from help group by a);

returns

A COUNT(*)
---- ----
a 4

I removed the example with 'rownum = 1', as it contained a vital flaw being it only returns row 1, thus if 2 toppings are equal in popularity then only the first one alphabetically would be returned.|||Thanks MUCH! Very obliged.

Originally posted by r123456
Hey,

Given the relation

A B
---- ----
a b1
a b2
a b3
a b4
b b2

select a, count(*)
from help
group by a
having count(*) >= all (select count(*) from help group by a);

returns

A COUNT(*)
---- ----
a 4

I removed the example with 'rownum = 1', as it contained a vital flaw being it only returns row 1, thus if 2 toppings are equal in popularity then only the first one alphabetically would be returned.

Complex File Import

I am trying to import a flat file of semi-colon delimited values into a
table. The table has many foreign keys that reference other tables. The
flat file has the data all explicitly stated instead of the ID numbers. I
want to make it so that if a new value is encountered, a new entry in that
related table is created. An example: The main table, CITTAS_Primary, has a
foreign key, DrayageID, that links to the table CITTAS_Drayage_Company, whic
h
has a foreign key, DrayageCompanyID, that links to the table CITTAS_Drayage.
I can parse the entries that exist in the table already, but I am unsure of
how to insert new values using an ActiveX Script Task. I am currently using
Execute SQL tasks to retrieve the recordsets from CITTAS_Drayage and
CITTAS_Drayage_Company and then place the resulting rowsets into global
variables. Here is the code that I have so far:
Function Main()
Dim Company, CompanyID
Company = DTSSource("Col003")
Dim Charge, ID
Charge = DTSSource("Col009")
ID = -1
Dim comps, chs
Set comps = CreateObject("ADODB.Recordset")
Set comps = DTSGlobalVariables("DrayageCompany").Value
Set chs = CreateObject("ADODB.Recordset")
Set chs = DTSGlobalVariables("DrayageCharge").Value
Dim i
For i = 1 To comps.RecordCount
If comps.Fields("DrayageCompany") = Company Then
CompanyID = comps.Fields("CompanyID")
Break
End If
comps.MoveNext
Next i
For i = 1 To chs.RecordCount
If chs.Fields("DrayageCost") = Charge And chs.Fields("DrayageCompanyID") =
CompanyID Then
ID = chs.Fields("DrayageID")
Break
End If
chs.MoveNext
Next i
If ID = -1 Then
'Insert code to insert new record
'Get ID for new cost/company pair
End If
DTSDestination("DrayageCompanyID") = ID
Main = DTSTransformStat_OK
End Function
I want it to be able to insert new companies into CITTAS_Drayage and new
cost/company pairs into CITTAS_Drayage_Company.
Also, for some reason, it is complaining about using the RecordCount
property of the recordset. I would think that it would work since it is
straight from SQL Server Books Online DTS collection. If anyone knows what'
s
going on with this, please enlighten me, because it amkes no sense to me.
Thanks in advance, everyone.
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsHi Chris
I am not sure of the example you talk about but
DTSGlobalVariables("DrayageCompany").Value may not be a recordset.
John
"Chris Lieb" wrote:

> I am trying to import a flat file of semi-colon delimited values into a
> table. The table has many foreign keys that reference other tables. The
> flat file has the data all explicitly stated instead of the ID numbers. I
> want to make it so that if a new value is encountered, a new entry in that
> related table is created. An example: The main table, CITTAS_Primary, has
a
> foreign key, DrayageID, that links to the table CITTAS_Drayage_Company, wh
ich
> has a foreign key, DrayageCompanyID, that links to the table CITTAS_Drayag
e.
> I can parse the entries that exist in the table already, but I am unsure o
f
> how to insert new values using an ActiveX Script Task. I am currently usi
ng
> Execute SQL tasks to retrieve the recordsets from CITTAS_Drayage and
> CITTAS_Drayage_Company and then place the resulting rowsets into global
> variables. Here is the code that I have so far:
>
> Function Main()
> Dim Company, CompanyID
> Company = DTSSource("Col003")
> Dim Charge, ID
> Charge = DTSSource("Col009")
> ID = -1
> Dim comps, chs
> Set comps = CreateObject("ADODB.Recordset")
> Set comps = DTSGlobalVariables("DrayageCompany").Value
> Set chs = CreateObject("ADODB.Recordset")
> Set chs = DTSGlobalVariables("DrayageCharge").Value
> Dim i
> For i = 1 To comps.RecordCount
> If comps.Fields("DrayageCompany") = Company Then
> CompanyID = comps.Fields("CompanyID")
> Break
> End If
> comps.MoveNext
> Next i
> For i = 1 To chs.RecordCount
> If chs.Fields("DrayageCost") = Charge And chs.Fields("DrayageCompanyID")
=
> CompanyID Then
> ID = chs.Fields("DrayageID")
> Break
> End If
> chs.MoveNext
> Next i
> If ID = -1 Then
> 'Insert code to insert new record
> 'Get ID for new cost/company pair
> End If
> DTSDestination("DrayageCompanyID") = ID
> Main = DTSTransformStat_OK
> End Function
>
> I want it to be able to insert new companies into CITTAS_Drayage and new
> cost/company pairs into CITTAS_Drayage_Company.
> Also, for some reason, it is complaining about using the RecordCount
> property of the recordset. I would think that it would work since it is
> straight from SQL Server Books Online DTS collection. If anyone knows wha
t's
> going on with this, please enlighten me, because it amkes no sense to me.
> Thanks in advance, everyone.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||BTW... I was going to add..
Rather than mess around with the activeX script it may be quicker (and IMO
easier) to load into a staging table, sort out the missing FKs and then load
into the live table.
John
"Chris Lieb" wrote:

> I am trying to import a flat file of semi-colon delimited values into a
> table. The table has many foreign keys that reference other tables. The
> flat file has the data all explicitly stated instead of the ID numbers. I
> want to make it so that if a new value is encountered, a new entry in that
> related table is created. An example: The main table, CITTAS_Primary, has
a
> foreign key, DrayageID, that links to the table CITTAS_Drayage_Company, wh
ich
> has a foreign key, DrayageCompanyID, that links to the table CITTAS_Drayag
e.
> I can parse the entries that exist in the table already, but I am unsure o
f
> how to insert new values using an ActiveX Script Task. I am currently usi
ng
> Execute SQL tasks to retrieve the recordsets from CITTAS_Drayage and
> CITTAS_Drayage_Company and then place the resulting rowsets into global
> variables. Here is the code that I have so far:
>
> Function Main()
> Dim Company, CompanyID
> Company = DTSSource("Col003")
> Dim Charge, ID
> Charge = DTSSource("Col009")
> ID = -1
> Dim comps, chs
> Set comps = CreateObject("ADODB.Recordset")
> Set comps = DTSGlobalVariables("DrayageCompany").Value
> Set chs = CreateObject("ADODB.Recordset")
> Set chs = DTSGlobalVariables("DrayageCharge").Value
> Dim i
> For i = 1 To comps.RecordCount
> If comps.Fields("DrayageCompany") = Company Then
> CompanyID = comps.Fields("CompanyID")
> Break
> End If
> comps.MoveNext
> Next i
> For i = 1 To chs.RecordCount
> If chs.Fields("DrayageCost") = Charge And chs.Fields("DrayageCompanyID")
=
> CompanyID Then
> ID = chs.Fields("DrayageID")
> Break
> End If
> chs.MoveNext
> Next i
> If ID = -1 Then
> 'Insert code to insert new record
> 'Get ID for new cost/company pair
> End If
> DTSDestination("DrayageCompanyID") = ID
> Main = DTSTransformStat_OK
> End Function
>
> I want it to be able to insert new companies into CITTAS_Drayage and new
> cost/company pairs into CITTAS_Drayage_Company.
> Also, for some reason, it is complaining about using the RecordCount
> property of the recordset. I would think that it would work since it is
> straight from SQL Server Books Online DTS collection. If anyone knows wha
t's
> going on with this, please enlighten me, because it amkes no sense to me.
> Thanks in advance, everyone.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps

Complete list of error codes

Does anyone know where to obtain a complete list of error codes which can occur in SQL Server ? For example : errror number 547 is a foreign key violation - where can I get a complete list of these numbers and there descritions ?

I can't find it in BOL or anywhere

In BOL type in 'errors-SQL Server' in the search box in the index tab and they are listed starting with Error 10008 (DB-Library)

you can also take a look here

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_syserrors1a_10mx.asp

Denis the SQL Menace

http://sqlservercode.blogspot.com/