| Author |
Topic |
|
patla4u
Starting Member
10 Posts |
 Posted - 2011-06-01 : 18:31:37
|
| Hello,I have a 2 tablesTable :1 - ACcolumn 1 - AC_CUSTOMER_IDCOLUMN 2 - AC_CUSTOMER_DM_IDCOLUMN 3 - AC_CUSTOMER_CDThis table is making composite primary key.(combination of 3 column)TABLE 2 - ADCOLUMN 1 - AD_CUSTOMER_IDCOLUMN 2 - AD_CUSTOMER_DM_ID(I don't have 3 column)How can i create foreign key constraint and primary key constrint??Table-2 is referring table-1 .Records in tables 2 is available in table 1.I am desining detabase and is is necessary like composite primary key must match column with composite foreign key???table 1 have 3 column and table 2 have 2 column so can i create constrint???or create relation???thanksB |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-06-01 : 19:12:53
|
| SQL won't let you do that because the foreign key needs to match up to a priamry or candidate key in the referenced table. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
patla4u
Starting Member
10 Posts |
Posted - 2011-06-01 : 19:32:14
|
| I GOT A ERROR MSG LIKETHE COLUMN IN TABLE 1 DO NOT MATCH THE EXISTING PRIMARY KEY OR UNIQUE CONSTRINT.please let me knowthanksb |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
patla4u
Starting Member
10 Posts |
Posted - 2011-06-01 : 20:00:29
|
| explain me more in detail |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-06-01 : 22:43:07
|
| Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help. data element do not change names from table to tableColumns are not in any orderRows are not records.>> How can I create foreign key constraint and primary key constraint Table-2 is referring table-1 <<FOREIGN KEY constraints must reference a UNIQUE or PRIMARY KEY in the referenced tableCREATE TABLE AC(customer_id INTEGER NOT NULL, customer_dm_id INTEGER NOT NULL, UNIQUE (customer_id, customer_dm_id), customer_cd INTEGER NOT NULL, PRIMARY KEY (customer_id, customer_dm_id, customer_cd));CREATE TABLE AD((customer_id INTEGER NOT NULL, customer_dm_id INTEGER NOT NULL, FOREIGN KEY (customer_id, customer_dm_id) REFERENCES AC (customer_id, customer_dm_id)););--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-02 : 05:25:01
|
| It's always worth creating test tables to check what will work - only takes a few minutescreate table d (i int, j int, k int)create table e (i int, j int, k int)create unique index ix on d (i)alter table e add constraint f1 foreign key (j,k) references d (j,k)There are no primary or candidate keys in the referenced table 'd' that match the referencing column list in the foreign key 'f1'.create unique index ix1 on d (i,j,k)alter table e add constraint f1 foreign key (j,k) references d (j,k)There are no primary or candidate keys in the referenced table 'd' that match the referencing column list in the foreign key 'f1'.create unique index ix2 on d (j,k,i)alter table e add constraint f1 foreign key (j,k) references d (j,k)There are no primary or candidate keys in the referenced table 'd' that match the referencing column list in the foreign key 'f1'.create unique index ix3 on d (j,k) include (i)alter table e add constraint f1 foreign key (j,k) references d (j,k)Command(s) completed successfully.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
Composite primary key