Guest User

Untitled

a guest
Jul 6th, 2012
19
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.52 KB | None | 0 0
  1. Can a field reference to 2 different FKs or any better option for this case?
  2. CREATE TABLE `settings` (
  3. `setting_id` int(6) NOT NULL,
  4. `description` varchar(32) NOT NULL,
  5. `code` int(6) DEFAULT NULL,
  6. `created` datetime NOT NULL,
  7. `updated` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  8. PRIMARY KEY (`setting_id`),
  9. KEY `code` (`code`),
  10. CONSTRAINT `settings_ibfk_1` FOREIGN KEY (`code`) REFERENCES `field_values` (`fv_id`) ON UPDATE CASCADE
  11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  12.  
  13. create table Setting (
  14. SettingID integer not null
  15. , SettingType char(1) not null
  16. , SettingName varchar(32) not null
  17. , Description varchar(32) not null
  18. , Created timestamp default CURRENT_TIMESTAMP
  19. -- other common-to-all-setups columns here
  20. );
  21. alter table Setting add constraint pk_setting primary key (SettingID);
  22.  
  23.  
  24. create table LanguageSetting (
  25. SettingID integer not null
  26. , Updated timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
  27. -- other columns specific to this setup-type here
  28. );
  29. alter table LanguageSetting
  30. add constraint pk_langset primary key (SettingID)
  31. , add constraint fk1_langset foreign key (SettingID) references Setting(SettingID);
  32.  
  33.  
  34. create table AgeFilterSetting (
  35. SettingID integer not null
  36. , Updated timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
  37. -- other columns specific to this setup-type here
  38. );
  39. alter table AgeFilterSetting
  40. add constraint pk_ageflt primary key (SettingID)
  41. , add constraint fk1_ageflt foreign key (SettingID) references Setting(SettingID);
  42.  
  43.  
  44. create table OtherSetting (
  45. SettingID integer not null
  46. , Updated timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
  47. -- other columns specific to this setup-type here
  48. );
  49. alter table OtherSetting
  50. add constraint pk_othset primary key (SettingID)
  51. , add constraint fk1_othset foreign key (SettingID) references Setting(SettingID);
  52.  
  53. CREATE TABLE [Child1](
  54. [ParentId] int NOT NULL,
  55. CONSTRAINT [PK2] PRIMARY KEY CLUSTERED ([ParentId])
  56. )
  57.  
  58. CREATE TABLE [Child2](
  59. [ParentId] int NOT NULL,
  60. CONSTRAINT [PK3] PRIMARY KEY CLUSTERED ([ParentId])
  61. )
  62.  
  63. CREATE TABLE [Parent](
  64. [ParentId] int NOT NULL,
  65. CONSTRAINT [PK1] PRIMARY KEY CLUSTERED ([ParentId])
  66. )
  67.  
  68.  
  69. ALTER TABLE [Child1] ADD CONSTRAINT [RefParent1]
  70. FOREIGN KEY ([ParentId])
  71. REFERENCES [Parent]([ParentId])
  72.  
  73. ALTER TABLE [Child2] ADD CONSTRAINT [RefParent3]
  74. FOREIGN KEY ([ParentId])
  75. REFERENCES [Parent]([ParentId])
Advertisement
Add Comment
Please, Sign In to add comment