create table z
(
eno int,
deptno char(2),
ename varchar(5),
constraint pk_eno_deptno primary Key(eno,deptno)
)
and I tried to establish a foreign key based on the above defined table. It's giving error,
create table z_dup
(
eno int FOREIGN KEY REFERENCES z(eno),
deptno char(2)
)
go
Error:-
*****
There are no primary or candidate keys in the referenced table 'z' that match the referencing column list in the foreign key 'FK__z_dup__eno__18CE1BA6'.Could not create constraint. See previous errors.
Even I tried to create a foreign key like the following,
create table z_dup
(
eno int FOREIGN KEY REFERENCES z(eno,deptno),
deptno char(2)
)
go
Error:-
********
More than one key specified in column level FOREIGN KEY constraint, table 'z_dup'.
Can anyone please help me to create a foreign key relation with the base table.
Hi, In my table1, I have a composite primary key ssn+firstname+lastname. In table2 I have ssn, firstname, lastname, col1. In table2 I want to create a foreign key which references table1 primary key? table1 and table2 has the primary, foreign key relationships. Still table2 need primary key. In table2 Can I make the ssn+Firstname+Lastname as primary key? or one column id as auto increment?
Hi all, In my project i will have the data in a collection of objects, I need to update series of tables with foreign key relations Right now my code looks like this foreach(object obj in Objects){ int accountId=Account.Insert(obj.accountOpenDate,obj.accountName);//this will update the accounts table and returns account id which is a Identity column in Acccounts table int DebtId=Debt.Insert(accountd,obj.debtamount,obj.debtbalance); this will update the Debts table and returns DebtId ///series of tables like above but all the relevant data comes from obj and in the Insert Methods i am using stored procedures to Insert the data into table } The no of objects varies from 1000 to 1 milliion,, with this approach its taking more time to update all the data. Please let me know if any alternative procedure to handle this kind of scenario.
I have two tables: table 1 and table 2. The primary key of table is composite key of two collumns and table 2 is the child of parent table 1. Is it possible to create / define a foreign key constraint using a composite primary key?
Is there anyway to get the order in which data to be import on to tables when they have primary and Foreign Key relations?
For ex:We have around 170 tables and when tries to insert data it will throw error stating table25 data should be inserted first when we insert data in table 25 it say 70 like that.
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.
Uma writes "Hi Dear, I have A Table , Which Primary key consists of 6 columns. total Number of Columns in the table are 16. Now i Want to Convert my Composite Primary key into simple primary key.there are already 2200 records in the table and no referential integrity (foriegn key ) exist.
may i convert Composite Primary key into simple primary key in thr table like this.
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.
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)?
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?
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?
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?
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.
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.
My previous post was not really clear, so I'll try again with a (hopefully) better (even if longer) example...
Consider the following...
A JOB describes the processment of a document. Each document can exist in two versions: English and French. A JOB can have 1 or 2 TASK, each describing the processement of either the English or French version. So we have the following:
that is there is an identifying 1:M (where maxium allowed for M is 2) relationship between JOB and TASK; TASK being identified by JobNum and Version (where the domain for Version is {E, F}).
Each TASK may require a TRANSLATION sub_task. Each TASK may require a TYPING sub_task. Each TASK may require a DISTRIBUTION sub_task.
For example, for a given doc, the English TASK requires TRANSLATION and DISTRIBUTION, while the French only DISTRIBUTION.
That is, there is a 1:1 not-required relationship between TASK and TRANSLATION, TYPING and DISTRIBUTION. So we have the following:
C: TRANSLATION (JobNum [PK] [FKb], Version [PK] [FKb], DueDate, ...) D: TYPING (JobNum [PK] [FKb], Version [PK] [FKb], DueDate, ...) E: DISTRIBUTION (JobNum [PK] [FKb], Version [PK] [FKb], Copies, ...)
As you can see I am using the PK of TASK as FK and PK for each of the three SUB_TASKs.
To complicate things, each SUB_TASK has one or more assignments. The assignments for each SUB_TASK records different information from the others. So we have...
C: TRANSLATION (JobNum [PK] [FKb], Version [PK] [FKb], DueDate, ...) D: TYPING (JobNum [PK] [FKb], Version [PK] [FKb], DueDate, ...) E: DISTRIBUTION (JobNum [PK] [FKb], Version [PK] [FKb], Copies, ...)
F: TRA_ASSIGN (JobNum [PK] [FKc], Version [PK] [FKc], Index [PK], Translator, ...) G: TYP_ASSIGN (JobNum [PK] [FKd], Version [PK] [FKd], Index [PK], Typyst, ...) H: REP_ASSIGN (JobNum [PK] [FKe], Version [PK] [FKe], Index [PK], Pages, ...)
that is there is an identifying 1:M relationship between each SUB_TASK and its ASSIGNMENTs, each ASSIGNMENT being identified by the SUB_TASK it belongs to and an Index.
I wish I could send a pic of the ER diagram...
Maybe there is another and better way to model this: if so, any suggestion?
Given this model, should I use for TRANSLATION, TYPING and DISTRIBUTION a surrogate key, instead of using the composite key, like for example:
C: TRANSLATION (TranslationID [PK], JobNum [FKb], Version [FKb], DueDate, ...) D: TYPING (TypingID [PK], JobNum [FKb], Version [FKb], DueDate, ...) E: DISTRIBUTION (DistributionID [PK], JobNum [FKb], Version [FKb], Copies, ...)
this will "improve" the ASSIGNMENTs tables:
F: TRA_ASSIGN (TranslationID [PK] [FKc], Index [PK], Translator, ...) G: TYP_ASSIGN (TypingID [PK] [FKd], Index [PK], Typyst, ...) H: REP_ASSIGN (DistributionID [PK] [FKe], Index [PK], Pages, ...)
I could even go further using a surrogate key even for TASK, which leads me to the following:
F: TRA_ASSIGN (TaskID [PK] [FKc], Index [PK], Translator, ...) G: TYP_ASSIGN (TaskID [PK] [FKd], Index [PK], Typyst, ...) H: REP_ASSIGN (TaskID [PK] [FKe], Index [PK], Pages, ...)
I don't really like this second solution, but I'm still not sure about the first solution, the one with the surrogate key only in the SUB_TASks tables.
I have a table that has 3 columns, two of them make a composite primary key. The table is populated with data. What I need to do is to add a third column to a composite primary key. I have tried to do that with the following command:
alter table databasesize add constraint pk_dbsize primary key (dbid)
But I get the error message:
Table 'databasesize' already has a primary key defined on it.
What I need to achieve is - everytime that a new line is inserted into an orderlines table part of the primary key will be the OrderId and the OrderLineId should be auto-incremented from 1 for each OrderId in the OrderLines table.
I know i can do this manually in my program, but i'm just wondering if theres a way to achive this in SQL Server?
Hi, i want to make a reference from a table on itself. The table has a composite Primary Key. But I just want to refernce the TEstCaseID. So whats wrong? Can anyone help me?
NOTE: I am not interested in any responses that want to argue the use of a unique ID field and autonumbering as the PK. It is quite clear from the forums that this subject is a polarizing one. This question is for those who follow text-book design practices and believe that a composite primary key should be used when it is available. I want to be one of them for the time being.
SYNOPSIS: I have three tables, TestSummary, TestDetails, and Steps.
The TestSummary table looks like this:
Create table TestSummary ( TestSummaryID int identity primary key, ... SequenceID int not null )
It contains the date and time of the test, the serial number, the part number, the test operator's name, and the ID of the sequence of steps used during the test. It uses a unique ID field for the primary key.
The TestDetails table looks like this:
Create table TestDetails ( TestDetailsID int identity primary key, TestSummaryID int not null, StepID int not null, ... )
It contains the details of the test like voltage readings, current readings, temperature, etc., one record per reading. It also contains the step number of the test sequence specified in the TestSummary table.
The Steps table looks like this:
Create table Steps ( SequenceID int not null, StepID int not null, Function int not null Primary key (SequenceID, StepID) )
It contains a list of all of the functions to be performed on the device under test by sequence number and the step number within the sequence.
When I try to establish a relation between TestSummary.SequenceID and Steps.SequenceID, SQL Server flags an error because TestSummary.SequenceID and Steps.SequenceID:Steps.StepID do not match.
Is is possible to create a composite primary key on a table variable?Neither of these two statements are successful:DECLARE @opmcjf TABLE (jobdetailid INT NOT NULL,cjfid INT NOT NULL,cjfvalue VARCHAR(100) NULL)ALTER TABLE @opmcjf ADD CONSTRAINT [PK_opmcjf] PRIMARY KEY CLUSTERED([jobdetailid],[cjfid])andDECLARE @opmcjf TABLE (jobdetailid INT PRIMARY KEY,cjfid INT PRIMARY KEY,cjfvalue VARCHAR(100) NULL)Thanks,Shaun
We have scenario like this .the source table have composite primary key columns c1,c2,c3,c4.c5,c6 .when we move the records to destination .we have to check columns (c1+ c2 + c3 + c4 + c5 + c6) combination exist in the destination. if the combination exist then we should do a update else we need to do a Insert . how to achive this .we have tryed useing conditional split which is working only for a single Primary key . can any one help us .
I want to created composite primary key using MS SQL Server Enterprise Manager Interface. I want to use Interface instead of writing it in CREATE Table statement. I was able to create foreign key using this Interface using the "Manage Relationship" option. But cannot find how to add primary key consisting of two fields. Any help regarding this is highly appreciated.
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
I am having a little trouble getting this to work right, but have come a ways since I started this.......other tables created first and with no problems..... then these two with the last table being the problemI need to set one foreign key in the second table referencing the first table.But, the primary key is clustered with the two foreign keys and I get the error....There are no primary or candidate keys in the referenced table 'courseScores' that match the referencing column list in the foreign key 'FK_course'.CREATE TABLE dbo.courseScores ( courseId varchar(20) NOT NULL CONSTRAINT FK_courseId_courseStructure2 FOREIGN KEY (courseId) REFERENCES courseStructure (courseId), studentId varchar(20) NOT NULL CONSTRAINT FK_studentId_students2 FOREIGN KEY (studentId) REFERENCES students (studentId), CONSTRAINT PK_courseScore PRIMARY KEY CLUSTERED (courseId, studentId) )CREATE TABLE dbo.objScores ( tmp int IDENTITY(1,1) PRIMARY KEY, objective varchar(50) NOT NULL, courseId varchar(20) NOT NULL CONSTRAINT FK_course FOREIGN KEY (courseId) REFERENCES courseScores (courseId) ) Once I get it working, then the tmp will be gone and then set 3 foreign keys as the clustered primary, fyi.Not sure how to reference half a primary key?Any help is greatly appreciated.....Thanks all,Zath
I'm currently using Oracle and receiving this error:
SQL Error: ORA-02291: integrity constraint (43437001.ATHLETE_FK1) violated - parent key not found 02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found" *Cause: A foreign key value has no matching primary key value. *Action: Delete the foreign key or add a matching primary key.
When inserting this value:
INSERT INTO ATHLETE VALUES ('A001','TOM','HANKS','03-FEB-90','USA','C001');
