By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,434 Members | 2,884 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,434 IT Pros & Developers. It's quick & easy.

Want 1 to many relationship when child table has no primary key

P: n/a
I want to create a 1-many relationship. Parent table has a primary
key, child table has no primary key. The child table does have an
index with all four fields of the parent's PK. How can I do this?
Thanks, Bob C.
Jul 20 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Bob C. (bc******@bmghomes.com) writes:
I want to create a 1-many relationship. Parent table has a primary
key, child table has no primary key. The child table does have an
index with all four fields of the parent's PK. How can I do this?


If that table does not have a primary key, you should go out and buy
one. It's good table design to always have a primary key.

I'm sure that I understand what you mean, but it sounds that you have:
(All datatypes below are arbitrary choices.)

CREATE TABLE paraent (keycol1 int NOT NULL,
keycol2 datetime NOT NULL,
keycol3 char(2) NOT NULL,
keycol4 tinyint NOT NULL,
othercol money NULL,
morecol float NULL,
CONSTRAINT pk_parent (keycol1, keycol2, keycol3, keycol4))

CREATE TABLE child (keycol1 int NOT NULL,
keycol2 datetime NOT NULL,
keycol3 char(2) NOT NULL,
keycol4 tinyint NOT NULL,
evenmorecol bit NOT NULL)

To add a foreign-key constraint:

ALTER TABLE child ADD
CONSTRAINT fk_child_parent FOREIGN KEY
(keycol1, keycol2, keycol3, keycol4)
REFERENCES parent (keycol1, keycol2, keycol3, keycol4)

Now, to give the child table a primary key, you need to find out what
indifies the each child, and if there is no such column, and a column
childno, so that you have:

CREATE TABLE child (keycol1 int NOT NULL,
keycol2 datetime NOT NULL,
keycol3 char(2) NOT NULL,
keycol4 tinyint NOT NULL,
childno int NOT NULL,
evenmorecol bit NOT NULL,
CONSTRAINT pk_child (keycol1, keycol2, keycol3, keycol4, childno),
CONSTRAINT fk_parent_child ... )

Because that's an essential characteristic of a child table that it
has the keys of the parent table, and in case of a 1-to-many relation-
ship one more more key columns.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #2

P: n/a
Thanks again, Erland. You have it right.

I've been using EM to create indexes and relationships, but this
particular relationship doesn't seem to want to stick.

Contracts = the parent with a primary key:
CommCode
NeigCode
LotNumber
ContractDate

DollarDetail = the child with a matching(non-primary, non-unique) key:
CommCode
NeigCode
LotNumber
ContractDate

When I use EM to work in the DollarDetail table, I set the primary key
table to Contracts and the foreign key table to DollarDetail, using on
the fields in these indexes.

Upon saving I get the message:

'Contracts' table saved successfully
'DollarDetail' table
- Unable to create relationship 'FK_DollarDetail_Contracts'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE
statement conflicted with TABLE FOREIGN KEY constraint
'FK_DollarDetail_Contracts'. The conflict occurred in database 'MDS',
table 'Contracts'.

So, am I setting the primary/foreign keys backwards? Should I be
doing this from the Contracts table? What do YOU see?

Thanks very much for your help!

Bob C.
Jul 20 '05 #3

P: n/a
Bob C. (bc******@bmghomes.com) writes:
'Contracts' table saved successfully
'DollarDetail' table
- Unable to create relationship 'FK_DollarDetail_Contracts'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE
statement conflicted with TABLE FOREIGN KEY constraint
'FK_DollarDetail_Contracts'. The conflict occurred in database 'MDS',
table 'Contracts'.

So, am I setting the primary/foreign keys backwards? Should I be
doing this from the Contracts table? What do YOU see?


If you want assistance on how to use Enterprise Manager, you are on
your own. I never use EM to change tables, but prefer using SQL commands.

However, I would interpret the message that the child table contains
data which is not in the parent table. Use this SELECT to find out:

