How to Create reference for Composite key

Hi All,

Can anyone tell me how to create a reference for composite key.

For ex, I have created tblEmp table successfully.

create tblEmp

(

empId varchar(100),

RegId varchar(100),

empname varchar(100),

constraint pk_add

primary key(empId, RegId)

)

And now, I am going to create another table which references the composite key.

create table tblAccount

(

acctId varchar(100) primary key,

empId varchar(100) references tblEmp(empId),

RegId varchar(100) references tblEmp(RegId)

)

But it gives error like

Server: Msg 1776, Level 16, State 1, Line 1
There are no primary or candidate keys in the referenced table 'tblEmp' that match the referencing column list in the foreign key 'FK__tbl'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

Could anyone please let me know how to create reference for composite key.

Thanks in advance,

Arun.

The below code will do:

create table tblEmp( empIdvarchar(100), RegIdvarchar(100), empnamevarchar(100),constraint pk_addprimary key(empId, RegId) )create table tblAccount( acctIdvarchar(100)primary key, empIdvarchar(100) , RegIdvarchar(100),constraint fk_tblAccountforeign key ( empId, RegId)references tblEmp( empId, RegId) )

In case you've already created both tables, you can use below query to set the foreign key in the account table.

alter table tblAccountadd constraint fk_tblAccountforeign key ( empId, RegId)references tblEmp( empId, RegId)

Hope this will help.

|||

Hi,

We can also do it by

create tblEmp

(

empId varchar(100),

RegId varchar(100),

empname varchar(100),

primary key(empId, RegId)

)

And now, I am going to create another table which references the composite key.

create table tblAccount

(

acctId varchar(100) primary key,

empId varchar(100) ,

RegId varchar(100) ,

foreign key(empId,RegId) references tblEmp(empId,RegId)

)

It works perfect

|||

arunkumar.niit:

primary key(empId, RegId)

arunkumar.niit:

foreign key(empId,RegId) references tblEmp(empId,RegId)

Hey, your queries are exactly the same as what I've posted except one thing that I've given my own names to the constraints and you've left it over the SQL Server.

|||

Hi,

Thanks for your reply.

Thanks & Regards,

Arun.

how to create key time column and key column for a case table and a nested table for time series

Hi, all experts here,

Thanks for your kind attention.

I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?

Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Can you please clarify if you're creating an OLAP mining model or a relational mining model?

Thanks

|||

Hi, Shuvro,

At the moment I am trying a create a relational mining model.

But it will be brilliant to hear from you for both how to create an OLAP mining model as well using case table and nested table with Time Series Algorithm.

Thanks and I am looking forward to hearing from you further for your advices.

With best regards,

Yours sincerely,

|||

Can you please post the schema of your case and nested table for the relational scenario?

For the OLAP mining model, the design would be on the same lines as the relational, but instead using the dimension attribute and the measure. The TIME KEY usually can be selected from your date dimension, depending on the granularity of your data, but you'll have to do some work to make it correctly sortable. (e.g. Month names are not a valid time key).

|||

You can mine just the fact table and ignore the date table. The fact table needs to have the date value anyway. Your model would look something like this

CREATE MINING STRUCTURE TS_Structure

(

[Date] LONG KEY TIME,

[Region] TEXT KEY,

Value1 DOUBLE CONTINUOUS PREDICT,

Value2 DOUBLE CONTINUOUS PREDICT

)

ALTER MINING STRUCTURE ADD MINING MODEL TS_Model

(

[Date],

[Region],

Value1 PREDICT,

Value2 PREDICT

) USING Microsoft_Time_Series

When using the tools, you would simply mark your source table as the "Case" table (not nested) and then mark both the [Date] column and the [Region] column as keys. The tools will figure it out.|||

Hi, Jamie,

Thanks for the advices.

With best regards,

Yours sincerely,

how to create dynamic columns in a temporary table

Hi there,
i have a requirement that a temporary table contains dynamic columns depending on where condition.

my actual table is like

Key Value X1 x X3 x X5 x Y1 y Y2 y

when user select x, the input variable passed to stored proc and the result is shown like

column names
X1 X3 X5 as column headers.

the select query is from temporary table.

these out put is based on the user selection. so the temporary table created with columns dynamically.

please help me out.
please let me know if you didn't understand.

thanks
Praveen.

Here the sample script,

Code Snippet

use tempdb

go

Create Table data (

[Key] Varchar(100) ,

[Value] Varchar(100)

);

Insert Into data Values('X1','x');

Insert Into data Values('X3','x');

Insert Into data Values('X5','x');

Insert Into data Values('Y1','y');

Insert Into data Values('Y2','y');

Code Snippet

create table #temp(dummy bit);

Declare @.Script as Varchar(8000);

Declare @.Script_prepare as Varchar(8000);

Set @.Script_prepare = 'Alter table #temp Add [?] varchar(100);'

Set @.Script = ''

Select

@.Script = @.Script + Replace(@.Script_prepare, '?', [Key])

From

data

Where

[Value] = 'X'

Exec (@.Script)

Alter table #temp drop column dummy;

Select * from #temp;

drop table #temp

|||Hi sekaran,
very nice and thanks alot.

but, i don't know that [key]. it's not static.
it is based on the result from a select query.
how to replace that '?' with the key, i need to write some other logic to get that key...

thanks
PRaveen.|||Can you post more information like result query & etc.?

How to create an table with composite Key?

Hi all,

well i want to have an web-based database application in which in an single webform , i need to insert the values to 5 different tables.

here empid is primary key (PK) in one table and in the rest of the tables its an Foreign Key(FK)

