| Author |
Topic |
|
mAdMaLuDaWg
Starting Member
42 Posts |
 Posted - 2002-06-19 : 15:07:53
|
Hello all,I'm having a problem creating constraints between some tables. Here is my problem to be exact.Tables in Question hereSeminarSchedule (has ID which is it's Identity and SDID as its primary keys. SDID is contrained to the ID of a table called SeminarDescription).SignUps (has ETUserID which is constrained to the ID of ETUserDB and SSID which is supposed to be constrained to the ID of SeminarSchedule). However when I try to constrain SignUps.SSID to SeminarSchedule.ID I get an error : 'The columns in SeminarSchedule do not match an existing primary key or unique constraint.' I can't understand this as both IDs in question here are primary keys and are of the same type (int).There is no data in the tables and everything else seems right.. I did this a million times and this is the first time I'm running into problems.Someone please help! |
|
|
blocky97
Starting Member
3 Posts |
Posted - 2002-06-19 : 15:11:09
|
| um..it sounds like you might not have a unique match up. i.e. table one has 1234table 2 has1254the 5 doesn't match up with the 3...that might cause that error... |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-19 : 15:14:16
|
| The column referenced by a foreign key constraint must be unique. It sounds to me like your uniqueness is only guarrenteed by the composite of ID and SDID . . .but then again, who can actually tell what you are talking about unless you post the exact DDL.In general, a composite key where one member is defined with the identity property points to poor database design. You oughta post your full create table statements (including the constraints) and let us help you sort this out.<O>Edited by - Page47 on 06/19/2002 15:15:11 |
|
|
|
mAdMaLuDaWg
Starting Member
42 Posts |
Posted - 2002-06-19 : 15:15:18
|
quote:
um..it sounds like you might not have a unique match up. i.e. table one has 1 2 3 4 table 2 has 1 2 5 4 the 5 doesn't match up with the 3...that might cause that error...
Tks for the fast reply but it didn't work. The tables are empty so there is no data in them. I tried deleting and recreating the tables but still doesn't work. Wonder what's wrongEdited by - mAdMaLuDaWg on 06/19/2002 15:18:08 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-19 : 15:35:54
|
| You don't need to have any data in the tables for what I've said to be true.<O> |
|
|
|
mAdMaLuDaWg
Starting Member
42 Posts |
Posted - 2002-06-19 : 15:53:56
|
quote:
The column referenced by a foreign key constraint must be unique. It sounds to me like your uniqueness is only guarrenteed by the composite of ID and SDID . . .but then again, who can actually tell what you are talking about unless you post the exact DDL.In general, a composite key where one member is defined with the identity property points to poor database design. You oughta post your full create table statements (including the constraints) and let us help you sort this out.<O>Edited by - Page47 on 06/19/2002 15:15:11
Alright I'll type them real fast so try to overlook typos.1.Create table SeminarSchedule(ID Int Identity Not Null,SDID Int Not Null,...) (rest don't matter as they contain data for the tables)2.Alter table SeminarSchedule Add Constraint SeminarPk Primary Key(ID)3.Alter table SeminarSchedule Add Constraint SeminarFK Foreign Key(SDID) References dbo.SeminarDescriptions(ID)4.Create table SignUps (SSID Int not Null,ETUserID Int Not Null)5.Create table ETUserDB (ID Int Identity Not Null,..)6.Alter table ETUserDB Add Constraint ETPk Primary Key(ID)7.Alter table SignUps Add Constraint SUFK Foreign Key (SSID) References dbo.SeminarSchedule(ID) -->problem here8. Alter table SignUps add Constraint SUFKII Foreign Key(ETUserID) References dbo.ETUserDB(ID) -->works fineI would appreciate it if you could also include all your criticism on this design as I'm relatively new to SQLEdited by - mAdMaLuDaWg on 06/19/2002 15:58:04 |
|
|
|
mAdMaLuDaWg
Starting Member
42 Posts |
Posted - 2002-06-19 : 15:54:48
|
quote:
You don't need to have any data in the tables for what I've said to be true.<O>
Srry... it was in reply to blocky!Edited by - mAdMaLuDaWg on 06/19/2002 15:57:27 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-19 : 16:02:51
|
| In your first post you say that ID and SDID are the primary keys (I am reading that to mean its a composite key) of SeminarSchedule. However, the primary key constraint in your most recent post says ID only is the primary key...which is it? (I'm not just being picky, it actually makes a difference)<O> |
|
|
|
mAdMaLuDaWg
Starting Member
42 Posts |
Posted - 2002-06-19 : 16:46:42
|
quote:
In your first post you say that ID and SDID are the primary keys (I am reading that to mean its a composite key) of SeminarSchedule. However, the primary key constraint in your most recent post says ID only is the primary key...which is it? (I'm not just being picky, it actually makes a difference)<O>
Well I think I also made SDID a primary key.Alter table SeminarSchedule Add Constraint SeminarPk4 Primary Key(SDID) which ran fine.I'm not sure because I'm writing these statements from my memory.BTW, is there any way to view the history of your commands in MS SQL QUERY?Edited by - mAdMalUdAwG on 06/19/2002 16:50:55Edited by - mAdMalUdAwG on 06/19/2002 16:51:44 |
|
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2002-06-19 : 19:13:06
|
quote:
Well I think I also made SDID a primary key.Alter table SeminarSchedule Add Constraint SeminarPk4 Primary Key(SDID) which ran fine.I'm not sure because I'm writing these statements from my memory.BTW, is there any way to view the history of your commands in MS SQL QUERY?
Run exec sp_help SeminarSchedule and then again for SignUps. There will be a secion with a heading of "constraint_type"you'll find your PK constraints in there, I'd suggest double checking against Page's suggestion that way.Justin |
|
|
|
mAdMaLuDaWg
Starting Member
42 Posts |
Posted - 2002-06-20 : 08:36:13
|
| SeminarScheduleIdentity Seed Increment Not For Replication ------------------------------------------------------ID 1 1 1index_name index_description-----------------------------------------------------------PK_SeminarSchedule nonclustered, unique, primary key located on Primary1.constraint_type : FOREIGN KEY constraint name:FK_SeminarSchedule_SeminarDescriptions status_enabled : Enabled status_for_replication : IS_FOR_REPLICATIONConstraint keys : SDID REFERENCES Seminar.dbo.SeminarDescriptions(ID)2.constraint_type : PRIMARY KEY (non-clustered) constraint name : PK_SeminarSchedule status_enabled : (n/a) status_for_replication : (n/a) Constraint keys : ID,SDIDFor signups :1.constraint_type : FOREIGN KEY constraint name:FK_SignUps_ETUserDB status_enabled : Enabled status_for_replication : IS_FOR_REPLICATIONConstraint keys : ETUserID REFERENCES Seminar.dbo.ETUserDB(ID)2.constraint_type : PRIMARY KEY (non-clustered) constraint name : PK_SignUps status_enabled : (n/a) status_for_replication : (n/a) Constraint keys : SSID,ETUserIDHope you could tell me what is wrong by now... tks!Edited by - mAdMaLuDaWg on 06/20/2002 08:41:28 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-20 : 09:05:58
|
quote:
2.constraint_type : PRIMARY KEY (non-clustered) constraint name : PK_SeminarSchedule status_enabled : (n/a) status_for_replication : (n/a) Constraint keys : ID,SDID
...like I said...quote:
The column referenced by a foreign key constraint must be unique. It sounds to me like your uniqueness is only guaranteed by the composite of ID and SDID . . .but then again, who can actually tell what you are talking about unless you post the exact DDL.
It is really as simple as this. quote:
A foreign key value represents a reference to the tuple containing the matching candidate key value (the referenced tuple or target tuple).--C. J. Date, An Introduction to Database Systems
As you have defined the table, the target of your foreign key constraint (SeminarSchedule.SDID) is not a candidate key (a candidate key is any set of one or more columns whose combined values are unique among all occurrences). If your primary key constraint (uniquess guarrentee) was on SDID only or if you had a unique constraint defined for SDID, you would not have a problem. Here's the thing...quote:
In general, a composite key where one member is defined with the identity property points to poor database design. You oughta post your full create table statements (including the constraints) and let us help you sort this out.
Let me reiterate the second part of my original post. Why, oh why, would you have a contrived, synthetic identity column as part composit primary key? If SDID is unique and hence the natural key, as inferred by your attempt to create a foreign key constaint, then what is the point of the identity (ID being a SQL Server reserved word that should not be used as a column name). Either that or the identity column is a stand alone surrogate, because your natural key is far to complex to use as a primary key or (more likely) your table is not normalized, so instead of working that out you are trying to shoe-horn it into the relational model. . .<O> |
|
|
|
mAdMaLuDaWg
Starting Member
42 Posts |
Posted - 2002-06-20 : 09:15:57
|
Thanks page,I could see you are agitated with my tables  .I haven't grasped the concepts of SQL yet but I see what you mean. I should only make unique colums Candiate Keys. Anyway, thanks for your help... I'll redesign the Database and it should then work. Why I did this, well I had to overtake SQL development of our company after our DB programmer left, it looks like he (for some reason) did the same and I was blindy following what he did.Could you tell me why he would do that? He did it in several areas in a similar circumstance. i.e., the ForeignKey was also a primary key referencing the ID of another table. Also, what is the problem in naming a column ID... he (the SQL developer over here) did it in all his tables? Is it just a bad programming practice or would it cause problems?Again thanks. Edited by - mAdMaLuDaWg on 06/20/2002 09:37:11 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-20 : 10:01:31
|
Aggitated? Naw, didn't mean to come off that way. Just trying to lay out the facts...quote:
I should only make unique colums Candiate Keys.
Lemme try to help you along here. You don't get to make a column a candidate key. A candidate key is defined, in simple terms, as 'could this column be the primary key?...is the column (or tuple) a candidate for selection of a primary key.' If the column is unique, it's a candidate.quote:
Could you tell me why he would do that?
He probably didn't know better. The database world is made up of plenty of people that design by 'gut feel' rather than based on solid relational theory. Why is that? Well, certain database academics (Date, Pascal, etc) would say its the fault of the DBMS makes...microsoft, oracle, etc...for allowing non-relation things to be implemented. I think that's part of the answer; I'll reserver the other part of a future rant. Unfortunatley, he (the outgoing dba) is probably in the majority...quote:
what is the problem in naming a column ID
Well, put simply by Books OnLine...quote:
It is not legal to include the reserved keywords in a Transact-SQL statement in any location except that defined by SQL Server. No objects in the database should be given a name that matches a reserved keyword. If such a name exists, the object must always be refered to using delimited identifiers. Although this method does allow for objects whose names are reserved words, it is recommended that you do not name any database objects with a name that is the same as a reserved word.
Good Luck.<O> |
|
|
|
mAdMaLuDaWg
Starting Member
42 Posts |
Posted - 2002-06-20 : 10:15:38
|
| Heheh... was j/k about the agitated statement. I know you were puzzled by my technique and it is something I see everyday as I'm a software engineer. I think what you said about the db world could be applied to the whole computer industry in general.Anyway, back to the question. If a tuple succesfully qualifies as a candidate for a primary key, then it should be made a primary key right? For example, all the ID columns in my case should be made a primary key right?Would there be any case of a table having two primary keys?Thanks again. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-20 : 10:57:59
|
| mAdMaLuDaWg, I do appriciate your thirst for knowledge, but I think at this point your best bet do some reading about relational databases design and normalization. (No, you can't have two primary keys...1st Normal Form)<O> |
|
|
|
mAdMaLuDaWg
Starting Member
42 Posts |
Posted - 2002-06-20 : 11:34:48
|
quote:
mAdMaLuDaWg, I do appriciate your thirst for knowledge, but I think at this point your best bet do some reading about relational databases design and normalization. (No, you can't have two primary keys...1st Normal Form)<O>
I enjoyed it too. Yes, I will have to do some reading up on this topic otherwise I see my questions going on for ever. When we move our website to VS.NET and upgrade our servers we will definitely need some help with databases and I will be sure to contact you.Thanks again. |
|
|
|
|
Why can't I create a relationship between tables??