Foreign Key and Nullable column.

Hi Gurus,
This seems simple but not to me. Now I want to create a new table that
contains a column such as Product_Id. I want to create a foreign key
constraint on Product_Id pointing to a Product table. Sometimes the
Product_Id can be NULL but I don't want to make the column to be a nullable
column, because that can make query complex and slow. So I like to use 0 to
represent NULL. Now I also don't want to insert a Product_Id=0 to the Product
table, because I just worry that can hurt applications or some reports. My
dillema here is that I just can't create the forieign key to protect data
integrity because the 0 Product_Id may not be able to find a line in the
Product table. I also have other design choices like this.
Do you have similar experience? Can any guru here give me a good advice? Can
I avoid to insert a dummy line with Product_Id=0 and still have the
Non-nullable column and still can create the FK?
Thanks in advance.
JamesJames,
Could make the column NOT NULL with a DEFAULT constraint of 9999999 or some
number out-of-range for your products.
HTH
Jerry
"James Ma" <JamesMa@.discussions.microsoft.com> wrote in message
news:0829A5B5-7BCE-4508-96E9-24ADA978A88D@.microsoft.com...
> Hi Gurus,
> This seems simple but not to me. Now I want to create a new table that
> contains a column such as Product_Id. I want to create a foreign key
> constraint on Product_Id pointing to a Product table. Sometimes the
> Product_Id can be NULL but I don't want to make the column to be a
> nullable
> column, because that can make query complex and slow. So I like to use 0
> to
> represent NULL. Now I also don't want to insert a Product_Id=0 to the
> Product
> table, because I just worry that can hurt applications or some reports.
> My
> dillema here is that I just can't create the forieign key to protect data
> integrity because the 0 Product_Id may not be able to find a line in the
> Product table. I also have other design choices like this.
> Do you have similar experience? Can any guru here give me a good advice?
> Can
> I avoid to insert a dummy line with Product_Id=0 and still have the
> Non-nullable column and still can create the FK?
> Thanks in advance.
> James|||James Ma wrote:
> Hi Gurus,
> This seems simple but not to me. Now I want to create a new table that
> contains a column such as Product_Id. I want to create a foreign key
> constraint on Product_Id pointing to a Product table. Sometimes the
> Product_Id can be NULL but I don't want to make the column to be a
> nullable column, because that can make query complex and slow. So I
> like to use 0 to represent NULL. Now I also don't want to insert a
> Product_Id=0 to the Product table, because I just worry that can hurt
> applications or some reports. My dillema here is that I just can't
> create the forieign key to protect data integrity because the 0
> Product_Id may not be able to find a line in the Product table. I
> also have other design choices like this.
> Do you have similar experience? Can any guru here give me a good
> advice? Can I avoid to insert a dummy line with Product_Id=0 and
> still have the Non-nullable column and still can create the FK?
> Thanks in advance.
> James
If the FK column can be null then allow null values. From a design
standpoint that's the best option to maintain data integrity, despite
whatever complications this causes with queries.
You can create a view to query the table and replace the NULL value with
whatever you want to send to the application. If no users have rights to
query the table directly you have what you want and have the RI on the
back end that the database demands.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks a lot. I'll use nullable column.
However, I still appreciate if you can help me to clarify. I read many
articles or books that recommend to use a special not null value to represent
null value, since null's behaviour is wierd sometimes (My experiences also
confirm that). So, does that mean it is a better idea to use the dummy line
method if I am designing a database from scratch? When no applications and no
queries have ever been coded?
"David Gugick" wrote:
> James Ma wrote:
> > Hi Gurus,
> >
> > This seems simple but not to me. Now I want to create a new table that
> > contains a column such as Product_Id. I want to create a foreign key
> > constraint on Product_Id pointing to a Product table. Sometimes the
> > Product_Id can be NULL but I don't want to make the column to be a
> > nullable column, because that can make query complex and slow. So I
> > like to use 0 to represent NULL. Now I also don't want to insert a
> > Product_Id=0 to the Product table, because I just worry that can hurt
> > applications or some reports. My dillema here is that I just can't
> > create the forieign key to protect data integrity because the 0
> > Product_Id may not be able to find a line in the Product table. I
> > also have other design choices like this.
> >
> > Do you have similar experience? Can any guru here give me a good
> > advice? Can I avoid to insert a dummy line with Product_Id=0 and
> > still have the Non-nullable column and still can create the FK?
> >
> > Thanks in advance.
> >
> > James
> If the FK column can be null then allow null values. From a design
> standpoint that's the best option to maintain data integrity, despite
> whatever complications this causes with queries.
> You can create a view to query the table and replace the NULL value with
> whatever you want to send to the application. If no users have rights to
> query the table directly you have what you want and have the RI on the
> back end that the database demands.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||James Ma wrote:
> Thanks a lot. I'll use nullable column.
> However, I still appreciate if you can help me to clarify. I read many
> articles or books that recommend to use a special not null value to
> represent null value, since null's behaviour is wierd sometimes (My
> experiences also confirm that). So, does that mean it is a better
> idea to use the dummy line method if I am designing a database from
> scratch? When no applications and no queries have ever been coded?
>
If there is a business case for this, and the value actually means
something, then you can do it. Otherwise, I wouldn't.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"James Ma" <JamesMa@.discussions.microsoft.com> wrote in message
news:9E1AF30B-B432-449D-9540-B541910D9F38@.microsoft.com...
> Thanks a lot. I'll use nullable column.
> However, I still appreciate if you can help me to clarify. I read many
> articles or books that recommend to use a special not null value to
> represent
> null value, since null's behaviour is wierd sometimes (My experiences also
> confirm that). So, does that mean it is a better idea to use the dummy
> line
> method if I am designing a database from scratch? When no applications and
> no
> queries have ever been coded?
>
Opinions differ and there aren't necessarily absolute right or wrong answers
to design questions. In my opinion it does make perfect sense to minimise
the use of nulls wherever feasible. I dislike using nullable foreign keys
for some of the reasons you've mentioned. There is an easy alternative.
Create a new table that has a common primary key with your current one and
then only populate that table where you need to reference a product.
CREATE TABLE your_table (x INTEGER NOT NULL PRIMARY KEY, ....)
CREATE TABLE your_table_product (x INTEGER NOT NULL PRIMARY KEY REFERENCES
your_table (x) , ...., product_id INTEGER NOT NULL REFERENCES products
(product_id))
--
David Portas
SQL Server MVP
--