my question is, how can i create these tables with composite key?

ie, i want to creat an table which will have FK and as well another key which both will uniquely idenitfies a row.

Well i am using SQL server 2000 and i wish to create these tables using the enterprise manager.

here are the tables

1) empidtable

empid,fname,lname

2)empcountrytable

empid,country

3)empvisatable

empid,visa

4)empdoctable

empid,doc

5)empfile

empid,filename,filecontenttype,filesize,filedata

Plz do help me

thanx in advance

If you're using Enterprise Manager, go to Design View of the table anduse control + click to select all the columns you want as part of thecomposite key, then right-click Set Primary Key.
|||The table with the composite key will have all the primary key columns of the five tables because a composite index must be on one table. You can have up to 16 columns and 900bytes so if you are using unicode you can only have NVARCHAR (450). The alternative which is used by Microsoft Consulting is to create a small Clustered index and add the column of the clustered index in all your nonclustered index on the table. You can use the index tuning Wizard which is part of the profiler to tell you the indexes you need on a table. Hope this helps.|||

Hey thanks for that.

will try to implement the same

how to create an copy of a certain record except one specific column that must be different &

Hi
I have a table with a user column and other columns. User column id the primary key.

I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key

Thanks.

try this:

insert into your_table ( user , field2 , field3 , ... )

select 'user2', field2 , field3, ....

from your_table

where user = 'user1'

|||

I WANTED TO AVOID SPECIFYING ALL THE COLUMNS SINCE THERE ARE MANY COLUMNS.

THANKS.

|||

Here is a little trick

in Query Analuzer Or SSMS press F8, this will display the Object Explorer

Drill down to the table that you need, click on the Columns folder (hold the button down) and drag it into the query window

Now all your columns will be listed in the query window, just exclude the one that you don;'t want

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Beautiful trick thanks a lot for sharing it

how to create an copy of a certain record except one specific column that must be different

Hi
I have a table with a user column and other columns. User column id the primary key.

I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key

Thanks.

try this:

insert into your_table ( user , field2 , field3 , ... )

select 'user2', field2 , field3, ....

from your_table

where user = 'user1'

|||

I WANTED TO AVOID SPECIFYING ALL THE COLUMNS SINCE THERE ARE MANY COLUMNS.

THANKS.

|||

Here is a little trick

in Query Analuzer Or SSMS press F8, this will display the Object Explorer

Drill down to the table that you need, click on the Columns folder (hold the button down) and drag it into the query window

Now all your columns will be listed in the query window, just exclude the one that you don;'t want

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Beautiful trick thanks a lot for sharing it

How to create a constraint like this:

Hi,

Suppose the following table definition in Sql Server 2005,

create table CompanySymbol
CompanyId int,
SymbolId int,
IsPrimarySymbol bit

Primary Key (CompanyId, SymbolId)

How can I create a constraint which wil ensure that IsPrimarySymbol will be set to 1(true) only once per CompanyId while allowing it to be set to 0 an unlimited amount of time per CompanyId. i.e.:

CompanyId SymbolId IsPrimarySymbol
--
1 1 1 (IsPrimarySymbol to 1 for CompanyId 1)
1 2 0
1 3 0
2 1 1 (IsPrimarySymbol to 1 for CompanyId 2)
2 2 0
3 1 0
4 1 1 (IsPrimarySymbol to 1 for CompanyId 4)
4 2 0
4 3 0

Thanks

Your best bet is to use a trigger to enforce this rule. It can be coded to either reject any Insert/Update that leaves the table with two rows flagged as IsPrimarySymbol. Alternatively, it could be coded to zero out all but the latest record set to IsPrimarySymbol. Be sure to consider Inserts and Updates of more than one record at a time.

It can be accomplished with a Constraint, but you need to also add a computed column.

create table CompanySymbol
CompanyId int,
SymbolId int,
IsPrimarySymbol bit
Guard as Case when IsPrimarySymbol = 1 Then 0 else SymbolId End

Now you can create a Unique Constraint( CompanyId, Guard )

My vote is for the trigger.

|||

Thanks for the answer, this should do the trick.

how to create "unsafe assembly"?

Greetings...

I'm trying to create an "unsafe assembly":
USE master
GO

CREATE ASYMMETRIC KEY StoredProcedures_dll_Key
FROM EXECUTABLE FILE = 'C:\Documents and Settings\All Users\Documents\hunter\StoredProcedures.dll';

CREATE LOGIN StoredProcedures_dll_Login
FROM ASYMMETRIC KEY StoredProcedures_dll_Key;

GRANT EXTERNAL ACCESS ASSEMBLY TO StoredProcedures_dll_Login;
GO

USE gfx_sa
GO

CREATE ASSEMBLY hunter_storedProcedures
FROM 'C:\Documents and Settings\All Users\Documents\hunter\StoredProcedures.dll'
WITH PERMISSION_SET = UNSAFE;
GO

CREATE PROCEDURE [dbo].[hunter_storedProcedure1]
(
@.symbol_id as int
)
AS EXTERNAL
NAME hunter_storedProcedures.StoredProcedures.StoredProcedure1
GO

But on 'CREATE ASSEMBLY" clause I'm getting error:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

Assembly is signed by Visual Studio.

What's wrong?

Best regards...

This is caused by the same problem as explained in KB 918040: http://support.microsoft.com/default.aspx/kb/918040

The same workaround will solve your problem: EXEC sp_changedbowner ‘sa’ (or any other login that you know exists on the server).

If you upgrade to the latest service pack, then you should get the error message listed in the KB rather than the 'A severe error occurred...' message.

Steven