SELECT *
FROM child c
WHERE NOT EXISTS (SELECT *
FROM parent p
WHERE p.keycol1 = c.keycol1
AND p.keycol2 = c.keycol2
AND p.keycol3 = c.keycol3
AND p.keycol4 = c.keycol4)

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

P: n/a
bc******@bmghomes.com (Bob C.) wrote in message news:<2e**************************@posting.google. com>...
Thanks again, Erland. You have it right.

I've been using EM to create indexes and relationships, but this
particular relationship doesn't seem to want to stick.

Contracts = the parent with a primary key:
CommCode
NeigCode
LotNumber
ContractDate

DollarDetail = the child with a matching(non-primary, non-unique) key:
CommCode
NeigCode
LotNumber
ContractDate

When I use EM to work in the DollarDetail table, I set the primary key
table to Contracts and the foreign key table to DollarDetail, using on
the fields in these indexes.

Upon saving I get the message:

'Contracts' table saved successfully
'DollarDetail' table
- Unable to create relationship 'FK_DollarDetail_Contracts'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE
statement conflicted with TABLE FOREIGN KEY constraint
'FK_DollarDetail_Contracts'. The conflict occurred in database 'MDS',
table 'Contracts'.

So, am I setting the primary/foreign keys backwards? Should I be
doing this from the Contracts table? What do YOU see?

Thanks very much for your help!

Bob C.

Bob,

Sounds like your existing data could be conflicting with the FK rules
(i.e. you could have a record in the child table which has no
counterpart in the parent table)

You could get round this by deleting all your data in the child table
(if you have this option), or alternatively in the FK properties box
in EM, clikc the Relationships tab, and uncheck the "Check Existing
Data On Creation" checkbox. This will then only enforce the FK rule
checking on the subsequent addition or modification of rows.

If you want to do this using a query, you'd write it as:

ALTER TABLE child WITH NOCHECK
ADD CONSTRAINT fk_child_parent FOREIGN KEY
(keycol1, keycol2, keycol3, keycol4)
REFERENCES parent (keycol1, keycol2, keycol3, keycol4)

Better to get your data sorted out first, though, and then add the PK
allowing it to validate the existing data, otherwise you could run
into problems later on during use.
Jul 20 '05 #5

P: n/a


Bob C. wrote:
I want to create a 1-many relationship. Parent table has a primary
key, child table has no primary key. The child table does have an
index with all four fields of the parent's PK. How can I do this?
Thanks, Bob C.


I don't understand the problem. Let's say the parent row is uniquely
identified by the first four column values, say 1,2,3,4. For every
instance of the parent child releationship, the child table will
have a row with the parent's identity (1,2,3,4) and the child-specific data.
Just make sure the child table's index on those four fields isn't a unique index.

Joe Weinstein at BEA

Jul 20 '05 #6

P: n/a
Thanks very much, gentlemen. You both nailed it.

Your reponses have been thorough and very helpful!

Bob C.
Jul 20 '05 #7

P: n/a
Gentlemen,

I checked for orphaned child records and found none. I will try
Erland's sql statement to create relationship, but meanwhile are there
any new thoughts given the absence of orphans? I'm assuming I don't
have to have at least one child record for every parent record.

Thanks,

Bob C.
Jul 20 '05 #8

P: n/a
Thanks, Joe.

Yes, I have a primary key index in the parent table, and a similar
non-primary, non-unique index in the child table. All types, etc. in
the key columns match exactly. There are no orphans in the child
table.

The problem is that EM won't allow me to create a relationship between
the two tables based on the indexes mentioned above. Regardless of
which table I have open in design mode for this operation, when I save
I get the same error involving a constraint conflict in the parent
table.

Bob C.
Jul 20 '05 #9

P: n/a
Bob C. (bc******@bmghomes.com) writes:
I checked for orphaned child records and found none.
That's funny. Then again, I have never trusted EM. :-)
I will try Erland's sql statement to create relationship, but meanwhile
are there any new thoughts given the absence of orphans? I'm assuming I
don't have to have at least one child record for every parent record.


Your assumption is correct.

It's not that I want to belittle you, but maybe you should check your
transformation of my SELECT for orphans for typos?

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.