Foreign And Composite Keys

Hi.

Could somebody please explain to me how to create a foreign key from a table that
has two composite keys? I have a table, UserPrecedence, with two composite keys -
up_owner, owner_userID. I have a second table, Users, that has the primary key
"emailAddress" and a userID table to which all other tables point their FK columns.

http://i103.photobucket.com/albums/m156/pbd22/Keys.jpg

I want the userID column in Users to be the FK of the owner_userID column in UserPrecedence
but the Modify Table view complains that "both sides of the relationship must have the
same number of columns" when I try to create the relationship.

I am guessing this is because its a composite primary key. Can somebody explain
to me how this is done correctly (and why)?

I appreciate your help.
Thanks.

If you want to reference the "UserPrecedence" table from the "Users" table according to the present key structure of the "UserPrecedence" table you must also include the "up_order" column in the "Users" table. However, there might be other possibilities.

First, is the "owner_userID" column of the "UserPrecedence" table a unique column? If so, you might consider changing your primary key to this column.

|||
hi, thanks.

yes, owner_userID is unique but I need to keep my PK in UserPrecedence as a composite key on (up_order, owner_userID). The foreign key points from this table (source) to Users_userID (destination). Does this mean that I always have to add the composite PK column (I have many tables like this) that is not in the Users table to create the FK relationship?

thanks for your help.

|||

If your owner_userID field is unique, then you can do the following (which may not be best practice but it is an option):

You can alter your table and add a UNIQUE constraint on the owner_userID field

You can use then use the owner_userID field as a foreign key reference from other tables because you have designated it as an alternate key

The syntax for declaring the alternate MUST include explicit references

|||
Thanks for your help.

I am getting the following error:

"There are no primary or candidate keys in the referenced table 'precedence' that match the referencing column list in the foreign key 'FK_users_precedence'.

(Not to confuse you but I have made some naming changes to my tables - I am trying to sync up with the ISO-11179 rules. UserPrecedence is now 'precedence'. emailAddress is now email).

Below are the two tables in question:

DBO.users:

CREATE TABLE [dbo].[users](
[registerdate] [datetime] NOT NULL,
[password] [varchar](50) NOT NULL,
[role] [char](50) NOT NULL,
[securityquestion] [varchar](50) NOT NULL,
[securityanswer] [varchar](50) NOT NULL,
[zipcode] [int] NOT NULL,
[alternateemail] [varchar](50) NULL,
[email] [varchar](50) NOT NULL,
[birthmonth] [tinyint] NOT NULL,
[birthday] [tinyint] NOT NULL,
[birthyear] [int] NOT NULL,
[userid] [int] IDENTITY(1,1) NOT NULL,
[gender] [char](10) NULL,
[city] [varchar](50) NULL,
[state] [varchar](50) NULL,
[country] [varchar](50) NULL,
[editdate] [datetime] NULL,
[lastname] [varchar](50) NULL,
[firstname] [varchar](50) NULL,
[confirmed] [bit] NULL CONSTRAINT [DF__Users__confirmed__4CC05EF3] DEFAULT ((0)),
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_email] UNIQUE NONCLUSTERED
(
[email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

--

DBO.precedence

CREATE TABLE [dbo].[precedence](
[order] [int] NOT NULL,
[profileid] [int] NULL,
[userid] [int] NOT NULL,
[searchname] [varchar](50) NOT NULL,
CONSTRAINT [PK_precedence] PRIMARY KEY CLUSTERED
(
[searchname] ASC,
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]|||OK.

A little further along...

It seems I didn't create the UNIQE constraint on userid (since userid is a composite primary key and is,
therefore, UNIQUE, is it redundant to explicitly create a UNIQUE constraint on this column?). After I
did this, I ran the following code:

Code Snippet

ALTER TABLE users
ADD CONSTRAINT fk_users_precedence
FOREIGN KEY (userid)
REFERENCES precedence(userid)

and got the following error:

"The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_users_precedence". The conflict occurred in database "MyDB", table "dbo.precedence", column 'userid'."

here is my updated precedence CREATE script:

Code Snippet

CREATE TABLE [dbo].[precedence](
[order] [int] NOT NULL,
[profileid] [int] NULL,
[userid] [int] NOT NULL,
[searchname] [varchar](50) NOT NULL,
CONSTRAINT [PK_precedence] PRIMARY KEY CLUSTERED
(
[searchname] ASC,
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_userid] UNIQUE NONCLUSTERED
(
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

|||
OK. For anybody that is curious about this error and wants to see some possible solutions (if you
have a similar problem) the below thread provided a solution for me:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=478256&SiteID=1

Thanks.

For Loop

Hello,

Please, could someone explain me how "For Loop" funtions?

because I need to load data depending on a variable?

Can I use For loop to do that?

thanks

Your question is a bit vague. Here is the For Loop documentation. Yes, you can use variables to control the loop. If this doesn't answer your question, then please be more specific about what you're trying to do.

FOR EACH Loop to export files based on Header Record

Hey guys,,

Well im new at this SSIS stuff and i have something that i am trying to do, but cannot get it to work.. Ill try to explain, and if anyone can help me or point me in the right direction it would be much appriciated..

I have 2 tables, one header table. and one lines table. This is a one - to -many relationship.. ie 1 header, many lines.. This is a Order Header, and Order Lines table setup.. Order header has Order numbers and and email address field that link to the lines table by order number. I also have a view which links all this info together.

I would like to export a excel file (preferable named from the order number column - but that can come later) for each order number in the header table. The excel file will contain the details from the View that was created. I want this to loop through all the header records in the header table and create a excel file for each one..

Down the track i want to add a send mail task to this and pass the email address to a variable so i can use it in the send mail task.. But ill get the main part working first..

Anyhelp would be more than helpful.. I tried to set this up, but i am stuck on the enumerator part..

thanks again, scotty

I just created my first blog entry with something for you; let me know if it helps.

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||

Thanks Rafael.. This is exactly what i wanted to do and you have described the process exactly

Much appriciated..

Merry xmas and have a happpy new year..

Thaks

Scotty