Creating a Table using MySql queries:

In this we will learn how we can create tables in the database using sql queries. As we know it becomes quite complicated to make the tables manually in the database. To ease the process we can follow the steps to make tables using my sql queries.

Syntax:

CREATE TABLE table_name (
    column1 column1 datatype column1 constraint,
    column2 column2 datatype column2 constraint,
    column3 column3 datatype column3 constraint,
   ....
);

Constraints:

  • NOT NULL CONSTRAINT – Ensures that a column cannot have a null value.
  • DEFAULT CONSTRAINT – Provides a default value for a column when none is specified.
  • UNIQUE CONSTRAINT – Ensures that all value in a column are different.
  • CHECK CONSTRAINT – Make sure all values in a column satisfy certian criteria.
  • PRIMARY_KEY CONSTRAINT – Used to uniquely identify a row in a table.
  • FOREIGN_KEY CONSTRAINT – Used to ensure referential integrity of the data.

Keys:

  • A primary key is used to uniquely identify each row in a table.
  • A primary key can consists of one or more columns on a table.
  • When multiple columns are used as primary key, it is called as Composite key.
  • A foreign key is a column (or columns) that references a column (most often primary key) of other table.
  • The purpose of foreign key is to referential integrity of the data.

As shown in the above picture, Cust_ID is the foreign key for order table whereas it is primary key for in the Customer table that means the value of Cust_ID will not change in either of the table.

For example we will create a table named as customer_table inside the ‘test’ database:

Tagged : / /

Considerations for Multiple VSS Databases – Pros and Cons

multiple-vss-databases

Microsoft recommends against using multiple VSS databases in simple cases. The support that was in some earlier versions of the product using Data Path doesn’t seem to work at all well in some cases. And at first it can seem that VSS isn’t even capable of operating with multiple databases. On the other hand, there are sometimes good reasons for using multiple VSS databases. And mechanisms for accessing multiple databases ­­sometimes better than the original Data Path mechanism­­ are available in every case (although often poorly documented).
Considerations for Multiple VSS Databases

Pro

  • Quicker maintenance (ANALYZE, backup, etc.) You can for example do detailed maintenance on a different database each day of the week rather than having to do the whole thing all at once.
  • Smaller granularity (In other words you can take a damaged part of VSS down without taking having to take the entire VSS system down.) The current “all or nothing” behavior of VSS is a real thorn in the side of many VSS administrators. In fact this shortcoming alone seems such a “scalability problem” that it makes VSS less than desirable for use in any organization larger than ten people. Multiple VSS databases mitigates this.
  • Perhaps slightly (but not significantly) better performance.
  • Slightly less risk of running into VSS bugs that tend to show up when VSS is stretched beyond its testing and its design center to handle a single very large database.
  • If you have to “restore” a whole database, you at least don’t lose all your organization’s recent work.

Con

  • Potentially a separate list of users for each database, with each user having a separate SS.INI for every database. (One of the configuration recommendations below negates both of these limitations, but maintenance will not be fully automatic. You definitely will have to “add” new users to each database separately, and you may also have to “just remember” to do a few additional manual steps whenever you add or change a VSS user.
  • Separate “user rights” for each database. (It’s very difficult to work around this limitation as the VSS tools for maintaining “user rights” are poor. In fact there’s not even a way to “dump” a “user rights” database to text so you can scan or manipulate it yourself with your own text editing tools.) You could easily wind up facing a maintenance nightmare. One of the configuration recommendations below negates this limitation by simply “not using” user rights at all. You should seriously consider the option of not using “project security”.
  • Microsoft support may blame some hard problems on the multiple databases and say “we told you so”, even if multiple databases really don’t have anything to do with the problem you’re asking them to help with.
  • You cannot “share” files between multiple VSS databases at all. All you can hope for is that your source can be divided into multiple VSS databases along fairly natural project boundaries in such a way that there is virtually no need to “share” files between the multiple databases.
  • It is difficult to bring your multiple databases into one centralized system. The configuration recommendation below of having one unified grand project hierarchy makes this easier. But you will still need to use the Archive and Restore utilities if you need to move a project from one database to another.

 

Tagged : / / / / / / / / / / / / / /