Hi all,
I have the following tables:
Create Table T1( ID1 int Not Null, ID2 int Not Null, Description
VarChar(20), Constraint ID Primary Key(ID1, ID2) )
Create Table T2( ID1 int Not Null, ID2 int Not Null, Description
VarChar(20), Constraint ID Primary Key(ID1, ID2) )
CREATE TABLE test( MainID int Not Null, t1ID int Not Null, t2ID int
, CONSTRAINT t1ID FOREIGN KEY (MainID, t1ID) REFERENCES t1(ID1, ID2)
, CONSTRAINT t2ID FOREIGN KEY (MainID, t2ID) REFERENCES t2(ID1, ID2)
)
If I try to execute the following sql I get an error, because t2ID is
not a valid key for T2:
INSERT INTO test ( MainID, t1ID, t2ID ) VALUES (1, 1, null);
In other databases this is no problem, means I can fill one column with
a value and set the other column of the key to null. I'm porting a Db2
database to access and many tables have multiple column keys with only
the MainID filled.
Does anybody knows a workaround?
Thanks in advance
Andy 4 10679 am****@yahoo.de wrote: Hi all,
I have the following tables:
Create Table T1( ID1 int Not Null, ID2 int Not Null, Description VarChar(20), Constraint ID Primary Key(ID1, ID2) )
Create Table T2( ID1 int Not Null, ID2 int Not Null, Description VarChar(20), Constraint ID Primary Key(ID1, ID2) )
CREATE TABLE test( MainID int Not Null, t1ID int Not Null, t2ID int , CONSTRAINT t1ID FOREIGN KEY (MainID, t1ID) REFERENCES t1(ID1, ID2) , CONSTRAINT t2ID FOREIGN KEY (MainID, t2ID) REFERENCES t2(ID1, ID2) )
If I try to execute the following sql I get an error, because t2ID is not a valid key for T2:
INSERT INTO test ( MainID, t1ID, t2ID ) VALUES (1, 1, null);
In other databases this is no problem, means I can fill one column with a value and set the other column of the key to null. I'm porting a Db2 database to access and many tables have multiple column keys with only the MainID filled.
I ran this in SQL Server and it failed with the following message:
INSERT statement conflicted with TABLE FOREIGN KEY constraint 't1ID'.
The conflict occurred in database 'EsTest', table 'T1'.
I can't think of a workaround, but I would suspect that your data
design is fatally flawed.
Good luck!
Edward
Hi Edward,
the message you get has nothing to do with a flawed data design, insert
this two statements
INSERT INTO T1 ( ID1, ID2, Description ) VALUES (1, 1, 'test');
INSERT INTO T2 ( ID1, ID2, Description ) VALUES (1, 1, 'test');
and then insert the data into the test-table.
Sorry, I forget this two Insert-Statements.
Andy
Hi all,
does noone have the same problem or has a solution?
Andy am****@yahoo.de wrote: Hi all,
does noone have the same problem or has a solution?
You original post is almost 3 weeks old.
You might want to re-post and perhaps re-word your
question. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: geoff |
last post by:
The table creation script(at the end of this post) works fine on
4.0.1-alpha-win, but the foreign key constraints fail on 4.0.15-win. I
am starting the server with the same command for both...
|
by: Gunnar Vøyenli |
last post by:
Hi!
For the sake of simplicity, I have three tables, Employee, Department and
Work
Employee >---- Department
\ /
\ /
^ ^
Work
|
by: Bodza Bodza |
last post by:
I'm having an argument with an incumbent self-taught programmer that
it is OK to use null foreign keys in database design.
My take is the whole point of a foreign key is that it's not supposed...
|
by: adammitchell |
last post by:
How can you indicate that a FOREIGN KEY constraint references two
columns in two different tables?
"SQL Server Books Online" show an example of how to reference two
columns in the SAME table:...
|
by: pb648174 |
last post by:
I have a table called BidItem which has another table called
BidAddendum related to it by foreign key. I have another table called
BidFolder which is related to both BidItem and BidAddendum, based...
|
by: heroe |
last post by:
Hello *,
i write you with an urgent problem. I would like to create forign keys
to datetime field, but it doesn't seem to work. I get Can't create table
'.\sampleDB\student_module.frm' (errno:...
|
by: Thomas LeBlanc |
last post by:
I am able to enter a null in a foreign key field in one table(table2)
without the foreign key relationship (table1.t1_id) validating the value.
Why is this?
Below are 2 table's scripts:
...
|
by: am72de |
last post by:
Hi all,
I've posted this problem some weeks ago, but noone had a solution.
Perhaps now someone could help me.
I have the following tables:
Create Table T1
( ID1 int Not Null
, ID2 int Not...
|
by: Frank Swarbrick |
last post by:
So we're trying to decide if it's better to use IDENTITY columns or
sequences to create a surrogate key as the primary key for our tables. I
kind of like the identity column, because it's more...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |