locked
Creating PKs RRS feed

  • Question

  • Hi,

    I would like to know which column should I put in the PK first, [ID_CHECK] or [ID_CONTROL].

    CREATE TABLE QUALITY_CONTROL
    (
    	[ID_CHECK] [int] NULL,
    	[ID_CONTROL] [int] NOT NULL
    )
    
     ALTER TABLE QUALITY_CONTROL
     ADD CONSTRAINT QUALITY_CONTROL_P01 PRIMARY KEY ([ID_CHECK], [ID_CONTROL])
    
     or 
    
     ALTER TABLE QUALITY_CONTROL
     ADD CONSTRAINT QUALITY_CONTROL_P01 PRIMARY KEY ([ID_CONTROL], [ID_CHECK])

    The [ID_CHECK] column has more repeated data, I don't know if that means anything.

    Thanks.

    Monday, March 9, 2020 5:17 PM

All replies

  • I think your approach is wrong. First of all, the ISO standards prefer that we call attribute property is done as a postfix. That's why we would write "check_id" and "control_id" instead. But if "_id" is an identifier, it would be on a nominal scale and cannot be a numeric quantity. This is basic data modeling will ignore the fact that "check" uses a reserved word as a data element name; what are we checking? Since the improperly named check_id column is NULL-able, it cannot be part of the key anyway! All parts of the key must be known. This is the definition. This is not optional. 

    I am also sorry that you have only one quality control as shown by the singular tablename. And since we have no sample data to use to make better guesses, the only valid table declaration would look like this:

    CREATE TABLE Quality_Controls

    (something_check CHAR(10),

    control_id INTEGER NOT NULL PRIMARY KEY);

    I strongly suggest that you get a good book on basic data modeling and read it carefully. I think your whole approach is both clumsy and wrong.


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, March 9, 2020 8:02 PM
  • Pretty sure the Alter table statement will error out due to the ID_CHECK column allowing NULLS.  Was the intent for that column to allow NULLS?
    Monday, March 9, 2020 8:09 PM
  • Hi , 

    As shown in the above script, the ID_CONTROL and ID_CHECK columns in the QUALITY_CONTROL table form a composite primary key constraint for this table. This makes sure that every row in the QUALITY_CONTROL table has a unique combination of ID_CONTROL and ID_CHECK . This prevents the insertion of duplicate rows.

    For more information , please check following link. Primary and Foreign Key Constraints If you have any issue , please share us . 

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 10, 2020 3:12 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 12, 2020 7:54 AM
  • It seems that if you have some queries that filter by id_control separately (for example: ‘WHERE id_control=123’), then it is better to use this order in composite key: id_control, id_check. See: https://social.msdn.microsoft.com/Forums/en-US/3fa70133-6394-4bad-a0b8-8723aaad2c47.

    Thursday, March 12, 2020 2:31 PM
  • Hi,

     

    If you find any post in the thread is helpful, you could kindly mark it as answer. This would benefit the community, and also encourage the community member to keep working on your issues.

     

    Best Regards,

    Rachel

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 17, 2020 8:18 AM