Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rey00002
Starting Member
2 Posts |
 Posted - 2006-10-08 : 14:21:54
|
| I'm very new to SQL.YOur help is appreciated.Ok system_user is the main table.All the CREATED_BY and LAST_UPDATED_BY columns in all tables are supposed to reference backto the system_user table. Anything with _TYPE (i.e. CONTACT_TYPE, ADDRESS_TYPE ) is supposed to map to common_lookuptable.Heres the error I get:ERROR at line 12:no mathiching unique or primary key for this column-list.Heres where I I think I have the mistakes.Pks and FKsThank you.CREATE TABLE system_user( SYSTEM_USER_ID NUMBER, SYSTEM_USER_NAME VARCHAR2(20), SYSTEM_USER_GROUP_ID NUMBER, SYSTEM_USER_TYPE NUMBER, LAST_NAME VARCHAR2(20), FIRST_NAME VARCHAR2(20), MIDDLE_INITIAL VARCHAR2(1), CREATED_BY NUMBER CONSTRAINT nn_system_user_1 NOT NULL, CREATION_DATE DATE, LAST_UPDATED_BY NUMBER CONSTRAINT nn_system_user_2 NOT NULL, LAST_UPDATE_DATE DATE , CONSTRAINT pk_system_user_1 PRIMARY KEY (SYSTEM_USER_ID, CREATED_BY, LAST_UPDATED_BY));CREATE TABLE common_lookup( COMMON_LOOKUP_ID NUMBER, COMMON_LOOKUP_CONTEXT VARCHAR2(30), COMMON_LOOKUP_TYPE VARCHAR2(30), COMMON_LOOKUP_MEANING VARCHAR2(30), CREATED_BY NUMBER CONSTRAINT common_lookup_1 NOT NULL, CREATION_DATE DATE, LAST_UPDATED_BY NUMBER CONSTRAINT common_lookup_2 NOT NULL, LAST_UPDATE_DATE DATE , CONSTRAINT pk_common_lookup_1 PRIMARY KEY (COMMON_LOOKUP_ID), CONSTRAINT fk_common_lookup_1 FOREIGN KEY (CREATED_BY) REFERENCES system_user (CREATED_BY), CONSTRAINT fk_common_lookup_2 FOREIGN KEY (LAST_UPDATED_BY) REFERENCES system_user (LAST_UPDATED_BY));CREATE TABLE contact( CONTACT_ID NUMBER , MEMBER_ID NUMBER, CONTACT_TYPE NUMBER CONSTRAINT nn_contact_1 NOT NULL, LAST_NAME VARCHAR2(20), FIRST_NAME VARCHAR2(20), MIDDLE_INITIAL VARCHAR2(1), CREATED_BY NUMBER CONSTRAINT nn_contact_2 NOT NULL, CREATION_DATE DATE, LAST_UPDATED_BY NUMBER CONSTRAINT nn_contact_3 NOT NULL, LAST_UPDATE_DATE DATE , CONSTRAINT pk_contact_1 PRIMARY KEY (CONTACT_ID), CONSTRAINT fk_contact_1 FOREIGN KEY (CONTACT_TYPE) REFERENCES common_lookup (COMMON_LOOKUP_ID), CONSTRAINT fk_contact_2 FOREIGN KEY (CREATED_BY) REFERENCES system_user (CREATED_BY), CONSTRAINT fk_contact_3 FOREIGN KEY (LAST_UPDATED_BY) REFERENCES system_user (LAST_UPDATED_BY));CREATE TABLE address( ADDRESS_ID NUMBER, CONTACT_ID NUMBER CONSTRAINT nn_address_1 NOT NULL, ADDRESS_TYPE NUMBER CONSTRAINT nn_address_2 NOT NULL, CITY VARCHAR2(30) , STATE_PROVINCE VARCHAR2(30), POSTAL_CODE VARCHAR2(20), CREATED_BY NUMBER CONSTRAINT nn_address_3 NOT NULL , CREATION_DATE DATE, LAST_UPDATED_BY NUMBER CONSTRAINT nn_address_4 NOT NULL, LAST_UPDATE_DATE DATE , CONSTRAINT pk_address_1 PRIMARY KEY (ADDRESS_ID), CONSTRAINT fk_address_1 FOREIGN KEY (CONTACT_ID) REFERENCES contact (CONTACT_ID), CONSTRAINT fk_address_2 FOREIGN KEY (ADDRESS_TYPE) REFERENCES system_user (COMMON_LOOKUP_ID), CONSTRAINT fk_address_3 FOREIGN KEY (CREATED_BY) REFERENCES system_user (CREATED_BY);, CONSTRAINT fk_address_4 FOREIGN KEY (LAST_UPDATED_BY) REFERENCES system_user (LAST_UPDATED_BY));CREATE TABLE telephone( TELEPHONE_ID NUMBER, CONTACT_ID NUMBER CONSTRAINT nn_telephone_1 NOT NULL, ADDRESS_ID NUMBER CONSTRAINT nn_telephone_2 NOT NULL, TELEPHONE_TYPE NUMBER CONSTRAINT nn_telephone_3 NOT NULL, COUNTRY_CODE VARCHAR2(3), AREA_CODE VARCHAR2(6), TELEPHONE_NUMBER VARCHAR2(10), CREATED_BY NUMBER CONSTRAINT nn_telephone_4 NOT NULL, CREATION_DATE DATE, LAST_UPDATED_BY NUMBER CONSTRAINT nn_telephone_5 NOT NULL, LAST_UPDATE_DATE DATE , CONSTRAINT pk_telephone_1 PRIMARY KEY (TELEPHONE_ID), CONSTRAINT fk_telephone_1 FOREIGN KEY (CONTACT_ID) REFERENCES contact (CONTACT_ID), CONSTRAINT fk_telephone_2 FOREIGN KEY (ADDRESS_ID) REFERENCES address (ADDRESS_ID), CONSTRAINT fk_telephone_3 FOREIGN KEY (TELEPHONE_TYPE) REFERENCES system_user (COMMON_LOOKUP_ID), CONSTRAINT fk_telephone_4 FOREIGN KEY (CREATED_BY) REFERENCES system_user (CREATED_BY);, CONSTRAINT fk_telephone_5 FOREIGN KEY (LAST_UPDATED_BY) REFERENCES system_user (LAST_UPDATED_BY)); |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-10-08 : 20:20:54
|
What are you trying to do with those columns? If you want the CREATED_BY and LAST_UPDATED_BY columns in all the other tables to be filled with the ID of the user that created or updated the row, then it looks like you need to reference the SYSTEM_USER_ID column in the system_user table. I'm not sure why you have the CREATED_BY and LAST_UPDATED_BY columns in the system_user table, unless you just want to reference the SYSTEM_USER_ID of the user that created or updated a user, but you certainly don't want to reference those columns from the other tables.To reference a column from another table with a foreign key, you must have a primary key or unique index/constraint on that column - that is, a foreign key can only reference a value that is guaranteed to be unique in the other table.So I'd your system_user and common_lookup tables should be like this, you can figure out the other tables from theseCREATE TABLE system_user( SYSTEM_USER_ID NUMBER, SYSTEM_USER_NAME VARCHAR2(20), SYSTEM_USER_GROUP_ID NUMBER, SYSTEM_USER_TYPE NUMBER, LAST_NAME VARCHAR2(20), FIRST_NAME VARCHAR2(20), MIDDLE_INITIAL VARCHAR2(1), CREATED_BY NUMBER CONSTRAINT nn_system_user_1 NOT NULL, CREATION_DATE DATE, LAST_UPDATED_BY NUMBER CONSTRAINT nn_system_user_2 NOT NULL, LAST_UPDATE_DATE DATE , CONSTRAINT pk_system_user_1 PRIMARY KEY (SYSTEM_USER_ID));CREATE TABLE common_lookup( COMMON_LOOKUP_ID NUMBER, COMMON_LOOKUP_CONTEXT VARCHAR2(30), COMMON_LOOKUP_TYPE VARCHAR2(30), COMMON_LOOKUP_MEANING VARCHAR2(30), CREATED_BY NUMBER CONSTRAINT common_lookup_1 NOT NULL, CREATION_DATE DATE, LAST_UPDATED_BY NUMBER CONSTRAINT common_lookup_2 NOT NULL, LAST_UPDATE_DATE DATE , CONSTRAINT pk_common_lookup_1 PRIMARY KEY (COMMON_LOOKUP_ID), CONSTRAINT fk_common_lookup_1 FOREIGN KEY (CREATED_BY)REFERENCES system_user (SYSTEM_USER_ID), CONSTRAINT fk_common_lookup_2 FOREIGN KEY (LAST_UPDATED_BY)REFERENCES system_user (SYSTEM_USER_ID)); I haven't changed any of your data types but you're using Oracle type names - these aren't going to work unless you've created user defined types with those names - OR you're using Oracle in which case you should find an Oracle forum. This is a SQL Server forum and while we don't have anything against Oracle, we won't be able to help you with things that are specific to Oracle.Oh and actually I take that back - most of us probably do have something against Oracle  |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-10-09 : 09:41:38
|
| If you use Oracle post your question atwww.DBForums.comwww.OraFaq.comMadhivananFailing to plan is Planning to fail |
|
|
|
rey00002
Starting Member
2 Posts |
Posted - 2006-10-09 : 13:17:43
|
| Thank you snSQL I understand.In the future I will post to the other fourm if it relates to that. |
|
|
|
|
|
|
|
|
Simple Q. Whats wrong?