Foreign key and indexes

I have a question about foreign keys and indexes that I am unsure
about. If a table has a foreign key does this column have a non
clustered index assigned to it as default (that is hidden)? or does it
make sense to add a non clustered index to the foreign key column in
the foreign key table?
I am thinking this as I am unsure how SQL server handles foreign keys.
EXAMPLE BELOW...
DOES TABLE2.Table1ID have a non clustered index that SQL server uses?
CREATE TABLE dbo.Table1
(
Table1Id int NOT NULL,
Foo varchar(10) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
PK_Table1_1 PRIMARY KEY CLUSTERED
(
Table1Id
) ON [PRIMARY]
GO
CREATE TABLE dbo.Table2
(
Table2ID int NOT NULL,
Table1ID int NULL,
barr varchar(10) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table2 ADD CONSTRAINT
PK_Table2 PRIMARY KEY CLUSTERED
(
Table2ID
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table2 WITH NOCHECK ADD CONSTRAINT
FK_Table2_Table1 FOREIGN KEY
(
Table1ID
) REFERENCES dbo.Table1
(
Table1Id
) NOT FOR REPLICATION
GO
ALTER TABLE dbo.Table2
NOCHECK CONSTRAINT FK_Table2_Table1
GOSQL Server does not index foreign keys by default. Usually it does make
sense to create an index on a foreign key.
David Portas
SQL Server MVP
--sql

foreign key - relationship

I have two tables and I'm trying to create a one to many relationship (master table can have many records in the details table)

I created a column in my details table with with ID of the primary key in the master table.

The primary key ID isn't inserted as a foreign key when I insert a record. I specifed the relationship in EM.

Not sure why the primary key ID isn't inserted as a foreign key into my details table?

Any help is greatly appreciated. Thanks.
-Dman100-Explain how you are doing it?

ohhh you do realise you need to insert the value into the table -- it does not do it automatically|||Okay, my mistake, I thought it would be automatic.

Can I do this within my sql statement, using a join or insert or whatever?? to pass the primary key value from the master table into the details table as a foreign key?

Thanks for your help! I appreciate it.
-Dman100-

Foreign Key

I would like to create a foreign key but the Primary table has 2 fields as it Primary Key. Is there a way to create a Foreign Key that links only on one field of the primary key.

Ex: table 1: id int , language char(2), description varchar(100) PK = ID + language

table 2 : id int, idlanguage int PK = id FK (idLanguage refers to id from table 1)

This cause an error because the foreign key does not include all part of the primary key.

Rufen

If your table1.id is unique, then create primary key only on that column, if not, then you should add laguage in your table2 column because there will be no way to distinguish between languages that have the same id.
|||

I know that there will be no way to distinguish all records that have the same id, but that is what I want. When I delete a record from Table 1, I want to delete all record from table 2 that have this id (foreign from table 1).

|||

You can implement the foreign key logic using triggers.

For eg. For Delete

CREATE TRIGGER trg

ON table1

FOR DELETE

AS

BEGIN

DELETE FROM table2

WHERE idlanguage in (SELECT id FROM deleted)

END

You can have similar trigger for insert and update

Foreign key

Hello,
I need to add a column to a table. The new column is a foreign key which references another table's column.

how can you do this? Does it need to be done in two steps, like:

alter table table1
add new_col_name datatype

then

alter table table1
add table constraint.

If so, what is the syntax for adding a foreign key contraint which references another table.

thank youHello,

Yes, it has to be done in two steps, just as you said.

To add a foreign key constraint :

ALTER TABLE table1
ADD CONSTRAINT fk_table1_table2
FOREIGN KEY (field1)
REFERENCES table2(field2);
Where field1 is the column in table1 which is referenced by the column field2 in table2. If you have multi-column FK constraints, just put them in the right order, separated by commas :
ALTER TABLE table1
ADD CONSTRAINT fk_table1_table2
FOREIGN KEY (field11, field12)
REFERENCES table2(field21, field22);

Regards,

RBARAER|||thanks! what does the fk_table1_table2 mean? Is it just a lable?|||It can be done in one step, at least it can on Oracle:
alter table table1
add (new_col_name references table2(keycol));

or (to give the constraint a specific name):

alter table table1
add (new_col_name constraint table1_table2_fk references table2(keycol));|||cool, I'll try that also, but the first code worked.

Foreign key

How do I find out Foreign keys associated with a table?
How do I create Foreign Key when creating a table
using Enterprise manager.
Thank you,
Lin> How do I find out Foreign keys associated with a table?
http://www.aspfaq.com/2520
> How do I create Foreign Key when creating a table
> using Enterprise manager.
I recommend creating tables in Query Analyzer, then you can explicitly
declare foreign keys instead of using a GUI. See CREATE TABLE in Books
Online.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||sp_help <table name> will show you the foreign keys associated with a give
table.
To add foreign key relationships in EM, click on the "Manage
Relationship..." toolbar item when creating a "New Table".
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Lin" <anonymous@.discussions.microsoft.com> wrote in message
news:1bf2f01c4522f$18cc4fc0$a601280a@.phx.gbl...
> How do I find out Foreign keys associated with a table?
> How do I create Foreign Key when creating a table
> using Enterprise manager.
> Thank you,
> Lin
>|||Greg,
Thank you very much, this really helps!
-Lin
>--Original Message--
>sp_help <table name> will show you the foreign keys
associated with a give
>table.
>To add foreign key relationships in EM, click on
the "Manage
>Relationship..." toolbar item when creating a "New
Table".
>
>--
>----
--
>----
--
>--
>Need SQL Server Examples check out my website at
>http://www.geocities.com/sqlserverexamples
>"Lin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1bf2f01c4522f$18cc4fc0$a601280a@.phx.gbl...
>> How do I find out Foreign keys associated with a table?
>> How do I create Foreign Key when creating a table
>> using Enterprise manager.
>> Thank you,
>> Lin
>
>.
>|||Aaron,
Thank you very much!
-Lin
>--Original Message--
>> How do I find out Foreign keys associated with a table?
>http://www.aspfaq.com/2520
>> How do I create Foreign Key when creating a table
>> using Enterprise manager.
>I recommend creating tables in Query Analyzer, then you
can explicitly
>declare foreign keys instead of using a GUI. See CREATE
TABLE in Books
>Online.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>.
>sql

Foreign Key

Hi Friends,
Is there any way to get the table name which is referenced by the
foreign key

for example: consider two table "Staff" and "Department"

Staff with following columns
PK_ID
FK_DepartmentID
Name
Address

Department with following columns
PK_DepartmentID
DeptName

Actually what i need is: Initially i would be having the table name as
"Staff"
from Staff table i need to identify that the column FK_DepartmentID is
a foreign key
and the primary key is in the Department table

i need to traverse from Staff table and identify that FK_DepartmentID
is a primary key in Department table

this has to be accomplished by sql query... probably this could be
fetched from
Data Dictionary but i couldnt find the relationship between the system
tables.

Thanks
ArunDhaJArunDhaJ wrote:

Quote:

Originally Posted by

Hi Friends,
Is there any way to get the table name which is referenced by the
foreign key
(..)

(SQL Server 2005)

IMHO the easiest way is to use sys.foreign_keys. You don't need any
other system view. Try this:

USE YOUR_DATABASE; -- remember about current database context

SELECT
OBJECT_NAME(parent_object_id) as table_with_FK,
OBJECT_NAME(referenced_object_id) as referenced_table
FROM sys.foreign_keys
WHERE OBJECT_NAME(parent_object_id) = 'Staff'

--
Best regards,
Marcin Guzowski
http://guzowski.info

foreign key

Hi

is it a good practice to create FK on each & every column in a table whose values are taken from master table.

should I create or not.

If I create will that be heavy i.e what about memory consumption ?

pls reply

Thanks

Shubhangi1. You can foreign keys.

or

2. You can check through code. By using Inner join table name
on parenttabel.field=childtable.field

ro

3.Before inserting a record in child table, check whether it exists in the parent table through code by using if exists or if (select count(*) from tablename where
...)=0

or

if exists(select 1 from tablename where ...)

Foreign Key

I would like to create a foreign key but the Primary table has 2 fields as it Primary Key. Is there a way to create a Foreign Key that links only on one field of the primary key.

Ex: table 1: id int , language char(2), description varchar(100) PK = ID + language

table 2 : id int, idlanguage int PK = id FK (idLanguage refers to id from table 1)

This cause an error because the foreign key does not include all part of the primary key.

Rufen

If your table1.id is unique, then create primary key only on that column, if not, then you should add laguage in your table2 column because there will be no way to distinguish between languages that have the same id.
|||

I know that there will be no way to distinguish all records that have the same id, but that is what I want. When I delete a record from Table 1, I want to delete all record from table 2 that have this id (foreign from table 1).

|||

You can implement the foreign key logic using triggers.

For eg. For Delete

CREATE TRIGGER trg

ON table1

FOR DELETE

AS

BEGIN

DELETE FROM table2

WHERE idlanguage in (SELECT id FROM deleted)

END

You can have similar trigger for insert and update

sql

foreign key

i want to make primary key and foreign key relationship of table A1 and table B1 but A1 exist in database A and B1 exist in database B
column name u can pretain as C1, C2

'Normally', related tables live within the same database.

The Foreign Key constraint declaration doesn't go outside the scope of the database, so in this case you can't declare a FK constraint.

AFAIK, the option you have to enforce cross-database FK relationships, is by using triggers.

/Kenneth

|||

You can’t create a constraint across the database. But there is a workaround available to fix your issue. Using Instead of trigger / for after trigger. But I recommend to use the Instead of Trigger rather than after trigger..

Code Snippet

Use DB1

Go

Create table A

(

ID int Primary Key,

Name varchar(100)

)

Go

Code Snippet

Use DB2

Go

Create table BB

(

Id int,

[Desc] varchar(100)

)

Go

CreateTrigger BB_Triger

on BBInstead of Insert

as

Begin

Insert Into BB

Select * from Inserted as ins Where Exists (Select 1 From DB1..A a Where a.id = ins.id)

End

/*

--use any one

Create Trigger BB_Triger

on BBAfter Insert

as

Begin

Delete from BB

Where NOT EXISTS (Select 1 From DB1..A a Where a.id = BB.id)

End

*/

GO

Code Snippet

Insert Into DB1..A values(1,'One')

Insert Into DB2..A values(2,'Two')

Code Snippet

Insert Into DB2..BB values(1,'Valid')

select * from BB

Insert Into DB2..BB values(4,'In Valid')

select * from BB

|||Thanks good idea

foreign characters are not being imported into the table correctly

hello everyone,

i have few fields that contain foreign characters with diacritic marks which are not getting imported correctly.

below is the import format:

- File type: ASCII
- Row delimiter: carriage return and line feed {CR/LF}
- Column delimiter: Tab
- Text qualifier: None

Please advice.

Here is the errors i'm getting:

- Executing (Error)

Messages

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Country_str_local_long_name" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task: The "output column "Country_str_local_long_name" (37)" failed because truncation occurred, and the truncation row disposition on "output column "Country_str_local_long_name" (37)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task: An error occurred while processing file "L:\Country.txt" on data row 6.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - Country_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)

Hi,

Have you set the properties for the locale and the default code page?

If you're using a Flat File source, these properties are available in the Flat File Connection Manager Editor dialog box. You open this dialog box by double clicking the Flat File Source control, and then clicking New in the Flat File Source Editor dialog box.

sql

Foreign and primary keys

I have an application in which i need to get the foreign key fields
from a table and then get all the foreign keys primary key field from
the linking table. Could some one tell me how i do this using
INFORMATION_SCHEMA. I have tried and can get the foreign keys but not
sure how to get the associated primary keys.See:
http://groups.google.com/group/micr...a0218d9e069531c

Razvan

foreign and primary key question

OK - I have a two tables in a database. Table one contains an ID, 'oneID', field as the primary key. It is auto-incremented. Table two has ID field, 'twoID', as the primary key. This field also auto-increments. Table two also has 'oneID' as the foreign key.

Now, my question is, how do I get the foreign key in table two to auto-increment in conjuction with table one's primary key? They are after all the same data. Do I have to manually code to get table one data and save it to table two data?

thanks

Yes you'd have to manually INSERT the data into the other table. By setting up the PL-FK constraint you are just setting up a "relation" between the tables so any inserts/updates/deletes into the tables are checked for their data consistency.

Assuming your first INSERT is going through a stored proc, get the ID of the value just inserted via SCOPE_IDENTITY() and immediately do the INSERT into the second table. You could also do this via triggers but I dont recommend it. they are a big performance overhead and drag your system.

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.

Foregein Key data voilation in merge replication

HI,
The situation is like this, merge replication is setup on server(Publisher),
and 4 clients machines and these are subscribers.
I have two table with primary key and foreign key relationship. Both the
tables have primary key with Uniqueidentifier column. First uniqueidnetifer
column is mapped to second table as Foregein key.
here in some situation inserts into primary key and foregein key tables
happyining in the sequence like insert first in Primary Key table, second in
Foregein Key table.
In some situations the process is reversing, like first inserting foregein
key table then primary key table. With this type of activity i am loosing
most important data in teh foregein key tabel. Suggest me in this how can i
proceed.
Regards
Satish
After you make some data inserts on publisher, merge replication is applying
those changes on subscriber, but these modifications can be applied in
different sequence than you originally perfomed on publisher. The common way
to avoid foreign key conflicts is adding of NOT FOR REPLICATION flag to
foreign key constraints. When this flag is set - foreign key constraint is
not checked for replicated data. I suggest you to check Books Online for
more information.
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Satish" <Satish@.discussions.microsoft.com> wrote in message
news:B0F93C30-505A-47FC-B5A4-19ADD5FD94C3@.microsoft.com...
> HI,
> The situation is like this, merge replication is setup on
server(Publisher),
> and 4 clients machines and these are subscribers.
> I have two table with primary key and foreign key relationship. Both the
> tables have primary key with Uniqueidentifier column. First
uniqueidnetifer
> column is mapped to second table as Foregein key.
> here in some situation inserts into primary key and foregein key tables
> happyining in the sequence like insert first in Primary Key table, second
in
> Foregein Key table.
> In some situations the process is reversing, like first inserting foregein
> key table then primary key table. With this type of activity i am loosing
> most important data in teh foregein key tabel. Suggest me in this how can
i
> proceed.
>
> Regards
> Satish
|||Thank you for quick reply. Most of the wesites are telling to create Foreign
Key with NOT FOR REPLICATION option.
I have got one more question that, why merge replication is not inserting
the data in the sequence manner, first Primary key data and then Foreign key
data. Any specific reason in this.
Regards
Satish
"Kestutis Adomavicius" wrote:

> After you make some data inserts on publisher, merge replication is applying
> those changes on subscriber, but these modifications can be applied in
> different sequence than you originally perfomed on publisher. The common way
> to avoid foreign key conflicts is adding of NOT FOR REPLICATION flag to
> foreign key constraints. When this flag is set - foreign key constraint is
> not checked for replicated data. I suggest you to check Books Online for
> more information.
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
> "Satish" <Satish@.discussions.microsoft.com> wrote in message
> news:B0F93C30-505A-47FC-B5A4-19ADD5FD94C3@.microsoft.com...
> server(Publisher),
> uniqueidnetifer
> in
> i
>
>
|||Satish,
This is standard behaviour in SQL Server 2000 and is improved in SQL Server
2005.
Have a look at these articles for more details:
http://support.microsoft.com/default.aspx?scid=kb;[LN];307356
http://support.microsoft.com/kb/308266/EN-US/
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Forecasting TimeSeries from Cube

I've saw many tutorials about using TimeSeries. But all of them using a table. But I'm using a cube to represent data. So I'm trying to build forecast from cube, but it doesn't so good as in could be.
I've got the same problem as desribed in Microsoft's tutoral Adventure work. So I need to forecast a series of sales.
The problem is that I can't create second key value, as it shown in tutorial. So I can't split good's sales. I have created dimentions for goods and for time. So cube's browser shows me very handsome view, but the problem with mining model still remains...
Please, help me! How can I solve this problem?
Can I create a separate table from cube to build forecast by this table?
Or I can solve this problem not using tables?
I posted a solution here which may help.sql

Forecasting in analysis tab cannot view

Hi I installed the add-in for excel data mining but when i try to select a table, in the tab of analyze, dont have any properties. please help me.

carlos of southamerica

Did you select a cell within a table?

Do you see "Table Tools" above the ribbon?

Do you see "Analyze" and "Design" ribbons? (they are usually the last 2 ribbons)

If all answers are yes, what do you see in the Analyze ribbon?

Foreach loop with XML Source failure

I can't import from XML files using a foreach loop. I load an XML file with a generated XSD. When I map the file to the table it has no errors. If I now go back and change to a different XML file, I get an error:

"Error 1 Validation error. Data Flow Task: DTS.Pipeline: input column "COLUMNNAME" (129) has lineage ID 2115 that was not previously used in the Data Flow task. Package.dtsx 0 0"

This is for testing purposes. When I run the foreach loop it does not work. Ironically, I do the exact same thing in another foreach loop with a completely different XML and it works fine.

Here is the broken XSD:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="ComputerStatus">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="computer">
<xs:complexType>
<xs:attribute name="GUID" type="xs:string" use="optional" />
<xs:attribute name="WSUSServer" type="xs:string" use="optional" />
<xs:attribute name="WSUSGroup" type="xs:string" use="optional" />
<xs:attribute name="computerName" type="xs:string" use="optional" />
<xs:attribute name="OSBuild" type="xs:unsignedShort" use="optional" />
<xs:attribute name="OSSP" type="xs:unsignedByte" use="optional" />
<xs:attribute name="Model" type="xs:string" use="optional" />
<xs:attribute name="Make" type="xs:string" use="optional" />
<xs:attribute name="BIOS" type="xs:string" use="optional" />
<xs:attribute name="Processor" type="xs:string" use="optional" />
<xs:attribute name="LastReportedStatus" type="xs:string" use="optional" />
<xs:attribute name="LastSyncTime" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Help. Please. What have I done wrong. I imagine there is a flaw in my XML, but I can't pinpoint it.

Here is a sample of the XML file:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<ComputerStatus>
<computerCount QTY="1" />
<computer GUID="edc2b6a5-5d86-467c-8c89-43fa18ae5921" WSUSServer="WSUS" WSUSGroup="THIS" computerName="COMPUTER" OSBuild="3790" OSSP="1" Model="COMPUTERTYPE" Make="HP" BIOS="1" Processor="x86" LastReportedStatus="10/25/2006 12:00:49 PM" LastSyncTime="10/25/2006 11:57:09 AM" />
</ComputerStatus>

That error says INPUT column, so I doubt its coming from the XML source adapter. An XML source adapter has output columns (and external metadata columns). Are you certain the error is with the source adapter and not some other pipeline component?

As an aside, that XSD and xml will work just fine in without regard the surrounding container. The XSD is not broken so far as use in the SSIS source adapter is concerned, although it does not contain the <computerCount> element.|||

Thank you for the feedback, but I think that I failed to mention that yes, the next thing that I send the XML Source to, whether it be a sort, derived column, an ole db destination, etc... is where the failure shows up.

Take for instance the case where I put the XML Source to an OLE DB Destination. I use a file and set the columns via regular mapping. Then I go back and set the XML Source to another file to be sure it continues to work and I get the error:

Error 1 Validation error. Data Flow Task: DTS.Pipeline: input column "WSUSServer" (5136) has lineage ID 4776 that was not previously used in the Data Flow task. Package.dtsx 0 0

Then I go back into the Ole DB Destination and have it map using Column Names. And everything is okay again. Then go back and switch to the next file and get this error:

Error 1 Validation error. Data Flow Task: DTS.Pipeline: input column "WSUSServer" (5136) has lineage ID 5265 that was not previously used in the Data Flow task. Package.dtsx 0 0

It's a vicious cycle.

An aside, to your aside, I was messing with the XSD and took out the ComputerCount during debug.

Thank you for your help.

sql

Foreach Loop read table data and write to file

Hi,

I want to do the following with a ssis package:

INPUT:

A table contains 2 columns with data i need. column A=Filename and column B=FileContent

PROCESS:

I need to loop through ea record in the table and retrieve columns A and B. Then for ea column i need to write the Content hold in column B into File hold in column A.

I so far found out, that i need a Execute SQL Task in Control Flow querying the table and get columns A and B into 2 variables, plus a 3rd var holding the object. Then the output goes into a Foreach Loop Container. From this point i don't know how to continue. I tried to put a Data Flow Task inside the Foreach Loop, but couldn't find out how i now get the 2 variables to the Data Flow Task and use them to for the file to be written and the content to be placed in the file.

Is there any example similiar to that so i could learn how to start on that?

Thanks

Danny

(Further you can use Import Column transform; in example from here this transform was called File Inserter (in beta release).) - I thought you need insert a file. To export a file you need Export Column transform

|||

The Sample you mention is not exactly what i need. That sample loops through a list of files and writes the names of the files back to a table. Then it has a standard Data Flow Task reading the table with the filenames inserted before and do something with it.

What i need is loops through a table, and for each row i need 2 values from the table to work with in the Data Flow Task. One of the values is the filename to be written and the other value is the content to be written in the file.

|||

You can do in following way :

1. Let's say you want to put the files in c:\YourFolder, add a data flow task and connection to your table

2. Add a derived column transformation; make a derived column name NewFilePath and in expressions :

"C:\\YourFolder\\+(DT_WSTR,50)ColumnA"

3. Add an Export Column transformation; in Export Column transformation editor set

Extract Column= ColumnB

File Path Column=NewFilePath

so SSIS will get the file from columnB and put in the folder using NewFilePath

ForEach Loop or For Loop?

I have source and destination table names in the database(one table) and I need to read the source and destination tables one by one...

My Lookp table is like the following...

Srn srctable desttable

1 SRC1 DEST1

2 SRC2 DEST2

3 SRC3 DEST3

Now I want one package to load from source to destination.. how do I do it.. I dont know how to use....

How do I run the pacakge for each of the rows... ..............................

Is the metadata the same for each data transfer? If not then you're going to have to build as many data-flows as there are rows in your lookup table and if that is teh case - what's the point in looping over them?

-Jamie