How To Create Reference For Composite Key
Oct 29, 2007
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.
View 4 Replies
ADVERTISEMENT
Nov 24, 2006
The table above is my users table. It allows for a user to be at multiple sites or multiple locations within a single site or multiple sites. Would it be wise to use a auto incrementing primary key instead of the 3 column composite key? The reason I ask is because if I am referencing this SU table (which I will be a lot), a lot more data would be replicated to the tables which have the foreign key to this table, right? But if I used a single incrementing column as the primary key, only a small integer would be used as the foreign key, saving space?Does this make sense?
View 1 Replies
View Related
May 4, 2005
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
View 3 Replies
View Related
Jul 24, 2014
I am trying to create a FK using a composite PK and here are the details that I want to achieve.
Table -A
Column A1 not null,
Column A2 not null
Primary key (A1, A2).
Table -B
Column B1 Primary key.
Column B2 not null
FK (B2) References A(A1).
When I try to do this I am getting some errors. Questions: First of all is this possible? if yes, then how I can create it.
View 1 Replies
View Related
May 5, 2008
I am trying to create a Unique Constraint on a SQL Server 2005 table where the uniqueness is based on 2 columns.
Could anybody provided some help on how I could enforce this on an existing table (link, or example) I have been looking around without luck.
Thanks in advance
John.
View 4 Replies
View Related
May 22, 2007
Hi All
I hope someone smart can help me, it would be highly appriciated.
I am developing an SQL Serverdatabase and in on of the tables I need the primary key to consist of two pieces of data.
TblOrders: OrderNum, Orderdate, ....
TblDispatchers: DispatcerID, Dispatcher, DispatcherAddress
The OrderNum field in TblOrders need to be a composite of an AutoNum-field (incrementet by 1) and the DispatcerID from the tblDispatchers.
Can this be done, and how.
Many thanks
Kind regards
Tina
View 3 Replies
View Related
Feb 20, 2007
Hello,
I have a table which has a composite primary key consisting of four columns, one of them being a datetime called Day.
The nice thing afaik with this composite key is that it prevents duplicate entries in the table for any given day. But the problem is probably two-fold
1. multiple columns need to be used for joins and I think this might degrade performance?
2. in client applications such as asp.net these primary keys must be sent in the query string and the query string becomes long and a little bit unmanagable.
A possible solutions I'm thinking of is dropping the existing primary key and creating a new identity column and a composite unique index on the columns from the existing composite key.
I would like to have some tips, recommendations and alternatives for what I should do in this case.
View 1 Replies
View Related
Jan 14, 2008
I'm trying to create two tables, Admins and SecurityGroups, I want to have the ability to assign a security group to an admin so that I don't have to specify all the rights individually to every admin. So, I have the securityGroupID in the Admins table and I reference the SecurityGroups table for data integrity. On the other hand, for audit purposes, I also want to record which admin created the security group so I have the AdminID in the SecurityGroups table and I reference the Admins table for data integrity. Of course when I try to create any of those tables I get an error message that the other table doesn't exist. I wonder if this can be done without having to remove the references constraint from one of the tables and then using alter table. Here's an example below to make it clearer:
CREATE TABLE Admins
(
AdminID INT NOT NULL PRIMARY KEY,
SecurityGroup NULL REFERENCES SecurityGroups (SecurityGroupID)
)
CREATE TABLE SecurityGroups
(
SecurityGroupID INT NOT NULL PRIMARY KEY,
CreatedBy INT NOT NULL REFERENCES Admins (AdminID)
)
Any suggestions are highly appreciated...
View 7 Replies
View Related
Jul 23, 2005
Hello all,I have two tables - Projects and ProjectStructTable Projects contains master records of the projects, ProjectStructallows to define a project herarchie and contains the fieldsPrjStructId, ProjectId, PrjStructName, ..., ParentIdPrjStructParent contains a reference to the parent or to itselves ifrecord is top-level-record for a project.I try to create a trigger on table Projects (INSERT) whichautomatically creates the top-level-entry in ProjectStruct but Ididn't succed.Tried to use (several variations similar to)INSERT INTO ProjectStruct (ProjectId, PrjStructName, ParentId)SELECT prjProjectId, 'top-level',IDENT_CURRENT('ProjectStruct'))FROM INSERTEDbut this inserts a reference to the last inserted record. Why thishappens is pretty clear to me, but I found no way to get the referenceto the identity column of the record currently inserted.Is there a way to do this?
View 7 Replies
View Related
Nov 6, 2007
I surfed a lot of the internet piecing this together. I have a database that gets copied every night from a live database so users can run queries against it, so I needed to be able to recreate a stored procedure from within ASP.NET. Also, I needed to take information I was importing from a .CSV file and compare it using the stored procedure to find specific information and import it into another database. I was able to do this using the SQLDMO reference object.
Here are some clipits of what you need.
Imports SQLDMOPrivate myServer As New SQLServer
Dim serverName As String = System.Configuration.ConfigurationManager.AppSettings("Server").ToString()Dim userName As String = System.Configuration.ConfigurationManager.AppSettings("UserName").ToString()
Dim password As String = System.Configuration.ConfigurationManager.AppSettings("Password").ToString()
Dim storedProcedure As New StoredProcedure
Dim qresults As SQLDMO.QueryResults
Example Stored Procedure
Try
myServer.Connect(serverName, userName, password)
storedProcedure.Text = "IF EXISTS (SELECT * FROM sysobjects WHERE name='memb_proc' AND user_name(uid)='dbo') DROP PROCEDURE dbo.memb_proc"myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure)
storedProcedure.Text = "CREATE PROCEDURE dbo.memb_proc @lastname varchar(50) = '', @firstname varchar(50) = '', @zip varchar(5) = '', @membrowno varchar(50) OUTPUT AS Select @membrowno = rowno from memb where lastname LIKE @lastname and name LIKE @firstname and zip1 LIKE @zip "myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure)
Label1.Text = "Creation of stored procedure successful"Catch ex As Exception
Label1.Text = "Creation of stored procedure failed"
Finally
myServer.DisConnect()End Try
Run Stored Procedure and Obtain Results
myServer.Connect(serverName, userName, password)
qresults = myServer.ExecuteWithResults("USE [databasename] DECLARE @return_value int,@membrowno varchar(50) EXEC @return_value = [dbo].[memb_proc] @lastname = N'" & Last & "', @firstname = N'" & First & "',@zip = N'" & ZIP & "', @membrowno = @membrowno OUTPUT SELECT @membrowno as N'@membrowno'")For num = 1 To qresults.Rows
MemberRowNo = qresults.GetColumnString(num, 1)
Next
myServer.DisConnect()
View 3 Replies
View Related
Jun 28, 2006
When I run this ...
CREATE ASSEMBLY asmRANDRTCalendaringGateway FROM 'E:sqlRANDRTCalendaringGateway.dll' WITH PERMISSION_SET = UNSAFE
I get this error ...
Msg 10301, Level 16, State 1, Line 1
Assembly 'RANDRTCalendaringGateway' references assembly 'system.web, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.
Even though I do not explicity refer to system.web in the DLL's code ...
using Microsoft.Win32;
using System;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Configuration;
using System.Text;
using log4net;
using ADODB;
using MAPI;
using CDO;
using System.Runtime.InteropServices;
Is the problem just staring me in the face ?
cheers
View 14 Replies
View Related
Aug 14, 2007
I was hoping to instantiate a channelfactory from a clr stored proc in order to send messages to a wcf endpoint but VS 2005 wont allow refs to anything other than what it calls "SQL Server" targets. Is calling a wcf endpoint from clr sp's possible? I've alreay read the article on serialization under ss2005 but dont believe it has the answer to this particular piece of the puzzle.
View 4 Replies
View Related
Sep 10, 2014
I am using vs 2010 to write my dtsx import scripts.I use a script component as a source to create a flat file destination file.Everything have been working fine,but then my development machine crashed and we have to install everything again.Now when i use the execute package utility to test my scripts i get the following error:
Error system.NullReferenceException: Object refrence not set to an instance reference.
In PreExecute section
TextReader = new system.io.streamreader(" file name")
In the CreateNewOutputRows:
dim nextLine as string
nextLine = textReader.ReadLine
[code]...
is there something which i did not install or what can be the error?
View 0 Replies
View Related
May 22, 2008
Just installed VS 2005 & SQLServer 2005 clients on my workstation. When trying to create a new Integration Services Project and start work in the designer receive the MICROSOFT VISUAL STUDIO 'Object reference not set to an instance of an object.' dialog box with message "Creating project 'Integration Services project1'...project creation failed."
Previously I had SQLServer 2000 client with the little VS tool that came with it installed. Uninstalled these prior to installing the 2005 tools (VS and SQLServer).
I'm not finding any information on corrective action for this error.
Any one have this problem and found the solution?
Thanks,
CLC
View 1 Replies
View Related
Sep 23, 2006
I'm just now learning both SQL and ASP.NET, and I cannot seem to figure out how to build my data structure. I believe the answer to my problem is a composite key, but I cannot seem to get it to work. Here is an example. My database is of recorded dances, with exact locations within a ballroom. I believe I need 2 tablesTable #1 - DanceTableColumns: DanceID, Name, Description, TagsTable #2 - StepsTableColumns DanceID, StepID, longLocation, latLocation, Action, DescriptionWithin my ASP.NET application I want to be able to enter data about a dance, including metadata and a series of steps. The Dance and metadata content to be stored in DanceTable, and the series of moves stored in the StepsTable. I want the steps to be IDed as 1, 2, 3, 4...x with the first step being labled 1. and I want each dance to have it's own unique ID (DanceID). Right now I'm using "ExecuteNonQuery()" to add my data to my SQL database, and when I add new steps to the StepsTable SQL just finds the largest ID within StepID and increments it by one. So my steps are labeled as:Dance1:Step1, Step2, Step3, Step4Dance2:Step5, Step 6, Step7What I really want is (or I think what I want is) is a composite primary key.Dance1:Step1, Step2, Step3, Step4Dance2:Step1, Step2, Step3That way the StepID is used as both a primary key and it indicates the position within the dance. I guess I could just use a standard SQL table, let SQL auto generate StepID's and then add a new column called something like "StepNumber", but it just seems goofy to be generating a stepID and never using it. With composite keys (If I understand them) each step would have a unique key as a combination of the DanceID+StepID (AKA Dance 345, steps 1-10). I pull up data by searching for dances, and then sort by StepNumber, and those should all be unique...if I can figure out how to build them.
View 1 Replies
View Related
Nov 9, 2006
I have a table with a composite key formed by the unique combination of columns w, x, y, z
I'm trying to write an INSERT statement along the following lines
INSERT INTO myTable
(SELECT w, x, y, z FROM someTable) t1
WHERE (this is the part I'm stumped on - where the unique combination of w, x, y, z is NOT in myTable already)
Help would be appreciated. Can you use the NOT IN keyword on composite values?
View 2 Replies
View Related
May 29, 2008
How can I create a composite key (two primary keys for one table) using SQL Server 2005 Management Studio? Can I do it using the GUI, or do I have to write SQL code to do this? Thanks
View 7 Replies
View Related
Apr 19, 2004
Made an interesting discovery today - a column of data type Bit is not allowed to participate in composite key. Rather surprised.
I have a certain unique code that exhibits 2 unique states, which rendered the use of the boolean column, so the uniqueness goes <code>-1 and <code>-0. Is there any 'hack' so to speak to avoid using a Tinyint for the boolean column instead?
View 5 Replies
View Related
Jun 10, 2005
Hi,
Suppose there is a composite index on a table, and in includes, for example 3 columns.
If I do a select using one of those three columns, will the select use the composite index? Will it aid in retrevial or should I create an index on the
individual column.
any thoughts on this would be appreciated.
thanks
View 2 Replies
View Related
Sep 24, 2004
Can someone help me create a composite key on ms sql server through the enterprise manager?
Thanks,
Laura
View 1 Replies
View Related
Aug 21, 2004
The orininal design of my db (part of it...) is the following
A JOB has a Number and a Description.
Each JOB can have one or two TASKS (min one, max two). Each TASK is identified by the JOB it belongs to and an Index (unique only for the same JOB).
Each TASK has one an only one set of INFO1, one and only one set of INFO2, one and only one set of INFO3 etc.
A: JOB (JobNum [PK], JobDescription, ...)
B: TASK (JobNum [PK] [FKa], Index [PK], TaskDescription, ...)
C: INFO1 (JobNum [PK] [FKb], Index [PK] [FKb], ...)
D: INFO2 (JobNum [PK] [FKb], Index [PK] [FKb], ...)
(There is a reason to keep INFO1, 2 and 3 separate, because eachof them will be linked to different table. This might influence the answer to my real question.)
First of all, I wouldn't add any surrogate key for TASK, not to loose the logic behind; plus I'd put an ined on JonMum only, being Index equal to 1 or 2 only, so not selective.
The real question is about INFO1 (and 2, 3 etc.) table: should I leave JobNum and Index as PK (consider that the PK of INFo1 will be used as FK for another table), or should I use a surrogate key, like for eaxmple
C: INFO1 (Info1ID [PK], JobNum [FKb], Index [FKb], ...)
I don't really like this solution. Actually I'd prefer the following
C: INFO1 (Info1ID [PK], ...)
where Info1ID = JobNum + Index (+ = string concatenation).
Any suggestion?
Thanks
View 3 Replies
View Related
Feb 11, 2005
Hi folks, i need an advise.
I've a gerand table customers_orders table with customer_id and order_id.
Whenever we have to find orders, for customer, this table is involved. Hey; i know u'll be angry y the heck this gerand exist but i've to blame the older dudes then.
Now this table has composite clustered index; CUSTOMER_ID+ORDER_ID.
The tables have grown over GB size; i see HASH INNER JOIN rather than MERGE for the GEREND and CUSTOMER table join.
Is it good to use composite clustered index; or should i clustered one the columns in the GEREND and other to normal index. What performance impact it could be.
Howdy!
View 2 Replies
View Related
Mar 2, 2004
WHile designing a Database should one go for composite Primary Keys.
Or what are the Pros and Corns of Composite Primary Keys
Thanx
View 5 Replies
View Related
May 11, 2004
Hello, everyone:
I need to define composite PK and FK for a ERD. Could someone offer the methods that work with,
1. T-SQL
2. ERD
Thanks a lot.
ZYT
View 4 Replies
View Related
Feb 24, 2014
I've been facing this situation since long but today i am asking here. Suppose i have a following tables;
AdmissionInfo, AdmID, AdmDate, AdmFee etc.
SubjectInfo i.e. SubID, SubName, SubStatus etc.
The Result table is like this:
ResID, AdmID, SubID, TheoryMarks, PracticalMarks, ObtMarks, TotalMarks, ResultTerm, SubPercentage.
ResID is PK, AdmID and SubID are Foreign Keys, right. Now What i want to ask/ learn that Am i suppose to combine ResID, AdmID and SubID as a composite PK or should i simply make the ResID as PK and other 2 be there as FK? When do we really need to use Composite PK and using it a good thing or not?
View 1 Replies
View Related
Jul 20, 2005
i have a master table with around 15 columns and i am trying to findthe appropriate primary keys and indexes for the table.To make the records unique, i need to include two datetime columns (start and end dates ) and two integer columns ( attributes of therecord ) to make up a composite primary key. Both of these fourcolumns are found in the WHERE clause of my queries.Is it acceptable in the view of performance and how should i createthe indexes?
View 5 Replies
View Related
Jul 3, 2006
This is really not a T_SQL question but there's no good category for it so I thought I'd just put it here.
I have a table that will contain a large amoutn of data in one field, and every piece of this data is uniquely identitied by six other attributes, that is, six other fields (e.g. user_ID, type_ID, year, country, state, item_ID). I can either make these six fields into a composite primary key, or add an additonal field (say an identity column) as the primary key and add a unique constraint on these six fields. What are the pros and cons of both designs? The one data field is of nvarchar(2000) type and the table is likely to have 50 million+ rows in a couple of years of real use.
This table is not referenced by any other tables so whatever the primary key is, there's no FK reference. However, there could be FK references to this table in the future. Does the FK possibility make a difference in the design considerations?
View 13 Replies
View Related
Aug 4, 2007
Hello,
Does composite primary key affect performance on the table that contains the composite primary key or tables that references this table?
When composite primary key should be used?
View 2 Replies
View Related
Jun 6, 2008
Hi What is the difference between Candidate key , composite key and alternate key. I went through many websites but I didn't get examples. There were only definitions. Can anyone please tell me the site or blogs that elaborate this concept RegardsKaran
View 2 Replies
View Related
Feb 2, 2004
Hi
I'm trying to create a composite Primary Key on a table. This is the SQL I've written:
CREATE TABLE BookingItems
(
BookingID INT NOT NULL
REFERENCES Bookings(BookingID),
EquipmentTypeID INT NOT NULL
REFERENCES EquipmentType(EquipmentTypeID),
CONSTRAINT PK_BookingItems_id PRIMARY KEY
(BookingID, EquipmentTypeID)
)
Is this right? I'm trying to define a Primary Key made up of BookingID and EquipmentTypesID, which are both Foreign Keys as defined in the column definition.
Thanks
Jon
View 1 Replies
View Related
Jul 18, 2005
I have several tables where I have used the command
distinct first_name, last_name, dob
to unduplicate.
I was wondering if I could create a new column in my table that combines the existing records for first_name, last_name and dob?
View 4 Replies
View Related
Jul 9, 2006
The requirement in to write a query which will return top 2 rows (in terms of lst_updt_timestamp column) for every combinations of cust_alias_nm, carrier_cd, acct_nbr columns.
Here I wrote a query which selects top 1 only.
Please help me to write to get the top 2nd along with the top 1st row.
select A.cust_alias_nm_id,
A.carrier_cd_id, A.acct_nbr_id,
sum(A.pd_clm_amt) clm_amt,
sum(A.pd_med_amt) med_amt,
sum(A.pd_exp_amt) exp_amt,
A.lst_updt_timestamp
from bal_load_stg A
group by A.cust_alias_nm_id, A.carrier_cd_id, A.acct_nbr_id, A.lst_updt_timestamp
having A.lst_updt_timestamp
in (
(select max(lst_updt_timestamp) from bal_load_stg B
where
A.cust_alias_nm_id = B.cust_alias_nm_id
and A.carrier_cd_id = B.carrier_cd_id
and A.acct_nbr_id = B.acct_nbr_id))
The output looks like:
cust_alias_nm_id carrier_cd_id acct_nbr_id clm_amt med_amt exp_amt lst_updt_timestamp
---------------- ------------- -------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ------------------------------------------------------
aaa 101 1234567891 500.00 500.00 500.00 2005-07-31 00:00:00.000
aaa 102 1234567891 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 100 1234567890 700.00 700.00 700.00 2005-10-31 00:00:00.000
The desired output should look like:
cust_alias_nm carrier_cd acct_nbr clm_amt med_amt exp_amt lst_updt_timestamp
---------------- ------------- -------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
aaa 100 1234567890 700.00 700.00 700.00 2005-10-31 00:00:00.000
aaa 100 1234567890 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 102 1234567891 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 101 1234567891 500.00 500.00 500.00 2005-07-31 00:00:00.000
aaa 101 1234567891 400.00 400.00 400.00 2005-05-31 00:00:00.000
All rows present in the table are
cust_alias_nm carrier_cd acct_nbr clm_amt med_amt exp_amt lst_updt_timestamp
---------------- ------------- -------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
aaa 100 1234567890 700.00 700.00 700.00 2005-10-31 00:00:00.000
aaa 100 1234567890 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 102 1234567891 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 101 1234567891 500.00 500.00 500.00 2005-07-31 00:00:00.000
aaa 100 1234567890 400.00 400.00 400.00 2005-05-31 00:00:00.000
aaa 101 1234567891 400.00 400.00 400.00 2005-05-31 00:00:00.000
Thanks in advance.
View 3 Replies
View Related
Jun 25, 2002
Newbie question...
I have two tables (categories & listings) which create a many-to-many relationship.
I have created an interim table with the primary keys from each table as a composite primary key...(cat_id & list_id).
How does the interim table get populated with the id's?
When I do an insert statement to insert data into the categories table, the cat_id field is automatically generated...same with the listings table, but when (and how) does the primary key data get into the interim table.
Thanks in advance for the assistance.
View 1 Replies
View Related