locked
table should allow only few fields unique and rest fields can allow duplicate values through sp while inserting or updation RRS feed

  • Question

  • i have table called "vehicle" in which it should insert/update unique values that means if we have chassis no 'xyz123' and again

    'xyz123' should not allow for insert/update.

    unique columns are :

    chassis no.,vehicle plate no,Reg slno,vehicle code

    and other field like : status flag,manufacture name,manufacture date,vehicle type can insert duplicate values.

    can anyone please help me to write sp for the above scenario ?



    Wednesday, December 18, 2019 4:45 AM

All replies

  • Can you post CREATE TABLE along with desired result?

    >>>chassis no.,vehicle plate no,Reg slno,vehicle code

    Is it considered UNIQUE row if you insert ?

    1,1,1,1

    1,2,1,1

    Otherwise CREATE UNIQUE constraint on all those fields (if I understood you correctly)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Wednesday, December 18, 2019 5:16 AM
    Answerer
  • Hi sqlserver-msdn, 

    Per your description , you would like to set unique condition fro your some columns in table 'vehicle'. As Uri Dimant said , you can  create constraints. Also, in your table if you would like one unique column , PK (Primary Key) might be a good way. Please check . 

    IF OBJECT_ID('test1') IS NOT NULL drop table  test1  
    go 
    create table test1
    (A int primary key,  -----only one column
    B int )
    insert into test1 values(1,1)
    insert into test1 values(1,1)
    /*
    (1 row affected)
    Msg 2627, Level 14, State 1, Line 7
    Violation of PRIMARY KEY constraint 'PK__test1__3BD019AE609B5369'. Cannot insert duplicate key in object 'dbo.test1'. The duplicate key value is (1).
    The statement has been terminated.
    */
    IF OBJECT_ID('test2') IS NOT NULL drop table  test2  
    go 
    create table test2
    (A int ,
    B int )
    alter table test2 add constraint unique_A unique(A);----add many constraints
    insert into test2 values(1,1)
    insert into test2 values(1,1)
    /*
    (1 row affected)
    Msg 2627, Level 14, State 1, Line 21
    Violation of UNIQUE KEY constraint 'unique_A'. Cannot insert duplicate key in object 'dbo.test2'. The duplicate key value is (1).
    The statement has been terminated.
    */

    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.


    Wednesday, December 18, 2019 6:29 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, December 26, 2019 2:52 AM
  • >> I have table called "vehicle" [sic] in which it should insert/update unique values that means if we have chassis no 'xyz123' and again 'xyz123' should not allow for insert/update. <<

    A table models a set of entities. Therefore, the tablename should be plural or collective, not singular unless you really do have only one vehicle. You might also look up what a field is in SQL because it's not a column. Then you forgot the basic netiquette of posting DDL in SQL forums. What you're doing is actually unnecessary. Google how a VIN number is constructed. This required identification standard includes the make, model, and manufacturer information. 

    CREATE TABLE Motor_Pool
    (vin CHAR(19) NOT NULL PRIMARY KEY
         CHECK (vin LIKE <<horrible reg exp>>)
     chassis_nbr CHAR(??) NOT NULL, 
     vehicle_plate_nbr CHAR(??) NOT NULL, 
    ..);

    There is no such thing as a "status flag " in a properly designed relational database. Please read the metadata papers or the ISO 11179 standards for data element names. An attribute can be a "<something in particular>_status" like employment, marriage, graduation or whatever. But we never write with flags in RDBMS; that was assembly language.

    The VIN includes manufacturing data, and the basic physical description of vehicle. You need to go back and learn the automobile industry.

    --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

    Friday, December 27, 2019 12:36 AM
  •  

    Hi ,

    Have you solve your issue ? If not , could you please share us more information?

     

    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.

    Wednesday, January 1, 2020 6:52 AM