Jk8i5X3Y

1drop table exams;
2drop table question_bank;
3drop table anwser_bank;
4
5create table exams
6(
7    exam_id uniqueidentifier primary key,
8    exam_name varchar(50),
9);
10create table question_bank
11(
12    question_id uniqueidentifier primary key,
13    question_exam_id uniqueidentifier not null,
14    question_text varchar(1024) not null,
15    question_point_value decimal,
16    constraint question_exam_id foreign key references exams(exam_id)
17);
18create table anwser_bank
19(
20    anwser_id           uniqueidentifier primary key,
21    anwser_question_id  uniqueidentifier,
22    anwser_text         varchar(1024),
23    anwser_is_correct   bit
24);
25	
26create table question_bank
27(
28    question_id uniqueidentifier primary key,
29    question_exam_id uniqueidentifier not null,
30    question_text varchar(1024) not null,
31    question_point_value decimal,
32    constraint fk_questionbank_exams foreign key (question_exam_id) references exams (exam_id)
33);
34	
35alter table MyTable
36add constraint MyTable_MyColumn_FK FOREIGN KEY ( MyColumn ) references MyOtherTable(PKColumn)
37	
38CONSTRAINT your_name_here FOREIGN KEY (question_exam_id) REFERENCES EXAMS (exam_id)
39	
40alter MyTable
41add constraint MyTable_MyColumn_FK FOREIGN KEY ( MyColumn ) 
42references MyOtherTable(PKColumn)
43	
44alter MyTable
45add constraint MyTable_MyColumn_FK FOREIGN KEY ( MyColumn ) 
46references MyOtherTable(PKColumn)
47on update cascade 
48on delete cascade
49	
50create table ProductCategories (
51  Id           int identity primary key,
52  ProductId    int references Products(Id)
53               on update cascade on delete cascade
54  CategoryId   int references Categories(Id) 
55               on update cascade on delete cascade
56)
57	
58create table question_bank
59(
60    question_id uniqueidentifier primary key,
61    question_exam_id uniqueidentifier not null constraint fk_exam_id foreign key references exams(exam_id),
62    question_text varchar(1024) not null,
63    question_point_value decimal
64);
65	
66ALTER TABLE [SCHEMA].[TABLENAME] ADD FOREIGN KEY (COLUMNNAME) REFERENCES [TABLENAME](COLUMNNAME)
67EXAMPLE
68ALTER TABLE [dbo].[UserMaster] ADD FOREIGN KEY (City_Id) REFERENCES [dbo].[CityMaster](City_Id)
69	
70-- First, chech if the table exists...
71IF 0 < (
72    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES 
73    WHERE TABLE_TYPE = 'BASE TABLE'
74    AND TABLE_SCHEMA = 'dbo'
75    AND TABLE_NAME = 'T_SYS_Language_Forms'
76)
77BEGIN
78    -- Check for NULL values in the primary-key column
79    IF 0 = (SELECT COUNT(*) FROM T_SYS_Language_Forms WHERE LANG_UID IS NULL)
80    BEGIN
81        ALTER TABLE T_SYS_Language_Forms ALTER COLUMN LANG_UID uniqueidentifier NOT NULL 
82
83        -- No, don't drop, FK references might already exist...
84        -- Drop PK if exists 
85        -- ALTER TABLE T_SYS_Language_Forms DROP CONSTRAINT pk_constraint_name 
86        --DECLARE @pkDropCommand nvarchar(1000) 
87        --SET @pkDropCommand = N'ALTER TABLE T_SYS_Language_Forms DROP CONSTRAINT ' + QUOTENAME((SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
88        --WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' 
89        --AND TABLE_SCHEMA = 'dbo' 
90        --AND TABLE_NAME = 'T_SYS_Language_Forms' 
91        ----AND CONSTRAINT_NAME = 'PK_T_SYS_Language_Forms' 
92        --))
93        ---- PRINT @pkDropCommand 
94        --EXECUTE(@pkDropCommand) 
95
96        -- Instead do
97        -- EXEC sp_rename 'dbo.T_SYS_Language_Forms.PK_T_SYS_Language_Forms1234565', 'PK_T_SYS_Language_Forms';
98
99
100        -- Check if they keys are unique (it is very possible they might not be) 
101        IF 1 >= (SELECT TOP 1 COUNT(*) AS cnt FROM T_SYS_Language_Forms GROUP BY LANG_UID ORDER BY cnt DESC)
102        BEGIN
103
104            -- If no Primary key for this table
105            IF 0 =  
106            (
107                SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
108                WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' 
109                AND TABLE_SCHEMA = 'dbo' 
110                AND TABLE_NAME = 'T_SYS_Language_Forms' 
111                -- AND CONSTRAINT_NAME = 'PK_T_SYS_Language_Forms' 
112            )
113                ALTER TABLE T_SYS_Language_Forms ADD CONSTRAINT PK_T_SYS_Language_Forms PRIMARY KEY CLUSTERED (LANG_UID ASC)
114            ;
115
116            -- Adding foreign key
117            IF 0 = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_T_ZO_SYS_Language_Forms_T_SYS_Language_Forms') 
118                ALTER TABLE T_ZO_SYS_Language_Forms WITH NOCHECK ADD CONSTRAINT FK_T_ZO_SYS_Language_Forms_T_SYS_Language_Forms FOREIGN KEY(ZOLANG_LANG_UID) REFERENCES T_SYS_Language_Forms(LANG_UID); 
119        END -- End uniqueness check
120        ELSE
121            PRINT 'FSCK, this column has duplicate keys, and can thus not be changed to primary key...' 
122    END -- End NULL check
123    ELSE
124        PRINT 'FSCK, need to figure out how to update NULL value(s)...' 
125END